DSXchange

A SQL Record F1# F2# F3# F4# 1 2 3 4 1 2 4 5 2 3 4 3 2 3 5 8 2 3 6 9 3 4 1 2 4 1 2 3 Hash File f1 f2 1 2 2 3 3 4 4 1 Output F1# F2# Max(F3#)Max(F4#) 1 2 4 5 2 3 6 9 3 4 1 2 4 1 2 3 Max values are found from the set of values returned by matching F1# with f1 and F2# with f2 .

This is an example, hope it expalins sth: A SQL Record (reference link) F1# F2# F3# F4# 1 2 3 4 1 2 4 5 2 3 4 3 2 3 5 8 2 3 6 9 3 4 1 2 4 1 2 3 Hash File (Source or Stream Link) f1 f2 1 2 2 3 3 4 4 1 for f1=1 and f2=3, from 'A' I get F1# F2# F3# F4# 1 2 3 4 1 2 4 5 So I want the below output for eac...

chulett wrote:Your issue is a little... hard to follow. How about some examples? Post some samples of input data and what you want to come out the other end.

Please chk my post, topic = 'Group By in DB2 Stage and Ref from hash File ', and see if that explains sth.

Hi, In my Job, corresponding to values from a Seq File, my Database will return say 10 rows. Now i want to do gp by on these 10 rows. Similarly for the next row of data coming from the File, another set of some rows will be returned and a Gp by will be done on them too. I dont have to combine the Gp...

In Server jobs use an Aggregator stage for grouping and aggregate functions. Sort the input data for best performance. ... Hi Ray, My reqmt is that I dont want to do a grp by on the complete data, I just wanna do a gp by on the set of data returned by the WHERE clause, and the values for comparison...

Hi, I have a Hash File which contains 8 fields: {F1 #, F2 #,F3 #,F4 #} and another DB2 Rec, TAB1{f1#,f2#,f3#,f4#,f5#,F6#,f7,f8....} (Fn # and fn #represents Key Fields) Now my reqmt is output as of these queries: SELECT f1#,f2#,f3#,MAX(f4#),f5#,MAX(f6#) FROM TAB1 WHERE f1#=Value1 AND f2# = Value2 AN...

kduke wrote:Not really. You have to determine which is new in the job and then insert else update. These 2 need to be 2 different links.

Plz tell me how to determine in the job itself whether the row is new or existing one?

Hi, My targt table has 2 fields: CREATED_EW_DTTM and LASTUPD_EW_DTTM. Now, I am using 'Update Existing or Insert New' action for writing to this table. So, my aim is: If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp. Else if it is jus...

ray.wurlod wrote:All that's fine. You just end up making more use of scratch disk, which will slow execution.

If you have an external sort utility, or your own sort routine, you might invoke that instead. For exa ...

I cannot read ur reply as it is marked as 'Premium Content'.

Hi, I am using Sort Stage in my job. Its output is an input to an Aggregator stage. The Help of Sort Stage specifies: 1) Max Rows in Virtual Memory : The maximum number of rows (from 2 to 50,000) 2) Max Open Files . = 20. So in all I can sort just 50,000 * 20 = 1,000,000 rows. But I ahev more than t...

DSguru2B wrote:Please search (Exact) on key words "Row out of sequence"

used SORT stage and the problem is resolved.
Thx!

I am getting the below error in my job:

J_JrnlFacts_v20..Aggregator1: Row out of sequence
At row 5, link "Err"
Row out of sequence

The job aborts immediately...Please xplain!

PS_JRNL_HEADER - Sounds like a Peopleosft Functionality. Does your job has a input link and output link from the table (DRS) to a shared container? Are you viewing data on the output link from the DRS?? As far as i know the shared container which you are talking about should get only one rows (Max ...

Also copy paste the SQL in the SQL tool you have and run it and see if it returns any rows or not. When I run the SQl, I get the desired result. Also, when I do 'View Data' from the stage, I can see the desired rows being returned.... BUT When I run the job, 0 rows read from PS_JRNL_HEADER_Link (DB...

No WHERE clause in the DB2 stage? Please check the generated SQL (in the job log). ... Thsi is the FROM Clause: EPM1PA.PS_JRNL_HEADER H,EPM1PA.PS_JRNL_LN LN,EPM1PA.PS_MB_ASCNTLDTTM DT,EPM1PA.PS_MB_JRNL_LN_SIS SIS This is the WHERE clause.... DT.MB_ASCNTLJOBNAME ='#JOBNAME#' AND H.JRNL_HDR_STATUS = ...