Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.

Public Report
Report From: InterBase/SDK/IBX    [ Add a report in this area ]  
Report #:  38473   Status: Open
Parameters with subqueries are parsed incorrectly
Project:  InterBase Build #:  8.0.0.2
Version:    8.0 Submitted By:   Wayne Niddery
Report Type:  Basic functionality failure Date Reported:  1/8/2007 4:19:50 PM
Severity:    Commonly encountered problem Last Updated: 3/20/2012 2:24:39 AM
Platform:    All platforms Internal Tracking #:   248788
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: (2 Total Ratings)
3.00 out of 5
Total Votes: 11
Description
This has been encountered for a long time now, here are details from a post dated 15 Feb 2002:

SELECT CUST_NO, CUSTOMER, CONTACT_FIRST
(SELECT MAX(CURRENCY) FROM COUNTRY WHERE COUNTRY = :PSCNT)
FROM CUSTOMER WHERE CUST_NO > :PICUST

There are four situations in which two will work and two will not. (using
ParamByName)
1. Two parameters: query will not execute
2. Parameter PSCNT and a value instead of PICUST: query will execute
3. Parameter PICUST and a value instead of PSCNT: query will not execute
4. No parameters, only values: query will execute

The error is '-303 Error conversion from string "value from PSCNT"'.

If I use an IBDataSet and Params[i] I'll get the right result if I put the
value for PICUST in Params[0] and the value for PSCNT in Params[1]. So it
seems Interbase is handling the parameters in the main-query first and the
parameters in nested query's next.

Steps to Reproduce:
Using a dataset component such as IBSQL, or IBDataset:

SELECT CUST_NO, CUSTOMER, CONTACT_FIRST
(SELECT MAX(CURRENCY) FROM COUNTRY WHERE COUNTRY = :PSCNT)
FROM CUSTOMER WHERE CUST_NO > :PICUST

There are four situations in which two will work and two will not. (using ParamByName)
1. Two parameters: query will not execute
2. Parameter PSCNT and a value instead of PICUST: query will execute
3. Parameter PICUST and a value instead of PSCNT: query will not execute
4. No parameters, only values: query will execute

The error is '-303 Error conversion from string "value from PSCNT"'.

If I use an IBDataSet and Params[i] I'll get the right result if I put the
value for PICUST in Params[0] and the value for PSCNT in Params[1]. So it
seems Interbase is handling the parameters in the main-query first and the
parameters in nested query's next.
Workarounds
None
Attachment
None
Comments

Ian Fear at 10/14/2008 2:29:12 PM -
this problem is a real pain.  Is it Interbase or TIBQuery doing it wrong ?? any chance of some one looking into it ??

Server Response from: ETNACODE01