error: single-row query returns more than one row AMDP

[bhanuprakash5]

hi all,

iam trying to use coalesce function in IN condition

et_res = select carrid from scarr as a 
  where carrid in(
      select 
        coalesce( (select carrid from sflight),
                  (select carrid from scarr))
        as carrid FROM scarr);

please help me solve this issue

[matt]

1) Always use the code button of the editor when inserting code into a post. I've done it for you this time.

2) Why do you expect only one row?

[DoanManhQuynh · Accepted solution]

Hello.

Your select statement is impossible to run because what you writing is subquery after SELECT statement, not after WHERE.

in this case I think you should use JOIN instead of COALESCE. or you can try bellow query ( im not sure if it fit your requirement) ( if carrid not found, then will replaced by '--' which is special symbol then first subquery will fail, you can try another symbol 😞

 SELECT carrid FROM scarr 
 WHERE carrid IN ( SELECT COALESCE( carrid, '--' ) FROM sflight ) OR
 carrid IN ( SELECT COALESCE( carrid, '--' ) FROM scarr ) 
 INTO TABLE @DATA(itab).