sunny82
asked on
how to do select within a select query by replacing a no. with a query within add_months
I have this query below which is running correctly and generating results as expected. This gives maximum value in business_date column in TABLEA for each of last 5 months.
--------------------------
select max(business_date)
from TABLEA
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') ,
-
5
) from TABLEA)
group by trunc(business_date, 'MM')
--------------------------
Now I want to replace the no. 5 in the query above by this query below -->
SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS')
I am trying to do this but getting error, not a single group-group function, please help -->
--------------------------
select max(business_date)
from TableA
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') ,
-
(SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS')
) from TableA)
group by trunc(business_date, 'MM')
--------------------------
select max(business_date)
from TABLEA
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') ,
-
5
) from TABLEA)
group by trunc(business_date, 'MM')
--------------------------
Now I want to replace the no. 5 in the query above by this query below -->
SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS')
I am trying to do this but getting error, not a single group-group function, please help -->
--------------------------
select max(business_date)
from TableA
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') ,
-
(SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS')
) from TableA)
group by trunc(business_date, 'MM')
ASKER
I will definitely try this tomorrow but how come my original query was working perfectly without the group by??
Here I am just replacing the number 5 with "SELECT PARAM_VALUE" query...
Here I am just replacing the number 5 with "SELECT PARAM_VALUE" query...
I am not sure ... but doing
add_months( trunc(max(business_date), - (select PARAM_VALUE FROM ... etc
may be confusing as Oracle as the second value in an add_month is an integer. Perhaps you may also want to try:
add_months( trunc(max(business_date), (select PARAM_VALUE * -1 FROM... etc
add_months( trunc(max(business_date), - (select PARAM_VALUE FROM ... etc
may be confusing as Oracle as the second value in an add_month is an integer. Perhaps you may also want to try:
add_months( trunc(max(business_date), (select PARAM_VALUE * -1 FROM... etc
there was a comma too much before the minus !
and your max and group by criteria don't match
select max(trunc(business_date, 'MM')) max_businessdate
from TableA
where business_date >=
(select add_months( trunc(max(business_date), 'MM') -
(SELECT PARAM_VALUE from RDW_PARAMS where PARAM_NAME =
'NUMBER_OF_MONTHENDS' where rownum <= 1))
from TableA)
group by trunc(business_date, 'MM')
and your max and group by criteria don't match
select max(trunc(business_date, 'MM')) max_businessdate
from TableA
where business_date >=
(select add_months( trunc(max(business_date), 'MM') -
(SELECT PARAM_VALUE from RDW_PARAMS where PARAM_NAME =
'NUMBER_OF_MONTHENDS' where rownum <= 1))
from TableA)
group by trunc(business_date, 'MM')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for the answers.
@sujith80: This worked perfectly. Many thanks.
@OP_Zaharin and @lwadwell: Single row Subquery returns more than 1 row error. Strange it was not there when 5 was used instead of query
@OP_Zaharin: Invalid no. of arguments error.
@sujith80: This worked perfectly. Many thanks.
@OP_Zaharin and @lwadwell: Single row Subquery returns more than 1 row error. Strange it was not there when 5 was used instead of query
@OP_Zaharin: Invalid no. of arguments error.
Out of interest, how many rows does
SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS'
return ... was this the subquery with multiple rows? ... I assumed it would only be 1.
SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS'
return ... was this the subquery with multiple rows? ... I assumed it would only be 1.
ASKER
No, it only returns the value 5.
@sunny82 - Glad to help!
select max(business_date)
from TableA
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') ,
-
(SELECT PARAM_VALUE
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS')
) from TableA group by trunc(business_date, 'MM'))
group by trunc(business_date, 'MM')