Re: Select query using case function

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. Received on Tue Aug 03 2004 - 03:16:47 CDT