sql ora-01427 error single query returning multiple rows

Question

Query:

SELECT DISTINCT st_str_hry.id_str_rt,
  decode(co_strgp.nm_strgp,'HOLLAND & BARRETT','H&B','GNC UK','GNC')brandName
FROM co_strgp,
     st_str_hry
WHERE co_strgp.id_strgp=st_str_hry.id_strgp
  AND co_strgp.nm_strgp in(nvl(:brandName,
                                 (SELECT co_strgp.nm_strgp
                                  FROM co_strgp
                                  WHERE id_strgp_lv='2'
                                    AND id_strgp in
                                      (SELECT DISTINCT id_strgp
                                       FROM st_str_hry
                                       WHERE id_str_rt in
                                           (SELECT DISTINCT id_str_rt
                                            FROM NBTY_TR_LTM_AER_RTN)))));

here the nvl part is throwing ora-01427 error which says single query returning multiple rows.

Answer 1

Rewrite the part of the code containing the subquery into this:

AND co_strgp.nm_strgp in(
           ( SELECT nvl( :brandName,co_strgp.nm_strgp )
             FROM co_strgp
             WHERE id_strgp_lv='2'
               AND id_strgp in
                   (SELECT DISTINCT id_strgp
                    FROM st_str_hry
                    WHERE id_str_rt in
                          (SELECT DISTINCT id_str_rt
                            FROM NBTY_TR_LTM_AER_RTN)))));