Home » SQL & PL/SQL » SQL & PL/SQL » Query (oracle 10g)
| Query [message #430334] |
Tue, 10 November 2009 02:31  |
|
|
Hello All,
My requirement is that
Am using run time date parameter to pass the value to pdate.
If i give 31/sep/09 in the parameter
then it find sum(amt between 1stjan08 and 31stsep08),
sum(amt between 1stjan09 and 31stsep09)
for that i written the following code,But its taking much time to Execute.
and also i opt to try with decode and it was not working though,.
SUM
(DECODE
(prefc.class_code,
'AA', (CASE
WHEN sls.docdt
BETWEEN TO_DATE
( '0101'
|| TO_CHAR
(( TO_NUMBER
(TO_CHAR
(:pdate,
'RRRR'
)
)
- 1
)
),
'ddmmrrrr'
)
AND TO_DATE
( TO_CHAR (:pdate, 'dd')
|| TO_CHAR (:pdate, 'mm')
|| TO_CHAR
(( TO_NUMBER
(TO_CHAR
(:pdate,
'RRRR'
)
)
- 1
)
),
'ddmmrrrr'
)
THEN slsd.amt1
ELSE 0
END
)
)
) data1,
SUM
(DECODE
(prefc.class_code,
'AA', (CASE
WHEN sls.docdt
BETWEEN to_date('0101'||to_char(:pdate,'RRRR'),'ddmmrrrr')
AND to_date(to_char(:pdate,'dd')||to_char(:pdate,'mm')||to_char(:pdate,'RRRR'),'dd/mm/rrrr')
THEN slsd.amt1
ELSE 0
END
)
)
) data2
If is there any easy method rather than so many to_char and to_dates.Please Help me to get the desired one..
Thanks in Advance
Seyed
[Updated on: Tue, 10 November 2009 02:32] Report message to a moderator |
|
|
|
| Re: Query [message #430339 is a reply to message #430334] |
Tue, 10 November 2009 02:38  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Indenting is great to clarify a query, too much indenting leads to unreadable query.
Assuming :pdate is of DATE datatype as your code seems to imply:
to_date('0101'||to_char(:pdate,'RRRR'),'ddmmrrrr')
is
to_date(to_char(:pdate,'dd')||to_char(:pdate,'mm')||to_char(:pdate,'RRRR'),'dd/mm/rrrr')
is
Regards
Michel
|
|
|
|
| Re: Query [message #430345 is a reply to message #430339] |
Tue, 10 November 2009 03:02 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 10 November 2009 09:38
to_date(to_char(:pdate,'dd')||to_char(:pdate,'mm')||to_char(:pdate,'RRRR'),'dd/mm/rrrr')
is
Not true. The concatenated string contains no separators between the numbers, so the to_date() will fail.
(But in essence, of course, you are correct, Michel)
[Updated on: Tue, 10 November 2009 03:02] Report message to a moderator |
|
|
|
| Re: Query [message #430356 is a reply to message #430334] |
Tue, 10 November 2009 03:25 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I thought about trying to rewrite your query, but it gave me a headache. Here's what you need to do, assuming that PDATE is of DATE type:,SUM(CASE WHEN sls.docdt BETWEEN to_date('01-jan-2008') and add_months(:pdate,-12)
then slsd.amt1
else 0
end data_1
,SUM(CASE WHEN sls.docdt BETWEEN to_date('01-jan-2008') and :pdate
then slsd.amt1
else 0
end data_2
If pdate is a string, then you will need to replace the references to :pdate with TO_CHAR(:pdate,'<format mask>')
|
|
|
|
| Re: Query [message #430366 is a reply to message #430345] |
Tue, 10 November 2009 03:36 |
|
|
thanks Micheal.
CASE
WHEN sls.docdt
BETWEEN TO_DATE
( '0101'
|| TO_CHAR
(( TO_NUMBER
(TO_CHAR
(:pdate,
'RRRR'
)
)
- 1
)
),
'ddmmrrrr'
)
can You propose some other simple method for the above code? ..
|
|
|
|
| Re: Query [message #430371 is a reply to message #430366] |
Tue, 10 November 2009 03:51 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
First, I'd recommend using a much tighter code format - SQL and PL/SQL are verbose languages at the best of times - using this format would make large procedures utterly unreadable.
Then you need to read up on the date manipulation functions that are available to you: CASE WHEN sls.docdt between add_months(trunc(:pdate,'YYYY'),-12)
{typo}
[Updated on: Tue, 10 November 2009 03:52] Report message to a moderator |
|
|
|
| Re: Query [message #430384 is a reply to message #430366] |
Tue, 10 November 2009 04:47 |
glakshkar
Messages: 7
Registered: September 2005
Location: PUNE
|
Junior Member |
|
|
Dear Sayed,
Here we go, I have created 1 temp table to work upon
CREATE TABLE TEMP_TABLE ( NO NUMBER
, AMOUNT NUMBER
, PURCHASE_DATE DATE);
now i insert dummy data into this
INSERT INTO TEMP_TABLE VALUES(1,1000,TO_DATE('02/01/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(2,1020,TO_DATE('02/03/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(3,1500,TO_DATE('10/04/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(4,3000,TO_DATE('17/05/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(5,2220,TO_DATE('02/07/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(6,3400,TO_DATE('02/09/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(7,1000,TO_DATE('23/10/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(8,9900,TO_DATE('27/11/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(9,500,TO_DATE('12/12/2008','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(10,89000,TO_DATE('13/01/2009','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(11,9020,TO_DATE('16/03/2009','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(12,9120,TO_DATE('20/04/2009','DD/MM/RRRR'));
INSERT INTO TEMP_TABLE VALUES(13,4500,TO_DATE('13/06/2009','DD/MM/RRRR'));
--------------------------------------------------------
SELECT * FROM TEMP_TABLE TT ORDER BY PURCHASE_DATE;
No | Amount | Purchase Date
-----------------------------------------
1 | 1000 | 02/01/2008
2 | 1020 | 02/03/2008
3 | 1500 | 10/04/2008
4 | 3000 | 17/05/2008
5 | 2220 | 02/07/2008
6 | 3400 | 02/09/2008
7 | 1000 | 23/10/2008
8 | 9900 | 27/11/2008
9 | 500 | 12/12/2008
10 | 89000 | 13/01/2009
11 | 9020 | 16/03/2009
12 | 9120 | 20/04/2009
13 | 4500 | 13/06/2009
/* Query as per your requirement*/
SELECT SUM((CASE WHEN GL.PURCHASE_DATE BETWEEN TO_DATE('01/01/'|| TO_CHAR(TO_DATE('30/09/2008','DD/MM/RRRR'),'RRRR'),'DD/MM/RRRR')
AND TO_DATE('30/09/2008','DD/MM/RRRR') THEN AMOUNT ELSE 0 END)) TOTAL_AMT_30092008
, SUM((CASE WHEN GL.PURCHASE_DATE BETWEEN TO_DATE('01/01/'|| TO_CHAR(TO_DATE('30/09/2009','DD/MM/RRRR'),'RRRR'),'DD/MM/RRRR')
AND TO_DATE('30/09/2009','DD/MM/RRRR') THEN AMOUNT ELSE 0 END)) TOTAL_AMT_30092009
FROM TEMP_TABLE GL;
/* till here Query you require */
Just replace '30/09/2008' with :pdate1 & '30/09/2009' with :pdate2
Check, if this is what you require
Regards
Gaurav 4m Pune
|
|
|
|
|
|
| Re: Query [message #430394 is a reply to message #430388] |
Tue, 10 November 2009 05:15 |
|
|
Thanks to JRowbottom Again.
I need to use decode in the place of case. .since case is taking more time.
The following code which i reduced with help from you people.
Last year
CASE
WHEN sls.docdt between add_months(trunc(&pdate,'YYYY'),-12)
AND add_months(&pdate,-12)
THEN slsd.amt1
ELSE 0
END
Current Year
CASE
WHEN sls.docdt BETWEEN TRUNC (&pdate, 'YEAR') AND &pdate
THEN slsd.amt1
ELSE 0
END
I tried to use decode but still facing probs..
|
|
|
|
|
|
| Re: Query [message #430414 is a reply to message #430394] |
Tue, 10 November 2009 06:05 |
|
|
When am running the scripts in Toad its workign fine.
But when i try to create a report with the report builder its giving me an error.
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
For more information please find the attached image
-
Attachment: Untitled.jpg
(Size: 11.98KB, Downloaded 466 times)
|
|
|
|
| Re: Query [message #430418 is a reply to message #430394] |
Tue, 10 November 2009 06:09 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:I need to use decode in the place of case. .since case is taking more time.
I'm afraid I don't believe you. Here's a test case comparing 100,000 CASE selects to 100,000 DECODE selects:
create or replace type ty_timing_table as table of number;
/
declare
t_timing ty_timing_table := ty_timing_table();
v_iter pls_integer := 100000;
v_max pls_integer := 1000;
v_time pls_integer;
v_avg number;
v_total pls_integer;
v_stddev number;
v_Val number;
begin
t_timing.delete;
t_timing.extend(v_iter);
for i in 1..v_iter loop
v_time := dbms_utility.get_time;
select case when i between 100 and 10000 then 1 else 0 end
into v_val
from dual;
t_timing(i) := dbms_utility.get_time - v_time;
end loop;
select avg(column_value)
,stddev(column_value)
,sum(column_value)
into v_avg,v_stddev,v_total
from table(t_timing);
dbms_output.put_line('Test 1 CASE: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));
t_timing.delete;
t_timing.extend(v_iter);
for i in 1..v_iter loop
v_time := dbms_utility.get_time;
select decode(sign(i-99),1,decode(sign(i-10001),-1,1,0),0)
into v_val
from dual;
t_timing(i) := dbms_utility.get_time - v_time;
end loop;
select avg(column_value)
,stddev(column_value)
,sum(column_value)
into v_avg,v_stddev,v_total
from table(t_timing);
dbms_output.put_line('Test 2 DECODE: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));
end;
Results:Test 1 CASE: Total: 183 Avg: .00183 stddev: .056
Test 2 DECODE: Total: 185 Avg: .00185 stddev: .056
|
|
|
|
| Re: Query [message #430423 is a reply to message #430418] |
Tue, 10 November 2009 06:18 |
|
|
Thanks for the information.
As am getting an error in the report builder..
Error :
WHEN sls.docdt==> BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
Can you tell me where i have to make changes .I tried with the to_date.
-
Attachment: Untitled.jpg
(Size: 11.98KB, Downloaded 510 times)
[Updated on: Tue, 10 November 2009 06:36] Report message to a moderator |
|
|
|
| Re: Query [message #430436 is a reply to message #430423] |
Tue, 10 November 2009 07:14 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That looks like your :pdate variable is defined as a number rather than a date.
Check the parameter definition in the User Parameters section.
|
|
|
|
|
|
| Re: Query [message #430440 is a reply to message #430334] |
Tue, 10 November 2009 07:25 |
|
|
i stopped posting here since it was not the problem of SQL/PLSQL tool.So i just start posting over there.
How to check parameter definition in the user parameter form ?.
|
|
|
|
| Re: Query [message #430441 is a reply to message #430440] |
Tue, 10 November 2009 07:27 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hmmm....
This is Oracle Reports that you're using, isn't it?
|
|
|
|
| Re: Query [message #430442 is a reply to message #430441] |
Tue, 10 November 2009 07:29 |
|
|
JRowbottom wrote on Tue, 10 November 2009 07:27Hmmm....
This is Oracle Reports that you're using, isn't it?
Yes..Report Builder 6i.
|
|
|
|
|
|
| Re: Query [message #430444 is a reply to message #430334] |
Tue, 10 November 2009 07:32 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Shall we continue this in the reports thread?
I don't really care which is used but having two is just confusing.
|
|
|
|
|
|
| Re: Query [message #430446 is a reply to message #430444] |
Tue, 10 November 2009 07:35 |
|
|
cookiemonster wrote on Tue, 10 November 2009 07:32Shall we continue this in the reports thread?
I don't really care which is used but having two is just confusing.
Yes over there ..Please thanks. .
|
|
|
|
| Re: Query [message #430447 is a reply to message #430443] |
Tue, 10 November 2009 07:36 |
|
|
ramoradba wrote on Tue, 10 November 2009 07:32seyed456 wrote on Tue, 10 November 2009 07:25i stopped posting here since it was not the problem of SQL/PLSQL tool.So i just start posting over there.
How to check parameter definition in the user parameter form ?.
see the above
But while creating the report By wizard,
I copied the query in the data and click next that time i get an error.
|
|
|
|
|
|
| Re: Query [message #430455 is a reply to message #430449] |
Tue, 10 November 2009 08:11 |
|
|
If i take either report wizard or data wizard ,there i paste my query which i built and click next by the time i get this error which i mentioned above posts,..
I tried change report builder parameter to date, though the same error..Please help me. .
|
|
|
|
|
|
| Re: Query [message #430459 is a reply to message #430455] |
Tue, 10 November 2009 08:18 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
seyed456 wrote on Tue, 10 November 2009 08:11If i take either report wizard or data wizard ,there i paste my query which i built and click next by the time i get this error which i mentioned above posts,..
I tried change report builder parameter to date, though the same error..Please help me. .
Why you are again posting here?
See the messages over there at reports forum...And provide the query.... i am unable to find the total query there ? Please continue on that thred only.
sriram.
|
|
|
|
| |
  |
Goto Forum:
Current Time: Wed Aug 20 07:59:11 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
