RE: SQL question

Charles,
We are reasonably up-to-date on PTFs.

But thanks ! for the SQL change. Adding the temp2 worked perfectly. I'm
curious why that works, but glad it does.

Thanks,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: CWilt@xxxxxxxxxxxx [mailto:CWilt@xxxxxxxxxxxx] 
Sent: Thursday, August 12, 2004 08:50
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL question

Loyd,

It looks ok to me.  Are you up to date on PTFs? 

Just as an experiment, try doing the following

with temp as ( 
                     <original code>
                    )
       ,temp2 as ( select * from temp where rate >= 14.50)

select min(rate) from temp2

and see what happens.


Charles

              

> -----Original Message-----
> From: Goodbar, Loyd (ETS - Water Valley) 
> [mailto:LGoodbar@xxxxxxxxxxxxxx]
> Sent: Wednesday, August 11, 2004 10:07 PM
> To: Midrange Systems Technical Discussion
> Subject: SQL question
> 
> 
> 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