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 `SELECT`s return more than one row? If they are, then fix them so that they only return one row. — Eric, Aug 15 '19 at 16:46

score 0 · Answer 1 · answered Oct 11 '19 at 12:11

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 Answers1