Need to Find Maximum Value in a Certain Column
Moderators: chulett, rschirm, roy
Need to Find Maximum Value in a Certain Column
Before Loading Data to a Target DB2 Table I need to check if "Column A" exists in the Target DB2 Table. If it exists then I need to add one to the Value of a "Column B". For Example in a case like this:
Target :
Column A Column B
A1234 1
A1234 2
A1234 3
Source :
Column A
A1234
Column B
Need to Perform a routine to check the target and derive the correct value ( which is 4)
If I am using a lookup of the target..is there a function to find out the maximum value of column B in the Target ?
Also if anyone has an efficient approach to my objective please share
Thanks
Target :
Column A Column B
A1234 1
A1234 2
A1234 3
Source :
Column A
A1234
Column B
Need to Perform a routine to check the target and derive the correct value ( which is 4)
If I am using a lookup of the target..is there a function to find out the maximum value of column B in the Target ?
Also if anyone has an efficient approach to my objective please share
Thanks
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Use a sparse look up 'of the the target' with an SQL like:
SELECT Column_A, max(Column_B) as Column_B FROM <Table_Name> group by Column_A
SELECT Column_A, max(Column_B) as Column_B FROM <Table_Name> group by Column_A
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
... or preload a Lookup File Set with the results of the same query. I repeat it here in case Josh edits his post to add a WHERE clause!
Code: Select all
SELECT Column_A, max(Column_B) as Column_B FROM <Table_Name> group by Column_A ;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A sparse lookup is where you do a lookup against the DB2 table.
You are not doing this. You are loading a result set from executing that query into a virtual Data Set, and performing the lookups against that. This is called a normal lookup.
You are not doing this. You are loading a result set from executing that query into a virtual Data Set, and performing the lookups against that. This is called a normal lookup.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
It was a server job
Mea culpa ! Can we keep this between us
Anyway OP started a new post related to this I think.
![Confused :?](./images/smilies/icon_confused.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
Anyway OP started a new post related to this I think.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>