Use listagg result in WHERE clause of other SELECT-CodePudding

I have a LISTAAGG RESULT

SELECT LISTAGG(TO_NUMBER(ORG_IDORGA), ',') WITHIN GROUP (ORDER BY ORG_IDORGA) listaid FROM SGP_ORGA WHERE ORG_IDORGA_P = 201

The result is an string (201,202,203)

I want to use this result in the where clause of my next select. The problem is the new field im filtering from is a number.

Select * From tabla2 where fieldNumber in resultStringFromListAgg;

CodePudding user response：

That's a wrong approach. Why would you first aggregate some values and then worry about extracting them to format that's suitable to be used in an IN list?

What you need is just an ordinary join:

select b.* 
from tabla2 b join sgp_orga s on s.org_idorga = b.fieldnumber
where s.org_idorg_p = 201

CodePudding user response：

declare

V_SQL   varchar2(2000 char);
V_SQL1   varchar2(2000 char);
V_SQL2   varchar2(2000 char);

begin

V_SQL := '
SELECT LISTAGG(TO_NUMBER(ORG_IDORGA), '','') WITHIN GROUP (ORDER BY ORG_IDORGA) 
listaid FROM SGP_ORGA WHERE ORG_IDORGA_P = 201';

V_SQL1 := 'Select * From tabla2 ';

 
V_SQL2 :=  V_SQL1 || ' where fieldNumber in (' || V_SQL || ' ) ';

V_SQL2;

end;