[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Geert GruwezOracle 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
 
sujith80Commented:
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
 
sujith80Commented:
@sunny82 - Glad to help!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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