| single row subquery returns more than one row [message #643568] |
Mon, 12 October 2015 05:30  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi,
I am getting the below error in the below query .
select 'MTD' as Denom_Text,(count(1)*8) as Count_1 from HRBO.DIM_PERIOD
where cal_date>=to_date( (select substr(WEEk_NO,1,3) from dim_date where weekend_date in (to_date('08/03/2015','mm/dd/yyyy')-1) )
||'/01/'||substr('08/03/2015',7,10),'mm/dd/yyyy')
and cal_date<=to_date('06/08/2015','mm/dd/yyyy')-1
and replace(to_char(cal_date,'DAY'),' ','') not in ('SATURDAY','SUNDAY')
Single row subquery returns more than one row
The reason for the error is that the above subquery which is having select statement is giving 2 records having SUBSTR(week_no,1,3) as JUL and SUBSTR(week_no,1,3) as AUGI wanted only the max row to be passed to
How to handle this error in subquery and the subquery should return only the max row which is AUG
Please appreciate your help sir
Regards
|
|
|
|
|
|
| Re: single row subquery returns more than one row [message #643570 is a reply to message #643569] |
Mon, 12 October 2015 05:56  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi Sir,
Thanks for the update.
The problem with using max in the subquery is the subquery is returning two rows of which one is JUL and the second value is AUG. if I use max in the subquery it is going to return JUL. But I need data of AUG month.Also the point is if subquery for month returns isOCT and NOV. In this case MAX value will be NOV instead of OCT.
Appreciate your help on this sir.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
