Routine Output to pipe delimited file

[somu_june]

Thanks Ray,

It worked after I used convert function.

Code: Select all

Ans=0   


PathFileName = FileDir:'/':FileName 

* OpenSeq File or Create File
         
OpenSeq PathFileName To FileVar
         Else Create FileVar Else Abort 

fvFilterDate = fvdate

* Set the Terminal Width wide to show more characters
     Call DSExecute('UV','TERM 170',rVal,rMsg)

*Get list of jobs that are modified from the date that the input parameter date is passed

cmd = \SELECT DS_JOBS.NAME FMT'60L', DS_AUDIT.CREATOR FMT'20L', DS_AUDIT.DTM FMT'20L', DS_AUDIT.MODIFIER FMT'20L'\ 
cmd := \ FROM  DS_AUDIT, DS_JOBS \
cmd := \ WHERE DS_JOBS.NAME = DS_AUDIT.INSTANCE \
cmd := \ AND DS_AUDIT.CLASS = '\
cmd := 2
cmd := \'\ 
cmd := \ AND SUBSTRING(DS_AUDIT.DTM FROM 1 FOR 10) = \
cmd := \'\
cmd := fvdate
cmd := \'\
*cmd := \ GROUP BY NAME, CREATOR, MODIFIER\
cmd := \;\
Anscmd = cmd
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode)
cmdOutput := Convert(@FM, char(10),vOutput)
EreplcmdOUtput := EREPLACE (cmdOutput,"Job name....................................................    Created by..........    Modified on.........    Modified by.........","")
*WriteSeq file to Output
WRITESEQ EreplcmdOUtput TO FileVar ELSE PRINT "CANNOT WRITE TO OUTPUT"
weofseq FileVar 
CloseSeq FileVar
ReturnAns = Ans
Return(ReturnAns)

In the above code, I'm replacing the header with blank lines, is there a way to remove the blank lines before I write to a sequential file.


Thanks,
Somaraju

[ray.wurlod]

Simple IF statement to control whether WRITESEQ is executed.

You can also put HEADER SUPPRESS at the end of the SQL query.

[somu_june]

Thanks Ray,

I tried with SUPRESS HEADER option as you mentioned, but I'm receiving an error message, so I commented the SUPRESS COLUMN HEADING statement, so I tried with unix sed command and its working fine. Please let me know if SUPRESS statement is wrong.


Ans=0


PathFileName = FileDir:'/':fvdsjobname:'_DSJOBAudit_Int.txt'

* OpenSeq File or Create File

OpenSeq PathFileName To FileVar
Else Create FileVar Else Abort

fvStartFilterDate = fvstartdate
fvEndFilterDate = fvenddate

* Set the Terminal Width wide to show more characters
Call DSExecute('UV','TERM 170',rVal,rMsg)

*Get list of jobs that are modified from the date that the input parameter date is passed

cmd = \SELECT DS_JOBS.NAME FMT'60L', DS_AUDIT.CREATOR FMT'20L', DS_AUDIT.DTM FMT'20L', DS_AUDIT.MODIFIER FMT'20L'\
cmd := \ FROM DS_AUDIT, DS_JOBS \
cmd := \ WHERE DS_JOBS.NAME = DS_AUDIT.INSTANCE \
cmd := \ AND DS_AUDIT.CLASS = '\
cmd := 2
cmd := \'\
cmd := \ AND DS_JOBS.NAME = \
cmd := \'\
cmd := fvdsjobname
cmd := \'\
cmd := \ AND SUBSTRING(DS_AUDIT.DTM FROM 1 FOR 10) >= \
cmd := \'\
cmd := fvStartFilterDate
cmd := \'\
cmd := \ AND SUBSTRING(DS_AUDIT.DTM FROM 1 FOR 10) < \
cmd := \'\
cmd := fvEndFilterDate
cmd := \'\
*cmd := \ SUPRESS COLUMN HEADING \
*cmd := \ GROUP BY NAME, CREATOR, MODIFIER\
cmd := \;\
Anscmd = cmd
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode)
cmdOutput := Convert(@FM, char(10),vOutput)
EreplcmdOUtput := EREPLACE (cmdOutput,"Job name.................................................... Created by.......... Modified on......... Modified by.........","")
*WriteSeq file to Output
WRITESEQ EreplcmdOUtput TO FileVar ELSE PRINT "CANNOT WRITE TO OUTPUT"
weofseq FileVar
CloseSeq FileVar
PathFileNameSkprcds = FileDir:'/':fvdsjobname:'_DSJOBAudit_Final.txt'
UnxToCmd = "sed -e '/records listed/d' -e '/^$/d' " : PathFileName : ">" : PathFileNameSkprcds
Call DSExecute("UNIX",UnxToCmd,output,retcode)
if retcode = 0 then PRINT "File Created Sucessfully with out blanks lines" else PRINT "File Creation Failed with out blank lines"
ReturnAns = Ans
Return(ReturnAns)


Thanks,
Somaraju

[ray.wurlod]

Tell me the exact error message you got from the SQL, or review the UniVerse SQL User's Guide (downloadable from Rocket Software).