Home » SQL & PL/SQL » SQL & PL/SQL » Parametrize a date (merged 4)
Parametrize a date (merged 4) [message #412972] Mon, 13 July 2009 13:08 Go to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Parameterize a date:

I am using the union all statement to get the data on specified dates. For example, I need the information how a property is trending over last week, so I use the union all and repeat the query for 7 times. This also slows my performance down. This is no big deal but now I need the data for one year! Is there a way I can parameterize the date (refer to the date AU_TIME in query below) in following query, sort of a "FOR i = 1 to 7 NEXT kind of statement. "

I also tried to use the calendar table as shown inmy next query but that is not any good either.

Your help is deeply appreciated.


Select 'JUL1', sum(case WHEN AP_NEW_VALUE = 'Configuration' then 1 else 0
END) "Configuration",
sum(case WHEN AP_NEW_VALUE = 'Technical' then 1 else 0
END) "Technical",
sum(case WHEN AP_NEW_VALUE = 'Other' then 1 else 0
END) "Other"
From AUDIT_LOG,AUDIT_PROPERTIES,BUG
Where AU_ACTION_ID = AP_ACTION_ID
AND AU_ENTITY_ID = BG_BUG_ID
And AU_ACTION_ID IN (Select Max(AU_ACTION_ID)
From AUDIT_LOG,AUDIT_PROPERTIES
Where AU_ACTION_ID = AP_ACTION_ID
And AP_FIELD_NAME = 'BG_USER_01'
And to_date(au_time) <=
to_date('2009-07-01','YYYY-MM-DD')
Group By AU_ENTITY_ID)


'Second Query by using the calendar table but this does not work either..

With
calendar as (Select to_date('07-01-2009','MM/DD/YYYY')+ 1*(level-1) calendar
from dual
connect by level <= 15)

Select calendar.calendar, sum(case WHEN AP_NEW_VALUE = 'Configuration' then 1 else 0
END) "Configuration",
sum(case WHEN AP_NEW_VALUE = 'Technical' then 1 else 0
END) "Technical",
sum(case WHEN AP_NEW_VALUE = 'Other' then 1 else 0
END) "Other"
From BUG, AUDIT_PROPERTIES,AUDIT_LOG
PARTITION BY (AU_ENTITY_ID)
RIGHT OUTER JOIN cal ON to_date(AU_TIME) = calendar
Where AU_ACTION_ID = AP_ACTION_ID
AND AU_ENTITY_ID = BG_BUG_ID
And AU_ACTION_ID IN (Select Max(AU_ACTION_ID)
From AUDIT_LOG,AUDIT_PROPERTIES
Where AU_ACTION_ID = AP_ACTION_ID
And AP_FIELD_NAME = 'BG_STATUS'
And to_date(au_time) < calendar
Group By AU_ENTITY_ID)
Group by calendar
order by calendar
Re: Parametrize a date (merged) [message #412975 is a reply to message #412972] Mon, 13 July 2009 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous topics:
Michel Cadot wrote on Sat, 13 December 2008 18:33
...

Before 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste your tries.

Regards
Michel

[Updated on: Mon, 13 July 2009 13:15]

Report message to a moderator

Re: Parametrize a date (merged) [message #412977 is a reply to message #412972] Mon, 13 July 2009 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT 'JUL1',
       Sum(CASE
             WHEN ap_new_value = 'Configuration' THEN 1
             ELSE 0
           END) "Configuration",
       Sum(CASE
             WHEN ap_new_value = 'Technical' THEN 1
             ELSE 0
           END) "Technical",
       Sum(CASE
             WHEN ap_new_value = 'Other' THEN 1
             ELSE 0
           END) "Other"
FROM   audit_log,
       audit_properties,
       bug
WHERE  au_action_id = ap_action_id
       AND au_entity_id = bg_bug_id
       AND au_action_id IN (SELECT   Max(au_action_id)
                            FROM     audit_log,
                                     audit_properties
                            WHERE    au_action_id = ap_action_id
                                     AND ap_field_name = 'BG_USER_01'
                                     AND To_date(au_time) <= To_date('2009-07-01','YYYY-MM-DD')
                            GROUP BY au_entity_id) 


Once again, I contend that only tables which provide data to the SELECT clause should be in the FROM clause.
Other tables required for filtering should be subordinated into the WHERE clause.
This can assist the CBO to make better choices & faster response time.
Re: Parametrize a date (merged) [message #412978 is a reply to message #412972] Mon, 13 July 2009 13:28 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
My connection had problems and the posts go repeated. Admins, Please delete the other 2.
Re: Parametrize a date (merged) [message #412979 is a reply to message #412978] Mon, 13 July 2009 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2? You mean 3 too much.
If you have problem FIRST go to the forum to check if your post is there and ONLY then repost.

And in the meantime and BEFORE posting anything else, read Foruml Guide.

Regards
Michel

[Updated on: Mon, 13 July 2009 13:49]

Report message to a moderator

Re: Parametrize a date (merged) [message #412987 is a reply to message #412979] Mon, 13 July 2009 17:27 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Michel Cadot wrote on Mon, 13 July 2009 13:47
2? You mean 3 too much.
If you have problem FIRST go to the forum to check if your post is there and ONLY then repost.

And in the meantime and BEFORE posting anything else, read Foruml Guide.

Regards
Michel



I am sorry but could you help me?
Re: Parametrize a date (merged 4) [message #412988 is a reply to message #412972] Mon, 13 July 2009 17:30 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I am sorry but could you help me?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Previous Topic: Help creating view for each record...
Next Topic: Performance issue with oracle 10g
Goto Forum:
  


Current Time: Thu Dec 08 04:09:06 CST 2016

Total time taken to generate the page: 0.10210 seconds