OraFAQ Forum: SQL & PL/SQL » paramter values fetch from dual and apply to query

Home » SQL & PL/SQL » SQL & PL/SQL » paramter values fetch from dual and apply to query (oracle 11 ,linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

paramter values fetch from dual and apply to query [message #632433]

Fri, 30 January 2015 13:35

miroconnect@yahoo.com
Messages: 202
Registered: April 2006

Senior Member

I have a table called audits with columns received_on and closed_on on this this table i have a view which translates dates to fiscal year and quarter

my query finds count of audits received and closed for any given fiscal year and a quarter

it uses parameters fiscal year(fy) and quarter in couple of places to filter data, I need advice on how I can select the parameter fy and quarter from dual instead on repeating the same parameter value all over the query

here is my query

SELECT (SELECT COUNT (*)
          FROM audit_dates_fyq_vw vw
         WHERE vw.fy_received_on = 2011 AND vw.fyq_received_on = 4)
          AS no_of_audits_received,
       (SELECT COUNT (*)
          FROM audit_dates_fyq_vw vw
         WHERE     vw.fy_received_on = 2011
               AND vw.fyq_received_on = 4
               AND (    vw.fy_closed_on IS NOT NULL
                    AND (   vw.fy_closed_on < 2011
                         OR (vw.fy_closed_on = 2011 AND vw.fyq_closed_on <= 4)))
               )
          AS no_of_closed_audits,
       (SELECT COUNT (*)
          FROM audit_dates_fyq_vw vw
         WHERE     vw.fy_received_on = 2011
               AND vw.fyq_received_on = 4
               AND (   vw.fy_closed_on > 2011
                    OR vw.fy_closed_On IS NULL
                    OR (vw.fy_closed_on = 2011 AND vw.fyq_closed_on > 4)))
          AS no_of_open_audits
FROM DUAL

is there any way I can avoid repeat of 2011 and 4 and fetch it using some select from dual?

Report message to a moderator

Re: paramter values fetch from dual and apply to query [message #632434 is a reply to message #632433]

Fri, 30 January 2015 15:04

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

with params as (select 2001 param1, 4 param2 from dual) <your select join to params>

Then you have to repeat param1 and param2 instead.

Report message to a moderator

Re: paramter values fetch from dual and apply to query [message #632435 is a reply to message #632434]

Fri, 30 January 2015 15:06

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And forgot:

Michel Cadot wrote on Fri, 06 September 2013 21:34

From your previous topics:

Michel Cadot wrote on Fri, 08 March 2013 07:28
...
For your next question remember: With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

BlackSwan wrote on Tue, 14 December 2010 19:39
...
Which part of the Posting Guidelines ( http://www.orafaq.com/forum/t/88153/0/ ) did you NOT read & follow?
...

BlackSwan wrote on Thu, 17 June 2010 01:59
...
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
...

And why don't you feedback and thank people that help you?

Report message to a moderator

Re: paramter values fetch from dual and apply to query [message #632437 is a reply to message #632435]

Fri, 30 January 2015 16:56

miroconnect@yahoo.com
Messages: 202
Registered: April 2006

Senior Member

Thank you so much, I will try to post test data and script. Thank You.

Report message to a moderator

Previous Topic:	Convert XML data as blob image
Next Topic:	SQL Loader ; SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:30:38 CDT 2024