CODEWITHSUNDEEP
oracle-databaselistagg
Prou Prou Tyu
Prou Prou Tyu

Reputation: 419

how to get the '' for each string in a listagg?

I have the following query :

 SELECT
  ix.dt AS DT,
  ix.UDBENCH_UDIDX  AS UDFO,
  ' .'  || REPLACE(REPLACE( ix.UDBENCH_UDIDX,' ',''),'IS','')  AS PF_TICKER, 
  i.szbez AS PORTFOLIO_NAME, 
  ix.rm_generic_inst_type_l1,
  ix.rm_generic_inst_type_l2,
  ix.scd_sec_type,
  m.ud_isin AS SECURITY_ID,
  '%' AS POS_TYPE,
  ix.sec_weight AS QUANTITY,
  ix.sec_ccy,
  ix.sec_price AS MKT_PRICE,
  '' AS COST_PX,
  '' AS POSITION_VALUE_AC,
  '' AS POSITION_VALUE_FC,
  m.ud_sedol AS UD_SEDOL,
  m.ud_bbgid AS UD_ID_BB_UNIQUE,
  m.ud_cusip AS UD_CUSIP,
  m.ud_bbgid AS UD_BBGID,
  m.inst_name AS INST_NAME,
  ix.idas AS IDAS,
  m.ud_scd_securityid AS UD_SCD_SECURITYID
FROM XXXX ix
INNER JOIN XXXXR i ON (i.udidx = ix.UDBENCH_UDIDX),
  XXXXX m  
WHERE  ix.dt >= to_date(sdt_start,'DD.MM.YYYY') 
AND ix.dt <= to_date(sdt_end,'DD.MM.YYYY')
AND ix.UDBENCH_UDIDX IN (select listagg( udfo,',') within group(ORDER BY udfo) 
                               from XXXXX where pf_ticker is null )
AND  i.szbez LIKE '%DFLT%'
AND ix.idas = m.idas;

I would like the part :

AND ix.UDBENCH_UDIDX IN (select listagg( udfo,',') within group(ORDER BY udfo) from XXXXX where pf_ticker is null )

Equivalent to : ix.UDBENCH_UDIDX IN ('blal','bll',blc') but it shows ix.UDBENCH_UDIDX IN (blal,bll,blc) and the result of my query is an empty table, do you know how to set listagg to have this result ( 'blal','bll',blc' instead of blal,bll,blc)? Thanks

Upvotes: 0

Views: 532

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

The IN operator doesn't work like that. You'd be comparing the UDBENCH_UDIDX values with a single string containing all udfo values, not all of the individual values of that column.

You can just use a subquery without the listagg():

AND ix.UDBENCH_UDIDX IN (select udfo from XXXXX where pf_ticker is null)

Or you can join to that table instead of using a subquery at all; something like:

FROM XXXX ix
INNER JOIN XXXXR i ON (i.udidx = ix.UDBENCH_UDIDX)
INNER JOIN XXXXX m  ON (m.udfo = ix.UDBENCH_UDIDX)
WHERE  ix.dt >= to_date(sdt_start,'DD.MM.YYYY') 
AND ix.dt <= to_date(sdt_end,'DD.MM.YYYY')
AND i.szbez LIKE '%DFLT%'
AND ix.idas = m.idas
AND m.pf_ticker is null;

... assuming the old-style join to XXXXX m is supposed to be getting the data related to the subquery you're doing - it's hard to tell with obfuscated names. (It's not a good idea to mix old and new style joins anyway; or to use old-style joins at all). It's possible you might want that to be an outer join, or the driving table, or something else - again can't infer that from the information provided.

If you already had a set of string literals to look for then you would do something like:

IN ('val1', 'val2', 'val3')

but you don't have string literals, you have string values from a table, which are not the same. You don't need to, and shouldn't, enclose those column values in single quotes within the query. The single quotes denote a literal value which is to be treated as a string; the values in the column are already strings.

You can actually do what you asked:

select '''' || listagg(udfo, ''',''') within group (order by udfo) || '''' from ...

which would give you a comma-separated list of quoted values from your table (or an empty string, which is the same as null, if there are no matching rows. If you were generating a statement to run later then that might make some sense, but that isn't the case here.

Upvotes: 0

Related Questions