OK - I'm developing a report and I needed an SQL select coded to get records from the tfgld106 table (Finalized Transactions).

I originally wrote my SQL query as:


select tfgld106.*
from tfgld106
where
tfgld106.leac INRANGE {:leac.f} and {:leac.t}
and tfgld106.fyer INRANGE {:fyer.f} and {:fyer.t}
and tfgld106.fprd INRANGE {:fprd.f} and {:fprd.t}

Where the leac.f/leac.t is the General Ledger Acct # range (From/To)
fyer.f/fyer.t is the Fiscal Year Range (From/To)
fprd.f/fprd.t is the Fiscal Period Range (From/To)


This worked just fine until we hit 2011 and when the report is run with a range like:

Fiscal Year FROM: 2010 TO: 2011
Fiscal Period FROM: 10 TO: 1

Obviously my SQL didn't account for this because of the Fiscal Period Range.

So - I modified the SQL to the following:


select tfgld106.*
from tfgld106
where
tfgld106.leac INRANGE {:leac.f} and {:leac.t}
and (tfgld106.fyer >= :fyer.f and tfgld106.fprd >= :fprd.f )
and (tfgld106.fyer <= :fyer.t and tfgld106.frpd <= :fprd.t)


For some reason - this Select Statement didn't return any rows - which I'm sure are there.

Can anyone see something I'm not doing right?
Does anyone have any suggestions or comments?

It seems to me that the logic in the SQL looks right....
It's been a long day and I'm afraid my mind is scattered right now...

Thanks in advance!

Hi,

Your First Select Query is Correct, the only thing that has to be taken care of is your Fiscal Period Range..

Fiscal Period Range has to be in Ascending Order and not descending ...

Fiscal Period Range : 1 to 10 - Correct
and
Fiscal Period Range : 10 to 1 - Incorrect


Cheers,
Abattoir

To make this work for multi-year queries, you would need to make the "and" between the two parenthetical statements into an "or". However, that then causes an issue when you are doing a single year query.

You could do an if statement "if fprd.t > fprd.f then" and execute the statement for the particular situation, but if you have a lot in your selectdo, that needs to be duplicated and that could get to be ugly code. Trying to think of a way to do this in a single select. I'll update if I think of something.

Good luck!

Vince

select tfgld106.*
from tfgld106
where (tfgld106.fyer > :fyer.f or (tfgld106.fyer= :fyer.f and tfgld106.fprd >= :fprd.f) )
and (tfgld106.fyer < :fyer.t or (tfgld106.fyer= :fyer.t and tfgld106.fprd <= :fprd.t ) )

Or you could that^.

Thank you VERY much Sameer.don and Vincent,
This Query worked very nicely!

Did just what I was describing!

And it looks like all 3 of you read this replied within minutes of each other.
Again Many Many THanks!

Will do the trick even more nicely

where {tfgld106.fyer, tfgld106.fprd} between {:fyer.f, :fprd.f} and {:fyer.t, :fprd.t}