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: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 02 Aug 2004 19:19:21 -0700
Message-ID: <1091499594.796241@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?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Aug 02 2004 - 21:19:21 CDT

Original text of this message

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