Solved

Convert comma separated values into tables.

Posted on 2014-04-21
12
822 Views
Last Modified: 2014-05-19
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
Comment
Question by:sakthikumar
  • 4
  • 3
  • 2
  • +3
12 Comments
 

Author Comment

by:sakthikumar
ID: 40014099
Is this possible without creating any new data type.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40014143
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40014301
"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
 

Author Comment

by:sakthikumar
ID: 40014551
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40014569
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40014660
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40014669
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
 

Author Comment

by:sakthikumar
ID: 40016835
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40016866
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
 

Author Comment

by:sakthikumar
ID: 40016981
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40016990
>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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40017046
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now