Re: selecting minimum date

[Jennifer Wagner 301-827-5467 FAX 301-594-6463 <WAGNERJ@CDER.FDA.GOV>]

I got it guys!  Thanks.  I was joining wrong in the subwhere section...I
saw it from this post.  Don't remember exactly what it was, but I'll
post the subselect statement here so you can see it...

and p.approval_date = (select min(p.approval_date) from product p,
formulation f
where f.dprf_ingred_no = 'XXXXXXXXX' and p.appl_no = f.appl_no
and p.appl_type = f.appl_type and p.product_no = f.product_no)

I think it has to do with the fact that I was putting from product pb --
creating a second synonym for this subselect statement.  Whatever, this
is what worked!!

Thanks!!

Jenny



>Is the main select from the same table as the sub select?  If so, then
>try something like this:
>
>from table t1
>where....
>and t1.approval_date = (select min(approval_date) from table t2 where
>t2.ingred_no = t1.ingred_no)
>
>Mike Olson
>
>
>> -----Original Message-----
>> From: Jennifer Wagner 301-827-5467 FAX 301-594-6463
>> [SMTP:WAGNERJ@CDER.FDA.GOV]
>> Sent: Thursday, August 12, 1999 8:30 AM
>> To:   Multiple recipients of list SQR-USERS
>> Subject:      selecting minimum date
>>
>> 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"