[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Convert comma separated values into tables.

I have a query a below like this,

   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 ));

I want to use the comma separated values in the "WITH" clause invnums in the IN clause of the select statement.

When I use it this way, I am getting error " cannot access rows from a non nested table item"


In what way I can do this?
0
sakthikumar
Asked:
sakthikumar
  • 4
  • 3
  • 2
  • +3
1 Solution
 
sakthikumarAuthor Commented:
Is this possible without creating any new data type.
0
 
PortletPaulCommented:
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)
		);

Open in new window

0
 
Wasim Akram ShaikCommented:
"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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
sakthikumarAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you want to read up this article:
http://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
0
 
slightwv (䄆 Netminder) Commented:
I'm not really seeing the need for the WITH.  Could you not achieve the desired results with a normal join?

You should also not use WM_CONCAT.  It is well documented to be an undocumented/unsupported function that should not be used.  There are many ways to generate a CSV from a column that doesn't use WM_CONCAT.  For example: LISTAGG in 11g.

If you can provide sample data and expected results I'm sure we can help with the query.
0
 
sdstuberCommented:
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
0
 
sakthikumarAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
             ) 

Open in new window


or am I missing something else?
0
 
sakthikumarAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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"
0
 
PortletPaulCommented:
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?
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now