DS_AUDIT issue with group by clause

[somu_june]

Hi All,

I have a server routine to get the auditing information like job name, creator and modifer name and last last modified timestamp for a particular date. I'm using below Transform function. But I'm getting an error message

DataStage/SQL: Individual column specifications are illegal with a
single-group table

Can some onr help me in how to resolve this issue.

Code: Select all

Ans = ""  
DataBack = ''
ConcatList = ''
***FileName=srch:InterationNumber:'_search.txt'
****FilePath = DirName:"/":FileName
fvFilterDate = fvdate

**OpenSeq FilePath to Temp.FVar
    ***Else Create Temp.FVar Else Abort
* 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_AUDIT.INSTANCE FMT'60L', DS_AUDIT.CREATOR FMT'20L', MAX(DS_AUDIT.DTM) FMT'20L', DS_AUDIT.MODIFIER FMT'20L'\ 
cmd := \ FROM UNNEST DS_AUDIT ON MODS \
*cmd := \ WHERE DS_JOBS.NAME = DS_AUDIT.INSTANCE \
cmd := \ WHERE DS_AUDIT.CLASS IN ('2','7')\
*cmd := 2
*cmd := \'\ 
cmd := \ AND SUBSTRING(DS_AUDIT.DTM FROM 1 FOR 10) = \
cmd := \'\
cmd := fvdate
cmd := \'\
*cmd := \ GROUP BY INSTANCE, CREATOR, MODIFIER\
cmd := \;\
Anscmd = cmd
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode)

***DataBack = Job name'l#70':"|":Created by'l#20':" ":"|":Modified on'l#20':" ":"|":Modified by'l#20'
**Ans := Field (vOutput,@FM,3)
Ans := vOutput

Output

TEST #1
*******

Arg1 = 2014-05-07

Test completed.


Result = DataStage/SQL: Individual column specifications are illegal with a
single-group table


Thanks,
Somaraju

[somu_june]

Hi,

Sorry its my mistake I removed comment from the below statement

*cmd := \ GROUP BY INSTANCE, CREATOR, MODIFIER\


but I'm facing an new issue


Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.

New Code:

Code: Select all

Ans = ""  
DataBack = '' 
ConcatList = '' 
***FileName=srch:InterationNumber:'_search.txt' 
****FilePath = DirName:"/":FileName 
fvFilterDate = fvdate 

**OpenSeq FilePath to Temp.FVar 
    ***Else Create Temp.FVar Else Abort 
* 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_AUDIT.INSTANCE FMT'60L', DS_AUDIT.CREATOR FMT'20L', MAX(DS_AUDIT.DTM) FMT'20L', DS_AUDIT.MODIFIER FMT'20L'\
 cmd := \ FROM UNNEST DS_AUDIT ON MODS \ 
*cmd := \ WHERE DS_JOBS.NAME = DS_AUDIT.INSTANCE \ 
cmd := \ WHERE DS_AUDIT.CLASS IN ('2','7')\ 
*cmd := 2 
*cmd := \'\ 
cmd := \ AND SUBSTRING(DS_AUDIT.DTM FROM 1 FOR 10) = \ 
cmd := \'\ 
cmd := fvdate 
cmd := \'\ 
cmd := \ GROUP BY INSTANCE, CREATOR, MODIFIER\ 
cmd := \;\ 
Anscmd = cmd 
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode) 

***DataBack = Job name'l#70':"|":Created by'l#20':" ":"|":Modified on'l#20':" ":"|":Modified by'l#20'
 **Ans := Field (vOutput,@FM,3) 
Ans := vOutput

Thanks,
Somaraju