Re: select statement within decode

What you want to do is only possible in that form from 8.1 onwards, although you can address the problem differently in earlier versions.

If you are on 8.1, then the error in the syntax is the attempt to do a Boolean type of decode, which Oracle doesn't understand. You need a simpler expression, something like:

decode(record_date,

        (select min(record_date)
             from MyTable t
             where t.GroupID = MyTable.GroupID
        )    , 'First' ,
               'Subsequent'

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jeff Weston wrote in message ...
>I had the following in access which basically calculated for every record
>whether it was the first record within any groupID (there would never be 2
>records for the same group on the same date).
>
>SELECT MyTable.GroupID, MyTable.record_date, IIf([record_date] In (select
>min([record_date]) from MyTable t where t.GroupID = [MyTable].[GroupID]
>group by t.GroupID),'First','Subsequent') as FirstOrSubsequent
>FROM MyTable
>
>I've tried to reproduce it in Oracle using decode as below, but get the
>error 'ORA-00907: missing right parenthesis' every time I try to use a
>select statement for the expression.
>
>select GroupID, record_date,
>decode((record_date In (select min(record_date) from MyTable t where
>t.GroupID = MyTable.GroupID group by t.GroupID)),-1,'First',0,'Subsequent')
>as FirstOrSubsequent
>from MyTable
>
>I know I could probably do this with some kind of stored procedure, but the
>DB is on an FM contract so I can't write any procedures / temp tables etc.
>
>Is it possible to use a SELECT within a decode, and if so, please could
>someone give me the correct syntax
>
>TIA
>Jeff
>
>
Received on Sat Mar 25 2000 - 11:21:02 CST