Re: selecting minimum date

[Robert Buford <robertb@ABBASYS.COM>]

Hi Jennifer,

Have you looked at the actual database record to see what its datatype is,
what some of the data looks like?  Sounds like you're kinda shooting in the
dark right now.

Bob


> -----Original Message-----
> From: Discussion of SQR, SQRIBE Technologies's database reporting
> language [mailto:SQR-USERS@list.iex.net]On Behalf Of Jennifer Wagner
> 301-827-5467 FAX 301-594-6463
> Sent: Thursday, August 12, 1999 9:30 AM
> To: Multiple recipients of list SQR-USERS
> Subject: selecting minimum date
> Sensitivity: Confidential
>
>
> Howdy all,
>
> You were such a great help last time, I thought I'd give it a shot again
> (someday I hope to be able to return the help, but you all know about
> 100% more than I do currently!!).
>
> Okay, now I'm working on a program that will output the first
> introduction of an ingredient on the market.  As you can guess, an
> ingredient can be in a zillion different products.  So, what I want is
> the first approval date for that ingredient.
>
> I tried doing it like this:
>
> where....
> and approval_date = (select min(approval_date) from table where
> ingred_no = &ingred)
>
> &ingred, of course, was defined earlier in the main select statement.
> Okay, that returned zilch.  I was told that I needed to transform the
> approval date to number.  So, I tried this (because I was told that it
> should be a Julienne number):
>
> select....
>
> approval_date
> to_char(approval_date) &app
>     let #app = &app
>
> where...
> approval_date = (select min(#app)...)
>
> didn't work.
>
> I even tried:
>
> where
> approval_date = (select min(to_num(to_char(approval_date,'J')))
> and got a "invalid column" error for "to_num"
>
> Any suggestions??
>
> Thanks for your help!!!
>
> Jenny
>
> PS to Wayne -- I hope this doesn't cause a confidential notice.  I
> switched it to "non-restricted"
>