Oracle LISTAGG() for querying use

Question

So I'm trying to make use of the LISTAGG() function to simply build a comma delimited list to use within an underlying query. The list generation works fine and I just applied an output for debug purposes where I can see my list as it should be:

VALUES: 'AB','AZ','BC','CA','CT','DC','FL','FO','GA','IL','KS','MA','MB','ME','MN','MS','MT','NB','NC','NL','NOVA SCOTIA','NS','NT','NU','NY','ON','ONTARIO','OR','PE','QC','QUEBEC','QUÉBEC','SASKATCHEWAN','SK','TX','VT','WA','YT'

When I try to pass this list variable to my query however just to see if anything will come back, nothing comes back, but if I copy / past the provinces / states list from above (as is) instead of using "v_Province" in my where clause, I get a result back. What am I doing wrong?

  DECLARE
     v_PROVINCE varchar2(500);
     v_results varchar2(1000);
  BEGIn
        dbms_output.enable(1000000);  

       Select '''' || LISTAGG(STATE, ''',''') WITHIN GROUP (ORDER BY STATE) || '''' PROV 
       INTO v_PROVINCE
       from (Select distinct STATE from ADDRDATA where STATE IS NOT NULL);

   DBMS_OUTPUT.PUT_LINE('VALUES: ' || v_PROVINCE);

   Select CITY
   INTO v_results
   from VWPERSONPRIMARYADDRESS
   where state in (v_Province)
   AND ROWNUM <= 1;

   DBMS_OUTPUT.PUT_LINE(v_results);


  END;
  /

Answer 1

Firstly, it is almost always more efficient to do everything in a single statement if at all possible.

Your second query doesn't work as you are returning everything into a single string. This is not a comma delimited list as required by an IN statement.

There is a little trick to get round this though. Assuming you are using the string for something between the two SELECT statements you can play around with regexp_substr() to turn your string into something usable.

Something like this would work;

select city
  from vwpersonprimaryaddress
 where state in ( 
           select regexp_substr(v_province,'[^'',]+', 1, level) 
             from dual
          connect by regexp_substr(v_province, '[^'',]+', 1, level) is not null
                  )

The variable v_province would have to be changed to be quoted twice, for instance '''AB'',''AZ'',''BC''' in order for this to work.

Here's a working example

Answer 2

What you're trying to do won't work, because the IN operator treats the comma-separated list as a single value. In theory, you could gather the values into a single string, then parse the string into individual values so your next query could interpret it. However, that would be a really bad idea.

A better idea would be to use an array to pass your list of values from the first query to the second:

create type nt_varchar_50 as table of varchar2(10)
/

DECLARE
     v_PROVINCE nt_varchar_50;
     v_results varchar2(1000);
     cursor cur_provinces is
        Select distinct STATE from ADDRDATA where STATE IS NOT NULL;
     i pls_integer;
BEGIN
     dbms_output.enable(1000000); 
     open cur_provinces;
     fetch cur_provinces bulk collect into v_PROVINCE;
     close cur_provinces;

     DBMS_OUTPUT.PUT('VALUES: ');
     for i in v_PROVINCE.first .. v_province.last loop
         if i <> 1 then
            DBMS_OUTPUT.PUT(', ');
         end if;              
         DBMS_OUTPUT.PUT(v_PROVINCE(i));
     end loop;
     DBMS_OUTPUT.PUT_LINE();

     Select CITY
     INTO v_results
     from VWPERSONPRIMARYADDRESS
     where state in (select * from table(v_Province))
     AND ROWNUM <= 1;

     DBMS_OUTPUT.PUT_LINE(v_results);
END;
  /

Of course, even this is vastly less efficient than use a single SQL statement in the first place. Really, you should only use this kind of technique if you need to do some sort of processing between the two queries that does not lend itself to SQL or, possibly, if you need to use the first result set multiple times.