Forum Stats

  • 3,768,167 Users
  • 2,252,755 Discussions
  • 7,874,480 Comments

Discussions

Passing LISTAGG values into Incluase

User_BU3NG
User_BU3NG Member Posts: 94 Blue Ribbon

I am able to get rows into columns following query :

 select listagg(UC_SELECTED_COLUMN, ',' ) within group (order by UC_SELECTED_COLUMN) from TABLE_TEST group by UC_SELECTED_TABLE order by UC_SELECTED_TABLE;

In addition to this, i need to pass above query into In CLAUSE of another query. Can you please help me?

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    Hi, @User_BU3NG

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    i need to pass above query into In CLAUSE of another query.

    That sounds like a roundabout, compicated way to do things. You can put the query itself into an IN sub-query, like this:

    IN (
        SELECT uc_selected_column
        FROM   table_test
      )
    

    or you could use a correlated IN sub-query, like this:

    IN (
        SELECT uc_selected_column
        FROM   table_test
        WHERE  uc_select_table = m.table_name
      )
    

    This will be simpler and more efficient than packing all the values into a string, and then unpacking them again.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond
    Accepted Answer


    The key thing to note about what you're doing is that you are NOT turning rows into columns. You are turning rows of data into an aggregated string i.e. a single string with the data all joined together as one thing.

    IN clauses in statements expect multiple values. Your string is a single value. Don't expect Oracle to magically understand that just because you have a single string with commas in it, that it should split that string up and treat each part of the string as separate values... it won't.

    You mistake is so common there's even a well known Tom Kyte post about it: https://asktom.oracle.com/Misc/varying-in-lists.html

    Frank Kulash

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    Hi, @User_BU3NG

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    i need to pass above query into In CLAUSE of another query.

    That sounds like a roundabout, compicated way to do things. You can put the query itself into an IN sub-query, like this:

    IN (
        SELECT uc_selected_column
        FROM   table_test
      )
    

    or you could use a correlated IN sub-query, like this:

    IN (
        SELECT uc_selected_column
        FROM   table_test
        WHERE  uc_select_table = m.table_name
      )
    

    This will be simpler and more efficient than packing all the values into a string, and then unpacking them again.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond
    Accepted Answer


    The key thing to note about what you're doing is that you are NOT turning rows into columns. You are turning rows of data into an aggregated string i.e. a single string with the data all joined together as one thing.

    IN clauses in statements expect multiple values. Your string is a single value. Don't expect Oracle to magically understand that just because you have a single string with commas in it, that it should split that string up and treat each part of the string as separate values... it won't.

    You mistake is so common there's even a well known Tom Kyte post about it: https://asktom.oracle.com/Misc/varying-in-lists.html

    Frank Kulash