SET COLSEP not inserting character if column is null or empty - please help
Hello experts,
This is probably a beginner question and I can't seem to find the answer. I am trying to spool output from a simple SQL query to a .txt or .csv file using colsep as the column separator which prints fine except when there is an empty column value. Here is my SQL:
set recsep off
set linesize 240
set newpage 0
set pagesize 9999
set colsep ';'
BREAK ON MESSAGE_SET_NBR SKIP 1 ON LAST_UPDATE_DTTM
Select D.MESSAGE_SET_NBR ,S.DESCR,D.MESSAGE_NBR, D.LAST_UPDATE_DTTM, D.MESSAGE_TEXT
from PSMSGCATDEFN D, PSMSGSETDEFN S
WHERE
D.MESSAGE_SET_NBR = S.MESSAGE_SET_NBR AND D.MESSAGE_SET_NBR BETWEEN 999 AND 19999
ORDER by D.MESSAGE_SET_NBR, D.MESSAGE_NBR ASC, S.DESCR
Here is an example of the data that is generated. The missing separator really screws up my outfile spacing.Please note the last line which is missing the colsep for the missing date.
18177;Applicant Tracking Messages ; 2582;13-APR-07;Some of the applicants, %1, do not have an email address in order
;Applicant Tracking Messages ; 2613;07-FEB-07;Applicants Being Rejected
;Applicant Tracking Messages ; 2614;04-APR-07;Select the employee to whom the Applicant is being routed
;Applicant Tracking Messages ; 2615;07-MAR-07;This source name is in use.
;Applicant Tracking Messages ; 2616;02-JUL-09;This subsource name is in use.
;Applicant Tracking Messages ; 2617;12-OCT-10;Proficiency is not specified for one or more Competency.
;Applicant Tracking Messages ; 2618 ;Please enter a a value in the Competency field before proceeding.
Thank you in advance for your help!.....Jason
Edited by: user957626 on Jun 16, 2011 2:40 PM