Re: SQL Help!

Peter Rak wrote:
>
> Kathy Holley wrote:
> >
> > Is there a simple way of doing this:
> > My table has 2 column: Language, desc
> > I need to select desc where Language = 2, if no rows are returned, then I
> > need to select desc where Language = 1;
> > (Language =1 is the default and will always be there)
> > I wrote one select statement to do it, but it seems a bit complicated, I
> > use 1 sub-select with 'EXIST' and one union.
> > I am sure there must be a better way to do it without having to check for
> > nulls and sending a second query.
> >
> > Thanks in advance for your help!
>
> Hi,
>
> Try this:
>
> select desc from tab where lang=(select max(lang) from tab);
>
> Peter.

I'm assuming that the 2 you specified might change, so you could use the following

SELECT nvl(max(primary.desc), max(fallback.desc)) FROM tab primary, tab fallback
WHERE (primary.lang = :LanguageNumber) AND

      (fallback.lang = 1);

This would be fine, unless your language table is long. If it is, then you might want to use a statement that will allow the use of indexes:

SELECT nvl(primary.desc, fallback.desc)
FROM tab primary, tab fallback
WHERE (primary.lang (+) = :LanguageNumber) AND

      (fallback.lang = 1);

Where :LanguageNumber would be the number of the preferred language choice in both examples.

-- 
-------------------------------------------------
Ken Johnson -  Senior Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com