PL/SQL :: Tuning Greater Than Predicate Query On Dates
Aug 2, 2012
I have the below query which is doing FTS and is very expensive causing load to timeout.
I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.
I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.
below is the query and plan
SELECT TRANSACTION_LOG.ID, TRANSACTION_LOG.USER_IDENTIFIER, TRANSACTION_LOG.START_TIME, TRANSACTION_LOG.END_TIME, TRANSACTION_LOG.REQUEST, TRANSACTION_LOG.RESPONSE ....
View 5 Replies
ADVERTISEMENT
Sep 20, 2012
The following sequence of simple statements shows me how Analytics and Predicate Pushing appear to work. But when I go from constant tests to a join with the same data as a row instead of a constant value, the Predicate Pushing stops.
I have a table with 9 million rows in it. It takes about 90 seconds to scan.
13:25:11 SQL>
13:25:11 SQL> select count(*) thecount from lv_pln_usge_fact ;
THECOUNT
----------
8681388
1 row selected.
Elapsed: 00:00:32.28
If I run an analytic that counts all rows in the table I can see that easy enough. This exampe as I understand it, scans the table (I know this because if for no other reason, it take 90 seconds to get an answer), then after scanning all rows, counts them and adds the count to each row. There are in fact 9500 or so rows with the values of lv_rqst shown. Instead of seeing all 9500 rows, I distinct it to get only one. See how the count shows all rows in the table.
From this we see that the predicate is not pushed into the inner query to filter rows. If it had, the analytic would have produced a number like 9500 not 9 million. I have no issue with this as this is how Analytics are documented work.
13:25:43 SQL> select distinct *
13:25:43 2 from (
13:25:43 3 select LV_RQST
13:25:43 4 ,count(*) over () thecount
[code]..
We can see how filter does happen with an analytic. We push the predicate into the inner query and all of a sudden we get a count of only those rows for the specific request. Thus we see the basics of how analytics work, particularly with respect to predicate pushing. There is not real rocket science here. The filter removed rows before the analytic counted them. This too is how Analytics are documented to work.
13:27:02 SQL> select distinct *
13:27:02 2 from (
13:27:02 3 select lv_rqst,count(*) over () thecount
13:27:02 4 from lv_pln_usge_fact
13:27:02 5 where lv_rqst = '746780192'
[code]...
So far we have been doing "all rows" analytics. Now we use a PARTITION clause to group the data. Notice the rowcount. It is the count for just the lv_rqst. Do not be fooled. This is because the PARTITION column says to synchronize the analytic count to the data for its associated row. Thus the counts will be grouped by LV_RQST. Whether we had the predicate on the outside of the query or not, for this specific lv_rqst shown we would still get this count. This is not proof that we did any filtering with the predicate lv_rqst = '746780192'.
However, notice the speed of the query. It gets done so fast, that there is no way it is scanning the table. I know there is an index that starts with LV_RQST so I conclude that the index was used which I believe means the predicate was in fact PUSHED into the inner query. Thus I think we did in fact filter the rows to just this single LV_RQST value and we also accessed the table using the predicate as well which became an index range scan instead of full table scan. My biggest point is we were able to use the index to get the data we want, not scan the table and this was made so because we added the LV_RQST column to the OVER clause as part of the PARTITION BY expression.
13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
13:27:03 4 ,count(*) over (partition by lv_rqst) thecount
[code]...
Now I add an additional column to the inner query, and an additional predicate against that column to the outer query. We know that 9539 is the count of all rows where LV_RQST='746780192' as we have seen that before above. I also know the data and know about half say Y and half say N for this indicator column. This query gives the right answer. I just put it here are additional demonstration of the way analytics work. It demonstrates that the new predicate is not pushed into the innser query to filter rows. Again this is how Analytics are documented to work.
It used the index to range scan only the rows where LV_RQST='746780192'. So only those predicates that have their columns in the anlytics OVER clause are allowed to be pushed into the query for filtering and accessing purposes.
13:27:03 SQL> col LV_PLN_USGE_DEL_IND format a20 trunc
13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
[code]...
This next query shows a little more clarity. If we add the indicator column to our OVER clause then the rowcount changes to be the number of rows where LV_RQST='746780192' AND lv_pln_usge_del_ind = 'N'. So by putting the column into the OVER expression, Oracle decides to push the predicate down into the inner query and filter the data before the analytic count is done. Again this just demonstrates for clarity how it works. I think I am describing it right anyway.
And once again the speed clearly indicates that an index range scan was done using LV_RQST='746780192'. Recall I said there is an index that starts with this column.
13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
13:27:03 4 ,LV_PLN_USGE_DEL_IND
[code]...
NOW WE COME TO MY PROBLEM.Instead of using the constant value '746780192' we are going to create a one column one row table that has this value in it. We are then going to join to the the analytic subquery instead of doing a contant test against it.
13:27:03 SQL> create table kevt1
13:27:03 2 (
13:27:03 3 lv_rqst varchar2(10) not null
13:27:03 4 )
13:27:03 5 /
Table created.
Elapsed: 00:00:00.06
13:27:03 SQL>
13:27:03 SQL> insert into kevt1 values ('746780192')
13:27:03 2 /
1 row created.
Elapsed: 00:00:00.00
13:27:03 SQL>
13:27:03 SQL> commit
13:27:03 2 /
Commit complete.
Elapsed: 00:00:00.00
This query is in my mind the same query we did before but we loose the use of the index and go back to doing a FULL TABLE SCAN.
13:27:03 SQL> select distinct x.*
13:27:03 2 from (
13:27:03 3 select LV_RQST
[code]...
For a little bit more clarity, two more queries. Pay attention to how long it takes, and to how the additional joins affect things. Notice, particularly with the last statement, that the join criteria is being pushed into the inner query with the analytics. Otherwise how did it get that count?
14:55:21 SQL> select distinct x.*
14:56:16 2 from (
14:56:16 3 select LV_RQST
14:56:16 4 ,LV_PLN_USGE_DEL_IND
[code]...
So after looking at all this, here is my question: How do I get Oracle the use the index and nested loop join to the table lv_pln_usge_fact. We know Oracle pushes the predicates down when the columns are referenced in the OVER expression because we see that in several places. We also know the CBO can do a nested loop join with index access on LV_RQST because it does it when we use a constant test. But it won't use the index and nested loop when we do a join to a table with the same data, no matter how much rewriting or hinting I do.
I tested this in 9i/10g/11g and got same behavior in all three places.
View 18 Replies
View Related
Apr 29, 2011
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
[code]....
View 1 Replies
View Related
Mar 17, 2011
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL))
from dwt_dvt_work_time where dwt_create_date > sysdate - 30
and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function:
CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE)
RETURN NUMBER
IS total_days NUMBER(11,2);
holiday_days NUMBER(11,2);
[code]....
View 1 Replies
View Related
Feb 1, 2012
I am sure I read somewhere that it is possible in 11g2 to store Predicate Fragments that work in a similar way to Functions for use in SQL queries?
I think the idea is that instead of defining a function that will form part of a predicate restriction you can define the SQL and then just reference it in your existing code.
For example, lets say I have 10 queries of varying types that only act the subset of ACTIVE customers. An 'ACTIVE' customer is one who has an order in the last 6 months OR receives a catalogue. In terms of SQL this is represented by a couple of table joins and and few 'AND' clauses.
In order to determine the active customers I can use a function in the predicate section of my 10 queries, but I don't want to do this for various reasons (context switching,tuning, etc).
Neither do I want to have to change the PROCEDURE declarations for the procs that store my 10 queries in order to pass in the portion of the SQL that would restrict the data set to ACTIVE customers.
I could just add the required SQL to each of the queries but this means if the definition of ACTIVE alters I have 10 changes to make. Is there a way that that predicate lines that select for 'ACTIVE' can somehow be declared and then this declaration referenced in my SQL queries?
View 1 Replies
View Related
Oct 5, 2012
is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date
2012-10-05
2012-10-06
2012-10-07
2012-10-08
2012-10-09
2012-10-10
2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date
2012-10-16
2012-10-17
2012-10-18
2012-10-19
2012-10-20
2012-10-21
2012-10-22
View 13 Replies
View Related
Apr 28, 2010
When i had a interview last week they ask me to write a query to display the next ten dates using sql query ( without using PL/SQL).
View 11 Replies
View Related
Oct 1, 2013
i need a query to get dates for last 7 days and each dates should be in one row... but select sysdate from dual..gives one row...
Expected Output Dates: 01-oct-201330-sep-201329-sep-201328-sep-201327-sep-201326-sep-2013
View 3 Replies
View Related
Feb 16, 2011
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
View 5 Replies
View Related
Sep 26, 2012
I need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788
View 8 Replies
View Related
Mar 17, 2010
i have a doubt in building a query.
I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
View 12 Replies
View Related
Sep 28, 2011
I got a issue with a query to fetch records between two dates for fixed timings
Date
From 29-09-2011 to 04-10-2011
Time
From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work
select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id
where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
[code]...
View 1 Replies
View Related
May 17, 2012
load data in multiple table using sql loader. I have IN predicate which i don't know is allowed in the sql loader or not
my control file and is as below
LOAD DATA
INFILE 'c: empdemo05.dat'
BADFILE 'c: empad05.bad'
DISCARDFILE 'c: empdisc05.dsc'
REPLACE
[code]....
i am getting below error when executing above error
SQL*Loader-350: Syntax error at line 5.
Expecting "(", found keyword when.
WHEN DEPTNO IN ('
View 4 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
Apr 9, 2013
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)
Sample inserts are:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.
The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from
[code]...
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
View 4 Replies
View Related
Apr 6, 2012
When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value
SQL> drop table precision_test;
Table dropped
SQL> create table precision_test(name number(2,5));
Table created
SQL> insert into precision_test values (1);
insert into precision_test values (1)
[code]....
View 5 Replies
View Related
Mar 9, 2011
I have two sql queries. They run the one after another.
Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc
Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
View 7 Replies
View Related
Dec 15, 2010
Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >
[code]...
from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?
View 3 Replies
View Related
Jul 15, 2013
I have table TEST_REP with below data
DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23
13 rows selected
I have used following query to get the below output:
select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......
Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0
10 rows selected
Expected Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46
View 6 Replies
View Related
Apr 7, 2013
is there any alternative can i user for in, not in, like to optimize this sql statement.
SELECT TKTNUM||’~’||
CUSNAME||’~’||
DECODE(PRTY,0,’PRIORITY 00’, 1,’PRIORITY 01’ ,2,’PRIORITY 02’, 03,’PRIORITY 03’, 04,’PRIORITY04’,’OTHERS’) ||’~’||
TO_CHAR(NEW_TIME(CREATEDTTM,’GMT’,’&2’),’MM-DD-YYYY HH24:MI:SS’) ||’~’||
CURSTA||’~’||
[code].......
View 4 Replies
View Related
Jul 14, 2011
I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours
View 9 Replies
View Related
May 26, 2013
how to pull data from a table where date is greater than current time (+24 hours)... my date field is in the following format 15-MAR-2013 20:07:00
I want to do something like this
select * from table_A where date_field > (sys_date_time) +24h
as an example, when I run a query @ 4 PM on March 26, I want to pull data that has date > 4 PM March 27
View 6 Replies
View Related
Mar 7, 2013
This is the description for the procedure:
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace
PROCEDURE ADD_REWARD_sp(
p_proj_id IN NUMBER,
p_pledgeAmt IN NUMBER,
p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
View 3 Replies
View Related
Dec 9, 2011
I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time
INTO on_time2
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date
WHERE ROWNUM = 2;
--RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time
INTO on_time2
FROM (SELECT on_time
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date)
WHERE ROWNUM = 2;
I can't move forward in my form until I figure out why this is not returning records
View 4 Replies
View Related
Nov 26, 2010
To find all the employees whose salaries greater than avg(salary) of the department.
Quote
select empname,salary,deptid from salaries t1 where
salary > (select avg(salary) from salaries t2 where t1.deptid = t2.deptid);
Unquote
Its not diplaying all departments
View 14 Replies
View Related
Oct 8, 2012
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE
V1 VARCHAR2(4000);
BEGIN
V1 :=
-- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000
EXECUTE IMMEDIATE V1;
END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
How can I create such table??
View 7 Replies
View Related
Oct 15, 2012
For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd
but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.
View 7 Replies
View Related
Dec 27, 2012
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
View 17 Replies
View Related
Nov 11, 2010
I have approximately 3 lakh records in mtl_material_transactions table. The value of attribute3 is populated with sysdate based on some business condition.
Out of these 3 lakh records, attribute3 has 5 records which are null. The problem is with the condition 'mmt.attribute3 is null'. The query is pasted below -
select mmt.*
from mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_parameters p
where mmt.transaction_type_id = mtt.transaction_type_id
and mp.organization_id = mmt.organization_id
and (upper(mtt.attribute1) ='OUTBOUND' or (upper(mtt.attribute1)='FORM' and upper(mtt.attribute2) in 'ISSUE','RECEIPT'))
and 1=decode(mmt.transaction_action_id,2,decode(mmt.subinventory_code,'WIP',0,1),1)
and mmt.attribute3 is NULL
View 3 Replies
View Related
