selecting minimum date

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

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"