DSXchange

Posted: **Fri Nov 03, 2006 9:50 am**

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 AND f3# = Value3 AND f4# = Value4
GROUP BY f1#,f2#,f3#,f4#,f5#,F6#;

Another form of query is:

SELECT f1#,f2#,f3#,MAX(f4#),f5#,MAX(f6#)
FROM TAB1
GROUP BY f1#,f2#,f3#,f4#,f5#,F6#
HAVING f1#=Value1 AND f2# = Value2 AND f3# = Value3 AND f4# = Value4.

The Value1 , Value2, Value3 adn Value4 are the values of F1#,F2#,F3#,F4# of the Hash File.

please explain how to achieve it in my job design.

Thx.

Posted: **Fri Nov 03, 2006 9:52 am**

In Server jobs use an Aggregator stage for grouping and aggregate functions.

Sort the input data for best performance.

Posted: **Fri Nov 03, 2006 9:56 am**

ray.wurlod wrote: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 will come from the Hashed File.

Posted: **Fri Nov 03, 2006 10:52 am**

Code: Select all

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	.

Posted: **Fri Nov 03, 2006 5:19 pm**

You might get it by using UtilityHashlookup routine. What you will need to do is create a seperate job for loading the hashed file and then pass the values in the hashed file to your current design as a parameter through the above mentioned routine.

DSXchange

Group By in DB2 Stage and Ref from hash File

Group By in DB2 Stage and Ref from hash File

An Example