Subject RE: [ib-support] Calling all SQL Gurus...
Author Kevin Stanton
Hi Martijn,
Yes, I've been thinking about changing the table.

What I'm trying to do is get all the months >= than 8 for the year 2001 and
all the months <= 7 for the year 2002. I thought with the proper
parenthesis in place I could achieve this.

I think I will modify the table like you said and just create a true date
field - either first or last day of the month would work as I'm really only
interested in the month/year.

Also, H.SalesAmt is verified on a before insert trigger to make sure the
amount is at least zero.

Thanks for the replies.


-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@...]
Sent: Thursday, August 22, 2002 8:51 AM
Subject: Re: [ib-support] Calling all SQL Gurus...

Hi Kevin,

> I have a history table with two fields: yr & mo (year and month)
> small ints.
> Possibly have a design problem here.
> When running a report, a user wants to select the date range from 8/1/01
> thru 7/31/2002.
> I've been trying various Selects but can't quite get it.
> The below does not select anything:

Well that makes sense... (see below)

> H.COMPANY = '01' AND DIVISION = '01'
> and ((H.Yr >= 2001 and >=8) and (H.Yr <= 2002 and <=7))

How can a H.Mo be >=8 and <=7 at the same time? Or at least, that's what
I think is happening... Reading all your parenthesis makes be do this:

and ( ( A and B ) and ( C and D ) )

which, I believe, can be written as:

and A and B and C and D

And there you have it - (B and D) will never return True.

ok - multiple possibilities here...

One thing could be to adjust the table in such a way that it stores
full dates - this way you could simply say:

H.DateField > '2001/08/01' and H.DateField < '2002/07/01'

That sure is the option I would go for.

Can you change the table or is this a no-go?

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird

Upscene Productions

"This is an object-oriented system.
If we change anything, the users object."

To unsubscribe from this group, send an email to:

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

[Non-text portions of this message have been removed]