Home » SQL & PL/SQL » SQL & PL/SQL » Query (oracle 10g)
Query [message #430334] Tue, 10 November 2009 02:31 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
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
trunc(:pdate,'YEAR')


to_date(to_char(:pdate,'dd')||to_char(:pdate,'mm')||to_char(:pdate,'RRRR'),'dd/mm/rrrr')

is
:pdate


Regards
Michel
Re: Query [message #430345 is a reply to message #430339] Tue, 10 November 2009 03:02 Go to previous messageGo to next message
Frank
Messages: 7880
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
:pdate



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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #430388 is a reply to message #430384] Tue, 10 November 2009 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of using colors you should read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Query [message #430394 is a reply to message #430388] Tue, 10 November 2009 05:15 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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..Sad
Re: Query [message #430413 is a reply to message #430394] Tue, 10 November 2009 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to use decode in the place of case. .since case is taking more time.

I doubt you can see any difference between case and decode.
I think you made wrong tests that lead to wrong conclusions.

Regards
Michel
Re: Query [message #430414 is a reply to message #430394] Tue, 10 November 2009 06:05 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 104 times)
Re: Query [message #430418 is a reply to message #430394] Tue, 10 November 2009 06:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 115 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 Go to previous messageGo to next message
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 #430439 is a reply to message #430334] Tue, 10 November 2009 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you refrain from posting the same question in multiple forums:http://www.orafaq.com/forum/t/152096/129190/
it just confuses matters.
Re: Query [message #430440 is a reply to message #430334] Tue, 10 November 2009 07:25 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

JRowbottom wrote on Tue, 10 November 2009 07:27
Hmmm....
This is Oracle Reports that you're using, isn't it?



Yes..Report Builder 6i.
Re: Query [message #430443 is a reply to message #430440] Tue, 10 November 2009 07:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
seyed456 wrote on 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 ?.

./fa/7000/0/

see the above
Re: Query [message #430444 is a reply to message #430334] Tue, 10 November 2009 07:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 #430445 is a reply to message #430444] Tue, 10 November 2009 07:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
cookiemonster wrote on Tue, 10 November 2009 07:32
Shall we continue this in the reports thread?
I don't really care which is used but having two is just confusing.


thats better Smile

sriram
Re: Query [message #430446 is a reply to message #430444] Tue, 10 November 2009 07:35 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

cookiemonster wrote on Tue, 10 November 2009 07:32
Shall 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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ramoradba wrote on Tue, 10 November 2009 07:32
seyed456 wrote on 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 ?.

./fa/7000/0/

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 #430449 is a reply to message #430447] Tue, 10 November 2009 07:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
seyed456 wrote on Tue, 10 November 2009 07:36
ramoradba wrote on Tue, 10 November 2009 07:32
seyed456 wrote on 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 ?.

./fa/7000/0/

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.



Then modify that and re execute that with your data wizard or report wizard which you are using...see the default behaviour it`s a number there for " sriram".

sriram
Re: Query [message #430455 is a reply to message #430449] Tue, 10 November 2009 08:11 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #430456 is a reply to message #430334] Tue, 10 November 2009 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I thought we agreed we'ed continue this in the other thread? Sad
Re: Query [message #430459 is a reply to message #430455] Tue, 10 November 2009 08:18 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
seyed456 wrote on 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. .



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.
Previous Topic: running total
Next Topic: Checking Null values in column (merged 2)
Goto Forum:
  


Current Time: Sun Sep 25 23:13:58 CDT 2016

Total time taken to generate the page: 0.07377 seconds