using CASE in SELECT

From: csb <uhavemeat_at_hotmail.com>
Date: 27 Feb 2003 21:15:37 -0800
Message-ID: <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 Fri Feb 28 2003 - 06:15:37 CET