Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select query using case function
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