Solved

Convert comma separated values into tables.

Posted on 2014-04-21
12
847 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 143

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 77

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
 
LVL 74

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 143

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 143

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 59
Oracle Nested table uses ? 2 59
Oracle Mulit-site configuration 28 69
oracle sql developer + get hour from datetime field 2 22
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.

738 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