Convert comma separated values into tables.

Is this possible without creating any new data type.

Your current query will produce a Cartesian product, it most probably needs a join

Why do you have to use 'with'?

What are you expecting this to do? "FROM TABLE (t.invnums)"
T is a common table expression (CTE) in your query, not a table function.

WITH T
AS (
	SELECT SUM(IH_INV_AMT)
		, IH_BATCH_ID
		, TO_CHAR(WM_CONCAT(IH_INV_NUM)) invnums
	FROM gcb_dba.invoice_header
	GROUP BY IH_BATCH_ID
	)
SELECT *
FROM gcb_dba.gcb_invoice_dtls
	, t
WHERE invoice_no IN (
		SELECT *
		FROM TABLE (t.invnums)
		);

Select allOpen in new window

"cannot access rows from a non nested table item"

you are actually trying to access the data from a non-nested table item

SELECT *
            FROM TABLE (t.invnums)

t.invnums must be a column of nested table object else you will get this kind of error,

if you have a nested table object matching with the invnums data object type then you can use "CAST" key word and make this work

something like this will work

SELECT * FROM TABLE( CAST(t.invnums AS <object_type_variable>))

also to your question

-->Is this possible without creating any new data type.

Yes, if any of existing object type satisfies the requirement, then you don't need other object to do this task, else you need to create one

Hi Portlet,

I want to use the comma separated values from the with clause query in
the main query in clause, so I tried to do something and it is not working.

Just give me a working solution.

Also I knew it would give me cartesian product, i will correct it later.

I think you want to read up this article:
https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
you will need a bit of preparation to be able to implement the function  get_rows_from_list I show and explain there, but it's really simple
hope this helps

also note, wm_concat is not documented or supported and known bugs.  There are better options.  Such as listagg or writing your own aggregate or using the collect function or using xml aggregation or writing a custom function

Hi all,

I will remove wm_concat no issues.

Let me explain my requirement in detail
This is my first query, which will retrieve three columns, in which one is a comma
separated value(invnums).

   SELECT SUM(IH_INV_AMT), IH_BATCH_ID, TO_CHAR(WM_CONCAT(IH_INV_NUM)) invnums
     FROM gcb_dba.invoice_header
    GROUP BY IH_BATCH_ID

I want to execute below query, in the IN clause of the below query,
 I want to pass the comma separated values from the first query.

    SELECT * FROM gcb_dba.gcb_invoice_dtls, t
    where invoice_no in ("Comma separated values from the first query")

I dont want to have multiple queries, that's the reason I used with clause,
You can give me some other idea if you have.

Sample data may not be helpful to you, my requirement is clear, from the comma separated values of the first query, need to execute the second query.

Let me know if you need more clarification.

actually, I don't see why you want a comma-separated list if you actually want to use a basic SQL feature...

    SELECT * 
 FROM gcb_dba.gcb_invoice_dtls, t
    where invoice_no in (
             SELECT IH_INV_NUM invnums
               FROM gcb_dba.invoice_header
             ) 

Select allOpen in new window

or am I missing something else?

Hi Guy Hengel,

I cannot use it that way, my requirement is different.

Just want to know how I can pass comma separated list in IN clause.

I read your article,it is very nice, and I am interested in XML function.

but on applying it like below

WITH T1 AS
 (SELECT SUM(IH_INV_AMT),
         IH_BATCH_ID,
         LISTAGG(IH_INV_NUM, ',')
         WITHIN GROUP (ORDER BY IH_INV_NUM) invnums
    FROM gcb_dba.invoice_header
   GROUP BY IH_BATCH_ID)
SELECT *
  FROM gcb_dba.gcb_invoice_dtls, t1
 WHERE invoice_no IN
       (SELECT extract(value(t), '/r/text()').getStringval()
          FROM table(xmlsequence(extract(xmltype(t1.invnums), '/t/r'))) t)
          and rownum =1;

I am getting the below error, Please help to get the results.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '8'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1

View program sources of error stack?

>I cannot use it that way, my requirement is different.
why? I don't see anything "different"?

anyhow: your code cannot work because the WITH statement is not generating a XML string, but just a comma-delimited string ... please reread the article and the suggested code carefully in regards to what you need as "input"

Can you please tell us why you are unable to join these 2 tables?
 gcb_dba.invoice_header
 gcb_dba.gcb_invoice_dtls

Those 2 tables have a strong  appearance of being related, quite possibly by IH_INV_NUM

e.g. as a guess

FROM  gcb_dba.gcb_invoice_dtls det
INNER JOIN  gcb_dba.invoice_header hed on det.IH_INV_NUM = hed.IH_INV_NUM

and once that simple relationship is formed you don't need to get clever with the rest of it.

I note you are still persisting with a query that produces a Cartesian product, although you claim you will fix it later. Why would you wait to fix such a basic flaw?