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

Les Gainous <lesgainous_at_earthlink.net> wrote:

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

I know it is a little ugly, but in PL/SQL you do have the option of using if -> elsif -> else -> end if, if you want to. Decode is only used in a DML statement to achieve a similar result.

>
>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!
>> >
Received on Sun Oct 20 1996 - 00:00:00 CDT