Need to Find Maximum Value in a Certain Column

cosec · Post by **cosec** » Tue Jun 19, 2007 10:43 pm

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

JoshGeorge · Post by **JoshGeorge** » Tue Jun 19, 2007 11:25 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Jun 20, 2007 1:07 am

... 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 ;

cosec · Post by **cosec** » Wed Jun 20, 2007 2:11 am

Currently I am doing a lookup on the Target DB2 Table selecting MAX(Column2)....

What is a sparse lookup ?

ray.wurlod · Post by **ray.wurlod** » Wed Jun 20, 2007 5:54 am

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.

chulett · Post by **chulett** » Wed Jun 20, 2007 6:53 am

Sheesh, what's with all the PX answers to a Server question posted in the Server forum?

ray.wurlod · Post by **ray.wurlod** » Wed Jun 20, 2007 6:58 am

Joshy started it!!!

chulett · Post by **chulett** » Wed Jun 20, 2007 7:00 am

How old are you? 8? Don't make me stop this car!

ray.wurlod · Post by **ray.wurlod** » Thu Jun 21, 2007 6:28 am

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.

JoshGeorge · Post by **JoshGeorge** » Thu Jun 21, 2007 10:36 pm

It was a server job

Mea culpa ! Can we keep this between us

Anyway OP started a new post related to this I think.