ORA-01427: single-row subquery returns more than one row while displaying the output

0
votes

I want to display 3 output with some condition for that particular output only not for entire query. so I used the sub-query in select statement.

I tried below code

--CREATE TABLE i86813_dt190429 as 
                   SELECT 
                   /*+ use_hash(RAP01 RAA02 RAP06) */ 
                   DISTINCT 'I86813' AS audit_id,
                   rap01.plcy                 AS plcy,
                   rap01.stuscd,
                   raa02.enddt_t              AS enddt,
                   rap01.j01_pt_line_cat_cd   AS j01_pt_line_cat_cd,
                   rap01.j01_pt_cdb_part_id   AS j01_pt_cdb_part_id,
                   rap01.j01_pt_state_cd      AS j01_pt_state_cd,
                   rap06.sctype,
                   raa02.EACPRC,

                   (SELECT rap06.pstsc
                     FROM rap06
                     JOIN raa02
                   ON rap06.plcy = raa02.plcy
                      AND raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )
                   ) AS pstsc_before_ea  --one day before EA
                   ,
                   (
                   SELECT rap01.aap
                     FROM rap01
                     JOIN raa02
                   ON rap01.plcy = raa02.plcy
                      AND raa02.enddt_t - 1 = rap01.enddt_t
                   ) AS aap_before_ea        --one day before EA and after EA
                   ,
                   (
                   SELECT rap01.aap
                     FROM rap01
                     JOIN raa02
                   ON rap01.plcy = raa02.plcy
                      AND raa02.enddt_t > rap01.enddt_t
                   ) AS aap_after_ea
--
from RAP01
--
Join RAA02 
ON raa02.j46_pt_line_cat_cd    = rap01.j01_pt_line_cat_cd
AND raa02.j46_pt_cdb_part_id   = rap01.j01_pt_cdb_part_id
AND raa02.j46_pt_state_cd      = rap01.j01_pt_state_cd
AND raa02.plcy                 = rap01.plcy
AND raa02.EACPRC               = '25'                    --channel of processing.
AND raa02.ahevnt               = '0993'                  -- ??
and raa02.sprodt_t             BETWEEN '13-AUG-2018' and '14-APR-2019'
--
left JOIN RAP06
ON RAP06.J42_PT_LINE_CAT_CD    = RAP01.J01_PT_LINE_CAT_CD
AND RAP06.J42_PT_CDB_PART_ID   = RAP01.J01_PT_CDB_PART_ID
AND RAP06.J42_PT_STATE_CD      = RAP01.J01_PT_STATE_CD
AND RAP06.PLCY                 = RAP01.PLCY
AND RAP06.SCTYPE               = '085'
AND RAA02.enddt_t              BETWEEN RAP06.ENDDT_T AND  (RAP06.DROPDT_T  - 1)     
--
WHERE rap01.j01_pt_line_cat_cd = 'A'
AND rap01.co3 || rap01.line3 IN (
'065010',
'010010',
'027010',
'021010',
'386010',
'065019',
'010019',
'027019',
'021019',
'386019'
)
AND RAP06.PLCY is NULL;

I got error as 'ORA-01427: single-row subquery returns more than one row' and '01427. 00000 - "single-row subquery returns more than one row"'

Could you please suggest the solution.

2
sqloraclesubquery
If you are using subquery in main select then it should return single value. – ch2019
test Group by and don't use distinct for this query... – Amirhossein
"test Group by and don't use distinct for this query... " Yuk GROUP BY suggestion for data unduplication and without using aggregate functions, but i guess its a matter of taste.. Also topicstarter i advice you to read Why should I provide an MCVE for what seems to me to be a very simple SQL query? for providing example data and expected results if you want better comments and or answers. – Raymond Nijland

2 Answers

0
votes

If you are using subquery in main select then it should return single value. Looks like you are passing the date range filter which is retuning multiple rows.

AND raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )

You can join these subqueries as derived table to the base table.

0
votes

In your select statement you have 3 subqueries to populate the columns pstsc_before_ea, aap_before_ea and aap_after_ea:

.....
                   (SELECT rap06.pstsc
                     FROM rap06
                     JOIN raa02
                   ON rap06.plcy = raa02.plcy
                      AND raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )
                   ) AS pstsc_before_ea  --one day before EA
                   ,
                   (
                   SELECT rap01.aap
                     FROM rap01
                     JOIN raa02
                   ON rap01.plcy = raa02.plcy
                      AND raa02.enddt_t - 1 = rap01.enddt_t
                   ) AS aap_before_ea        --one day before EA and after EA
                   ,
                   (
                   SELECT rap01.aap
                     FROM rap01
                     JOIN raa02
                   ON rap01.plcy = raa02.plcy
                      AND raa02.enddt_t > rap01.enddt_t
                   ) AS aap_after_ea

each of them should return only 1 row.
But it looks like at least 1 of them is returning more.
You can avoid the error by using something like:

SELECT max(rap01.aap)...

if this suits your logic.