An alternate way to get a comma separated list of values from a table

QUERY SAMPLE


with data
as
(
select myvalues, row_number() over (order by myvalues) rn, count(*) over () cnt
from
(
select VC_EMAILADDESS myvalues from TBL_EMAIL_ADDRESSES
)
)

select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
INTO p_vc_to
from data
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;





RESULT

P_VC_TO = item1,item2


SOURCE

http://www.quest-pipelines.com/pipelines/plsql/tips.htm#JULY

sometimes even though

I ran across another interesting point today I wanted to share with you. I guess the point is sometimes even though something is not in the list to use, it doesn’t always mean that you can’t use it. Case in point is the target filter for delete for a target. Notice I have a constant declared called OUTPUT2.















When I create the filter, you can see the OUTPUT2 is not in the list, but guess what? It works. See the gen script below.










OPTIONS (BINDSIZE=50000,ERRORS=50,ROWS=200,DIRECT=TRUE,READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE '{{ODS0_SOURCE_FIN.RootPath}}{{/}}XXXX.dat'
BADFILE '{{ODS0_SUPPORT_BAD.RootPath}}{{/}}XXXX.bad'
DISCARDFILE '{{ODS0_SUPPORT_DISCARD.RootPath}}{{/}}XXXX.dis'
DISCARDMAX 0
CONCATENATE 1
INTO TABLE "TBL_BALANCE_FILES"
REPLACE
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '"'
(
"DT_DWLOADDATE" EXPRESSION "TO_DATE( FUNC_GET_CURRENT_RUN_DATE() , 'YYYY/MM/DD')",
"VC_SUB_SYSTEM_NAME" CONSTANT 'FIN',
"DT_DATE_PERIOD" POSITION(1) INTEGER EXTERNAL "TO_DATE( :\"DT_DATE_PERIOD\" , 'YYYYMMDD')" ,
"VC_TABLE_NUMBER" INTEGER EXTERNAL ,
"VC_TABLE_DESC" CHAR ,
"N_TRANS_DATA_ROW_COUNT" INTEGER EXTERNAL ,
"N_TRANS_DATA_UNITS" DECIMAL EXTERNAL ,
"N_TRANS_DATA_DOLLARS" DECIMAL EXTERNAL
)