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: Wed, 04 Aug 2004 01:24:32 GMT
Message-ID: <Xns953AD912F4FE5pobox002bebubcom@204.127.199.17>


pobox002_at_bebub.com (Martin Burbridge) wrote in news:45a06b65.0408030713.197aed73_at_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
> 

Sorry I didn't notice its missing an 'end' i.e.

> 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)
> End
>  and sysdate
> ORDER BY tran_date
Received on Tue Aug 03 2004 - 20:24:32 CDT

Original text of this message

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