Hi Gurus
I appreciate if someone help me out, I'm getting error single row subquery returns more than one row , I have the following data.
Sample data
WITH datum AS
(
SELECT to_date('25-SEP-2015','DD-MON-YYYY') first_dt ,48 case_id FROM dual
UNION ALL
SELECT to_date('26-SEP-2015','DD-MON-YYYY') ,48 case_id FROM dual
)
SELECT d.*
,(SELECT MIN(CASE WHEN d.first_dt>to_date('25-SEP-2015','dd-mon-yyyy') THEN d.first_dt ELSE NULL END ) OVER (PARTITION BY case_id) FROM datum WHERE case_id=d.case_id) min_analytical_fun
FROM datum d
WHERE first_dt=to_date('25-SEP-2015','dd-mon-yyyy');
Error
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
but when I use the logic by using sub query then I'm not getting any error, can someone explain this to me. Thanks in advance.
Sample data
WITH datum AS
(
SELECT to_date('25-SEP-2015','DD-MON-YYYY') first_dt ,48 case_id FROM dual
UNION ALL
SELECT to_date('26-SEP-2015','DD-MON-YYYY') ,48 case_id FROM dual
)
SELECT d.*
--,(SELECT MIN(CASE WHEN d.first_dt>to_date('25-SEP-2015','dd-mon-yyyy') THEN d.first_dt ELSE NULL END ) OVER (PARTITION BY case_id) FROM datum WHERE case_id=d.case_id) min_analytical_fun
,(
SELECT MIN(first_dt) FROM datum dd
WHERE dd.case_id =d.case_id
AND dd.first_dt>to_date('25-SEP-2015','DD-MON-YYYY')
) min_sub_query
FROM datum d
WHERE first_dt=to_date('25-SEP-2015','dd-mon-yyyy');
Query Output
FIRST_DT, CASE_ID, MIN_SUB_QUERY
25-SEP-15 48 26-SEP-15