query contd

In continuation with the below query:

http://www.experts-exchange.com/Database/Oracle/Q_28335810.html


Sdstuber..Is there a way to get result it as one value rather than multiple records

I just want the result as a value not the records, the above example gives me 3 records instead of record with:
TGT.COL_B = SRC.COLB
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D

Thanks
gs79Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
try this...


SELECT id, listagg('TGT.' || str || ' = ' || 'SRC.' || str,' ') within group( order by str)
  FROM (SELECT id,
               REGEXP_SUBSTR(
                   col_str,
                   '[^ ,]+',
                   1,
                   n
               )
                   str
          FROM yourtable,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(
                   col_str,
                   '[^ ,]+',
                   1,
                   n
               )
                   IS NOT NULL
        MINUS
        SELECT id,
               REGEXP_SUBSTR(
                   key,
                   '[^ ,]+',
                   1,
                   n
               )
          FROM yourtable,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(
                   key,
                   '[^ ,]+',
                   1,
                   n
               )
                   IS NOT NULL) 
group by id

Open in new window

0
 
sdstuberCommented:
I aggregated them above with a space between each.
If you want them in a single string but each on their own line

then change the ' ' in LISTAGG to chr(10)


LISTAGG('TGT.' || str || ' = ' || 'SRC.' || str, chr(10))

on windows you might want to use

LISTAGG('TGT.' || str || ' = ' || 'SRC.' || str, chr(13) || chr(10))

example of chr(10) below


SELECT id, listagg('TGT.' || str || ' = ' || 'SRC.' || str,' ') within group( order by str)
  FROM (SELECT id,
               REGEXP_SUBSTR(
                   col_str,
                   '[^ ,]+',
                   1,
                   n
               )
                   str
          FROM yourtable,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(
                   col_str,
                   '[^ ,]+',
                   1,
                   n
               )
                   IS NOT NULL
        MINUS
        SELECT id,
               REGEXP_SUBSTR(
                   key,
                   '[^ ,]+',
                   1,
                   n
               )
          FROM yourtable,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(
                   key,
                   '[^ ,]+',
                   1,
                   n
               )
                   IS NOT NULL)
group by id 

Open in new window

0
 
gs79Author Commented:
I am also trying to generate the onclause for the merge query which is basically second part of the minus query. I am getting the results as follows:

TGT.COL_A = SRC.COL_A
TGT.COL_B = SRC.COL_B
TGT. = SRC.
TGT. = SRC.

here is the query I am using:
SELECT  listagg('TGT.' || str || ' = ' || 'SRC.' || str,chr(13)) within group( order by str)
from(
SELECT REGEXP_SUBSTR(key, '[^ ,]+',1,n) str
          FROM drop_test_tab,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(col_str, '[^ ,]+',1,n)  IS NOT NULL
)  

I am getting 4 rows with last two rows as blank from the inner query. I think 'IS NOT NULL' is intended to filter out the blank rows which is not happening

SELECT REGEXP_SUBSTR(key, '[^ ,]+',1,n) str
          FROM drop_test_tab,
               (    SELECT LEVEL n
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
         WHERE REGEXP_SUBSTR(col_str, '[^ ,]+',1,n)  IS NOT NULL

The inner query gives the following result:
COL_A
COL_B
NULL
NULL

Please let me know how to resolve this..

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
you have 2 different first parameters in the REGEXP_SUBSTR


key vs col_str


key should be in both parts of one half of the MINUS
and col_str should be in both parts of the other half


don't put both in both
0
 
gs79Author Commented:
Thanks..

It worked..

Is there a way to display as comma seperated. I can append a comma but it displays on the last line which is not needed:

TGT.COL_B = SRC.COL_B ,
TGT.COL_C = SRC.COL_C ,
TGT.COL_D = SRC.COL_D

Thanks
0
 
gs79Author Commented:
further massaging the listagg output with substr /length function i was able to remove the comma in the end..

Thanks
0
 
sdstuberCommented:
just add the comma to the aggregate function's delimiter parameter  (the 2nd parameter)

so, if you're using chr(13) as your delimiter
then change...

  listagg('TGT.' || str || ' = ' || 'SRC.' || str, chr(13))

to

  listagg('TGT.' || str || ' = ' || 'SRC.' || str, ',' || chr(13))



no need to do any substr/length parsing, let the aggregate do it for you
0
 
sdstuberCommented:
Do you need anything else?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.