Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: -> Help me w/this built-in function!

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

From: Les Gainous <lesgainous_at_earthlink.net>
Date: 1996/10/20
Message-ID: <326AE264.4D79@earthlink.net>

Gordon E. Hooker wrote:

>> <...snip>  

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

>> <snip...>

Gordon, but can I use and IF structure in place of a column in a select statement (I don't think I can):

select	col1, 
	if col2 = x then y else z end if,
	col3,
	col4

from mytable
where blah...blah...blah

??

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US