Re: Cool but ugly SQL

[Narayanan R Pillai <strqst400@xxxxxxxxx>]

For problem 1: Can you do something like MAX( Date( SHH_LCDATE || 
SHH_LCTIME ) ) ?

You will have to get the format correct though for the "SSH_LCDATE || 
SHH_LCTIME " field.

Pillai

Dan wrote:
> I'm told this can be done.  But I'm having a bugger of a time figuring this
> out.  And it's starting to be crunch time.  My file layout:
>
>  SHH_LIB    Char         10
>  SHH_FILE   Char         10
>  SHH_MEMBER Char         10
>  SHH_LCDATE Date         10
>  SHH_LCTIME Time          8
>  SHH_HASH   Char         20
>
> The file key is the first five fields, which makes the key unique.
> Duplicate values for the first three key fields are allowed and expected.
> My task is to select the Lib/File/Member with the highest timestamp (LCDATE
> & LCTIME), omitting any duplicate Lib/File/Member with earlier timestamps.
> I'm also selecting certain library and file values.  Here is my current SQL:
>
>   Select * from SRCMBRHASH where
>         shh_Lib = 'PRODSRCE' and shh_File LIKE 'Q%'
>     and shh_LCDate = (Select Max(shh_LCDate) from SRCMBRHASH
>       where shh_Lib = 'PRODSRCE' and shh_File LIKE 'Q%' )
>    Order by shh_Lib, shh_File, shh_Member, shh_LCDate desc,
>              shh_LCTime desc
>
> This "works", as in no syntax errors, but the result set is way too small.
> I would expect every unique Lib/File/Member to appear (approximately 100),
> but I'm only getting three rows.
>
> Problem 1: How do I select the MAX of two fields, LCDate and LCTime?  (I
> know, should be a real timestamp field, but that's a fix for the next
> version.)
> Problem 2: I think I need to compare shh_Lib to shh_Lib, shh_File to
> shh_File, and shh_Member to shh_Member from the two selects in the
> subselect.  But how?
>
> TIA,
> Dan
>