Re: -> Help me w/this built-in function!

Dave,

Thanks for your help. I come from the Sybase/MS SQL Server world and subqueries are allowed just about anywhere!

I also like using the type of logic you use in:

        decode(sign(count(q.id)-3),-1,'Not Qualified','Qualified')

There is a book I just read that takes advantage of a lot of this type of thinking.

BTW, if Oracle had a "CASE" or "SWITCH" statement structure, I wouldn't need the DECODE function. Is it that my version (7.1) doesn't have CASE and does Oracle plan to include such a structure in future versions?

Thanks!

-- 
 Les Gainous, lesgainous_at_earthlink.net

 Visit my web page at
      http://home.earthlink.net/~lesgainous

 Looking for a Client-Server job in California?
      http://home.earthlink.net/~lesgainous/jobs.html


Dave Workoff wrote:


> 

> You could try these:

> 

> 1) "Qualified" if exactly 3 qualifications exist:

>    Select e.id,

>              e.lastname,

>              e.firstname,

>              decode(count(q.id),3,'Qualified','Not Qualified')

>       from employee e, qualifications q

>     where e.id = q.id (+)

> group by e.id, e.lastname, e.firstname;

> 

> 2) "Qualified" if at least 3 qualifications exist:

>    select e.id,

>              e.lastname,

>              e.firstname,

>              decode(sign(count(q.id)-3),-1,'Not Qualified','Qualified')

>       from employee e, qualifications q

>     where e.id = q.id (+)

> group by e.id, e.lastname, e.firstname;

> 

> select statements, or sub-queries are not allowed in the column list of

> another query.

> 

> Hope this helps.

> Dave Workoff

> 

> ----------

> > From: Les Gainous <lesgainous_at_earthlink.net>

> > Newsgroups: comp.databases.oracle; comp.databases.oracle.server;

 comp.databases.oracle.misc


> > Subject: -> Help me w/this built-in function!

> > Date: Saturday, October 19, 1996 1:42 AM

> >

> > I'm new to Oracle (but not new to SQL databases) and wonder...why can't I

 do the following with the DECODE() function?:


> >

> > select        e.id

> >       e.lastname,

> >       e.firstname

> >       decode((select count(*) from qualifications q where q.id =

 e.id),3,'Qualified','Not Qualified')


> > from  employee e

> >

> >

> > I want the following and if the employee has three entries in the

> qualifications table, then I want the phrase 'Qualified' and if not, the

> 'Not Qualified':

> >

> > id    lastname        firstname

> > -------       --------------- --------------  --------------

> > 123   Smith           Jim             Not Qualified

> > 765   Doe             John            Qualified

> > 444   Garner          Debbit          Qualified

> > 231   Smith           Robin           Not Qualified

> > 885   Lopez           Rick            Not Qualified

> >

> >

> > The SQL statement won't work as constructed above. Is it that the

> expression "select count(*)..." is a complex expression? It works for a

> simple expression, such as when specifying a column name. It even works

> with a "simplified" complex expression, such as "2 + 1". In this case the

> "Qualified" gets displayed. If I put "5 + 1" then "Not Qualified" is

> displayed. This is correct...but what about a complex expression like a

> sub-query?

> >

> > Thanks in advance!

> >