Dan Palley wrote:
I am having trouble running the following query in IBConsole:
select
(
Select Sum(P.Amount)
From Payment P
Where
P.agency_linkno=B.Agency_LinkNo and
P.Bank_LinkNo=B.BankNo and
P.DateCleared is not null and
P.PaymentDate > :ToDate
) SubtractBalance
from bank B
where B.agency_linkno=:AgencyNo
P.PaymentDate is TIMESTAMP, B.Agency_LinkNo is INTEGER.
When I run the SQL, I enter the value of '12/31/16' for :ToDate and 0 for :Agency_LinkNo.
I get the following error:
Warning
"12/31/16" is not a valid integer value.
OK
If I remove the :AgencyNo parameter and just specify '0', the query runs normally.
This is Interbase XE 7. I get a similar error when running the query via FireDAC.
Thanks,
Dan Palley
Sabre
It is because IB does a strange ordering for the parameters. Most DBS (all?)
will order left to right top to bottom so param(0) is :ToDate and Param(1) is
:AgencyNo. InterBase though does the where clause first then goes back and does
embedded parameters in the select (and that includes where clauses that have
select statements in them). So this gets complex.
So for InterBase param(1) is :ToDate and Param(0) is :AgencyNo, but TParams will
parse left to right top to bottom. IBX (and I assume FireDAC) allows you to
change the ordering of the TParams (not something you normally do) to the
correct order that IB expects. InterBase does not support named parameters so
the parameters must fit the right "slot" regardless of name.
AFAIK, IBConsole (or IBExpert) allows reordering of parameters, so I suggest
using a constant in the select section and only parameterize the where clause
for testing, you can use parameters and fix the ordering in your app.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Connect with Us