Re: using CASE in SELECT

Use NVL() and outer join (+) like this

SELECT cr.id

,           nvl(fac.cl,'N')
        ||  nvl(fac.ht,'N')
        ||  nvl(fac.cf,'N')
        ||  nvl(fac.hn,'N')
        ||  nvl(fac.nl,'N')
FROM   cr
,             fac

Jan-Pier

"csb" <uhavemeat_at_hotmail.com> wrote in message news:e20bec7f.0302272115.2231b5b_at_posting.google.com...
> i have two tables :
>
> cr
>
> id f
> ----------
> a1 y
> a2 n
> a3 n
> a4 y
> a5 y
> a6 n
>
> and fac
>
> id cl ht cf hn nl
> -------------------------------
> a1 y n y n n
> a4 n y y y y
> a5 y y n y y
>
> fac will have a record only if cr.f = 'Y'
> fac.hn and fac.nl = 'Y' onlY if fac.ht = 'Y' else it is 'n'
> I would now like to get the out put as follows:
>
> (1)
>
> if(cr.f = 'Y')
> then cl||ht||cf||hn||nl
> else
> 'NNNNN'
>
> How do i query using a single SELECT with CASE statement
> to get the above output?
> I am using Oracle 9i on Linux 8.
> I tried using the query as given below
>
> SELECT c.id,c.f,
> f.ht,
> CASE
> WHEN (c.f = 'Y')
> THEN f.cl||f.ht||f.cf||hn||nl
> else 'NNNNN'
> END hot
> FROM cr c,fac f
> WHERE
> c.id = 'a2'
> AND f.id = c.id
>
> It works only if there is a record in fac, of course.Otherwise
> it gives 'no rows selected' msg, insted of 'NNNNN' that i require.
> ie. I need to get the output('NNNNN') even if there is no record in
> fac for an id in cr.
>
> (2)
>
> if(cr.f = 'Y')
> if(fac.ht = 'Y')
> then cl||ht||cf||hn||nl
> else cl||ht||cf
> else
> 'NNN'
>
> How should the query be to get the above o/p?
> Can some body help pl?
> Thanks
> csb
Received on Sat Mar 01 2003 - 21:25:10 CET