• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

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')
0
sunny82
Asked:
sunny82
  • 3
  • 2
  • 2
  • +2
1 Solution
 
OP_ZaharinCommented:
- you are selecting MAX in this line add_months( trunc(max(business_date), 'MM') but there is no group function. i added the GROUP BY (in bold):

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')
0
 
sunny82Author Commented:
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...
0
 
lwadwellCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Geert GOracle dbaCommented:
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')
0
 
SujithData ArchitectCommented:
Try this

select max(business_date)
from TableA,
(
SELECT PARAM_VALUE  
from RDW_PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS'
) x
where business_date >=
(
select add_months( trunc(max(business_date), 'MM') , -x.param_value
) from TableA)
group by trunc(business_date, 'MM') ;
0
 
sunny82Author Commented:
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.
0
 
lwadwellCommented:
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.
0
 
sunny82Author Commented:
No, it only returns the value 5.
0
 
SujithData ArchitectCommented:
@sunny82 - Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now