Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Weird SQL Error


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Sep 29, 2016 10:27 AM Last Post By: Dan Palley Threads: [ Previous | Next ]
Dan Palley

Posts: 43
Registered: 2/14/00
Weird SQL Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 27, 2016 12:18 PM
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
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Weird SQL Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 27, 2016 2:47 PM   in response to: Dan Palley in response to: Dan Palley
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)
Dan Palley

Posts: 43
Registered: 2/14/00
Re: Weird SQL Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 29, 2016 10:27 AM   in response to: Dan Palley in response to: Dan Palley
Thanks, Jeff, for the explanation. Do you feel this issue is a bug in Interbase?

Dan
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02