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.
Group By in DB2 Stage and Ref from hash File
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Hi Ray,ray.wurlod wrote:In Server jobs use an Aggregator stage for grouping and aggregate functions.
Sort the input data for best performance. ...
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.
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
An Example
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 .