Is there a better way to code below SQL using Case within Select?

Question

I have the below sql in my procedure, but it is giving "single-row query returns more than one row" error when I use Case within a Select. so I split the query into multiple Selects and finally writing the output by union of all the temp tables I created above.

I used Select and Case but giving error "single-row query returns more than one row".

 OUTTAB = SELECT A.MATERIAL,
                 A.BOM,
                 A.PARENTBOM,
                 A.STEPNUM,
                 B.VALIDFROM AS PARENTBOMFR,
                 (CASE WHEN B.BOMCOUNT = 1
                       THEN '99991231'
                       WHEN B.BOMCOUNT >= 2
                       THEN (CASE WHEN B.VALIDFROM = (SELECT E.VALIDTO FROM :LT_TEMP1 AS E WHERE A.PARENTBOM = E.BOM)
                                  THEN '99991231'
                                  ELSE ( SELECT TO_DATS(ADD_DAYS(C.VALIDTO, -1)) FROM :LT_TEMP2 AS C
                                          WHERE A.PARENTBOM = C.BOM
                                            AND B.VALIDFROM = (SELECT D.VALIDFROM FROM :LT_TEMP3 AS D WHERE A.PARENTBOM = D.BOM))
                                   END)
                       ELSE '99991231'
                   END) AS PARENTBOMTO,
                 A.COMPONENT,
            FROM :INTAB AS A
           INNER JOIN :LT_TEMP AS B
              ON A.PARENTBOM  = B.BOM;

LT_TEMP1,2,3 are temp tables built for one level each.

I get "single-row query returns more than one row" error when I run above query. So I divided the 3 temp tables into one level by joining with INTAB and finally writing to OUTTAB by union of all 3 temp tables. This seems performance intensive for the system to handle.

Debugging is part of programmers job. And the error message couldn't get any clearer. Have you checked if your SELECTs return more than one row? If they are, then fix them so that they only return one row. — Eric

Niclas Mühl Niclas Mühl · Accepted Answer · 2019-10-11T12:11:19

When you have a Procedure and you are sure, that your Subselects should return just one value you can add ::rowcount = 0 at the end. So the Error with "single-row query returns more than one row" should not be returned anymore.

Also you could try to save all these Variables you check in the subselects in a Variable.

Declare the Variables at the very beginning of you code. And then declare them with the following syntax:

delcare date validTo; 
SELECT D.VALIDFROM INTO validTo FROM :LT_TEMP3 AS DINNER JOIN :INTAB as A on A.PARENTBOM = D.BOM ::rowcount = 0;

Do that for every of your subselects and check the Case Statements. Should increase performace and remove the error.

Is there a better way to code below SQL using Case within Select?

1 Answers