Home » SQL & PL/SQL » SQL & PL/SQL » Cursor? Please guide!
Cursor? Please guide! [message #269843] Mon, 24 September 2007 18:28 Go to next message
djayatosu
Messages: 6
Registered: September 2007
Location: USA
Junior Member
Hey all,
I have a query that takes in one date parameter and gives me a single row output.The parameter is a date. I need to run the same sql for all weekends for two years..
eg:

Grouping week ending passed failed no result
1 Alpha 01/13/2006 4791 1131 3039

now, I need to run the same query for next weekend and so on for the full three years.

In other words, I need to iterate the same query with different parameter and then load those records in a table.

I just got the list of all weekends for the three years, but, I am not sure how to proceed. I need to use each of the value in the below query as a parameter in my complex SQL and then extract the result. I don't want to run the same query 152 times.
--query to fetch the weekends
select week_end
  from
(
select start_date as week_start,
       start_date + 6 as week_end,
       trunc(start_date,'d'),
       row_number() over (partition by trunc(start_date,'d') 
                              order by start_date) as rownumber
  from
  (select  to_date('10/31/2004','mm/dd/yyyy') + (level-1) as start_date        
           from dual
         connect by level <= trunc(sysdate)- to_date('10/31/2004','mm/dd/yyyy')+1
  
))
where rownumber  = 1

Can someone please guide?

Thanks for your time.

[Mod-edit: Frank added code-tags]

[Updated on: Tue, 25 September 2007 00:32] by Moderator

Report message to a moderator

Re: Cursor? Please guide! [message #269862 is a reply to message #269843] Mon, 24 September 2007 21:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Have you seen formatted and {...} sign before posted. Ensure that read forum guide before posting new thread.
Re: Cursor? Please guide! [message #269863 is a reply to message #269862] Mon, 24 September 2007 22:08 Go to previous messageGo to next message
djayatosu
Messages: 6
Registered: September 2007
Location: USA
Junior Member
I really apologize. This is my first ever post in the forum. I must have read the guide. I'm sorry.
Can anyone please guide me though?
Thank you!

Re: Cursor? Please guide! [message #269900 is a reply to message #269863] Tue, 25 September 2007 01:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I rewrote your query so you can rid of the analytic function. See if the result is the same (I didn't really test it):
SELECT NEXT_DAY (start_date, 'SAT') week_end
FROM   (SELECT     TO_DATE ('10/31/2004', 'mm/dd/yyyy') + (LEVEL - 1) AS start_date
        FROM       DUAL
        CONNECT BY LEVEL <= TRUNC (SYSDATE) - TO_DATE ('10/31/2004', 'mm/dd/yyyy') + 1)
WHERE  TO_CHAR (start_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN'
Now, can't you join your 'complex query' with the query above?

MHE
Re: Cursor? Please guide! [message #270024 is a reply to message #269900] Tue, 25 September 2007 08:06 Go to previous messageGo to next message
djayatosu
Messages: 6
Registered: September 2007
Location: USA
Junior Member
Frank, thanks for adding the code tags. I appreciate your help.
Maaher, thanks for the code. But, it's not what I was looking for. I need each of these values to be a parameter to my query.
In other words, if for example, my query is as follows..
select *
  from ...
 where some_date = (&Parameter}

Each time I execute the query with one value, it will return me one row. I need to execute this query with each of the 152 dates that we have and get the result in a single table. So basically, it's iteration or looping I guess.
I am pretty sure this is something simple to do. But, I've just started basic pl/sql programming and am no expert. Thus, seeking your help here.
Thanks for your time once again!
djay
Re: Cursor? Please guide! [message #270025 is a reply to message #270024] Tue, 25 September 2007 08:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No need for looping, if I understand correctly. It's a matter of joining:
SELECT <columns>
FROM   <tables>
WHERE  <date_column> IN  (<date_query>)
or
SELECT <columns>
FROM   <tables> 
   ,   ( <date_query> ) date_query
WHERE <table>.<date_column> = date_query.week_end
Of course you replace <date_query> with your dual thingy to retrieve your week_end values.

MHE

[Updated on: Tue, 25 September 2007 08:14]

Report message to a moderator

Re: Cursor? Please guide! [message #270027 is a reply to message #270025] Tue, 25 September 2007 08:20 Go to previous message
djayatosu
Messages: 6
Registered: September 2007
Location: USA
Junior Member
Maaher,
Thanks again. The problem is that I always won't get a result for all the dates. Okay, here is the output that I am looking for:

 Grouping    week ending   passed  failed no_result
 Alpha        11/6/2004      4791   1131    3039    (parameter 1)
 Alpha        11/13/2004     1234     2       23    (parameter 2)
 Alpha        11/20/2004       0      0       0     (parameter 3)
...
...
(run the query, fetch the result similarly for all 152 date paras)


Moreover, my query fetches the result up to that weekend (cumulative totals).
I hope I am clear now with my question. Sorry about the confusion.
-dj

[Updated on: Tue, 25 September 2007 08:25]

Report message to a moderator

Previous Topic: Row to Column conversion
Next Topic: Need help to purge data from a table
Goto Forum:
  


Current Time: Sat Dec 10 11:10:46 CST 2016

Total time taken to generate the page: 0.07799 seconds