Re: selecting minimum date

[Ray Ontko <rayo@ONTKO.COM>]

Jenny,

Your first select should be fine.  It's perfectly appropriate to
select a minimum date in a subselect and compare that directly
to another date.   You don't need to convert it to a Julian
date, or to a number, or anything like that to do a min() on it.

Are you sure that ALL &ingreds have an approval_date.  Your
subselect could be returning NULL for some &ingred values,
in which case no row would be selected in your main query.

Ray

P.S. Although I think you don't need it at all, "to_num" should
probably be "to_number".

> 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"
>

----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "RO&C: data movers and shakers."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/