SQL question

["Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

OK, I'm confused. 

 

I have the following SQL statement (works with or without a common table
expression).

 

with temp as ( 

select a.jobcode, a.effdate, a.scale,

       b.effdate, b.pos, b.rate

from kronosdta/krjcwsxr a

join lg/krwscale b on (a.scale=b.scale and

     b.effdate = (select max(z.effdate) from lg/krwscale z

     where b.scale=z.scale and z.effdate <= '2004-08-11'))

where a.jobcode=9156

  and a.effdate = (select max(z.effdate) from kronosdta/krjcwsxr z

      where a.jobcode=z.jobcode and z.effdate <= '2004-08-11')

) 

select * from temp

 

gives the expected results:

 

Job code  Effective date  Wage scale  Effective date  Scale position  Hourly
rate

  9,156     2004-07-05        F         2004-07-05           10
13.92 

  9,156     2004-07-05        F         2004-07-05           20
14.05 

  9,156     2004-07-05        F         2004-07-05           30
14.27 

  9,156     2004-07-05        F         2004-07-05           40
14.39 

  9,156     2004-07-05        F         2004-07-05           50
14.58 

  9,156     2004-07-05        F         2004-07-05           60
14.76

 

The goal of the exercise is to get the minimum hourly rate for a job code
with a floor of 14.50. In this scenario, the correct answer is 14.58.

 

When I code

...

Select * from temp

Where rate >= 14.50

 

Job code  Effective date  Wage scale  Effective date  Scale position  Hourly
rate

  9,156     2004-07-05        F         2004-07-05           50
14.58 

  9,156     2004-07-05        F         2004-07-05           60
14.76

 

However, If I code

...

Select min(rate) from temp

 

Or 

...

Select min(rate) from temp

Where rate >= 14.50

 

I get a null value.

 

This is not what I expected.

I coded this as a common table expression to ease testing, but will work as
a normal select statement (with the same results).

 

Do any of you SQL gurus have any advice for me?

 

Thanks,

Loyd

 

 

 

Loyd Goodbar

Senior programmer/analyst

BorgWarner

E/TS Water Valley

662-473-5713