Page 1 of 1
Need to Find Maximum Value in a Certain Column
Posted: Tue Jun 19, 2007 10:43 pm
by cosec
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
Posted: Tue Jun 19, 2007 11:25 pm
by JoshGeorge
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
Posted: Wed Jun 20, 2007 1:07 am
by ray.wurlod
... 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 ;
Posted: Wed Jun 20, 2007 2:11 am
by cosec
Currently I am doing a lookup on the Target DB2 Table selecting MAX(Column2)....
What is a sparse lookup ?
Posted: Wed Jun 20, 2007 5:54 am
by ray.wurlod
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.
Posted: Wed Jun 20, 2007 6:53 am
by chulett
Sheesh, what's with all the PX answers to a Server question posted in the Server forum?
Posted: Wed Jun 20, 2007 6:58 am
by ray.wurlod
Joshy started it!!!
Posted: Wed Jun 20, 2007 7:00 am
by chulett
How old are you? 8? Don't make me stop this car!
Posted: Thu Jun 21, 2007 6:28 am
by ray.wurlod
You're only young once but there's no statute of limitations on immaturity.
To be old and wise you first have to be young and stupid.
Posted: Thu Jun 21, 2007 10:36 pm
by JoshGeorge
It was a server job
Mea culpa ! Can we keep this between us
Anyway OP started a new
post related to this I think.