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?
|
|
|
|
|
|