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

Home -> Community -> Usenet -> c.d.o.server -> Re: Select query using case function

Re: Select query using case function

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 3 Aug 2004 08:13:44 -0700
Message-ID: <45a06b65.0408030713.197aed73@posting.google.com>


georgina.wren_at_eu.watsonwyatt.com (George) wrote in message news:<d45230d0.0408030016.76fc9ca6_at_posting.google.com>...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1091499594.796241_at_yasure>...
> > George wrote:
> >
> > > Hi,
> > >
> > > I'm having a few problems getting my head round this select query. I
> > > know the basics of sql, but this is just a bit too complex for me.
> > >
> > > I need to select data from two tables and one of the conditions is to
> > > select the transaction date between two dates. I need to include a
> > > case function (or at least I think I do?), to determine what the first
> > > date will be:
> > >
> > > So where the sysdate month is greater than 10 i.e. month of Oct
> > > then the transaction date is equal to '01-Oct-YY', YY - needs be
> > > current year.
> > > else the transaction date is equal to '01-Oct-YY', YY - needs be
> > > previous year.
> > >
> > > The second date is the current date i.e. sysdate
> > >
> > > Here is what I have so far: Can anyone help with this?
> > >
> > > SELECT a.resource_code ASSOCIATE, b.description PROJECT_CODE,
> > > a.tran_date START_DATE
> > > FROM actrans a, acactivity b, dual c
> > > WHERE a.resource_type = 'E'
> > > AND a.acct_category = 'TIME'
> > > and a.activity like '9-WW-357852%'
> > > and b.activity = a.activity
> > > a.tran_date between
> > > Case when (trunc(sysdate, 'MM')) > 10
> > > then '01-Oct' || to_char(sysdate, 'YY')
> > > else '01-Oct' || to_char(sysdate, 'YY'- 1)
> > > and sysdate
> > >
> > > ORDER BY tran_date
> > >
> > > Thanks
> > > George
> >
> > Biuggest problem I see with your query is the use of DUAL. Get it out of
> > there. What is it you think it is accomplishing?
>
> Hi,
>
> It's accomplishing nothing, I left that in there by mistake! I can't
> see why dual would be affecting my query anyway as it doesn't relate
> to anything. I just need some help with the 'case ' part of the query
> - is this the correct date format etc.
>
> Thanks in advance.

Try this

SELECT a.resource_code ASSOCIATE, b.description PROJECT_CODE, a.tran_date START_DATE
FROM actrans a, acactivity b
WHERE a.resource_type = 'E'

AND a.acct_category = 'TIME' 
and a.activity like '9-WW-357852%'
and b.activity = a.activity

a.tran_date between
Case when (extract(month from sysdate)) > 10  then add_months(trunc(sysdate,'Y'),9)
 else add_months(trunc(sysdate,'Y'),-3)
 and sysdate
ORDER BY tran_date

Hth

-- 
Martin

this email is full of spam and never read
Received on Tue Aug 03 2004 - 10:13:44 CDT

Original text of this message

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