gs79
asked on
query contd
In continuation with the below query:
https://www.experts-exchange.com/questions/28335810/query-to-parse-string.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
https://www.experts-exchange.com/questions/28335810/query-to-parse-string.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
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
ASKER
further massaging the listagg output with substr /length function i was able to remove the comma in the end..
Thanks
Thanks
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
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
Do you need anything else?
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
Open in new window