At 07:19 AM 19/04/2005 -0700, you wrote:

>Hi Helen,
>
>This query populates on-hand inventory into PI_INQ. After upgrading to IBO
>4.5B, the on-hand numbers were no longer inserted.
>
>After some testing (took a while to figure out), I found replacing :USERID
>with 'KEVIN' (or the literal name of the user logged in to my app), my
>on-hand numbers came back.

It doesn't make sense to me, unless the params are getting scrambled. You
previously had a very old IBO that well pre-dated the changes Jason did
last year in 4.3Aa, to have IBO detect whether it was connecting to a
Firebird 1.5 server. This is because 1.5 fixed a very old InterBase/Fb 1.0
bug that scrambled params under some conditions. Now, IBO de-scrambles the
params only in those old versions. Maybe you have found a hole in that fix...

However, I must say I can't see anything in your insert spec that looks
like on-hand numbers....

I wouldn't have thought you could make a parameter out of that select
<constant_value> syntax. Did you use it like that before? It's kind of
doubtful (to me) that the statement could stay prepared if you changed the
actual select statement (as you are attempting with this
syntax). Remember, parameters are not variables; and for DML statements
they should be WHERE criteria.

But *if* if so happens that the UserID is actually the database user name,
then you'd have a stable statement if you used the context variable
CURRENT_USER (or the predefined literal USER, if you are in dialect 1).

>I have not upgraded databases, currently using FB 1.03 but am gearing up to
>move to 1.5.

Then you'll need to fix up your joins and correlated
subqueries. Currently, in Fb 1.0, your subquery is ambiguous and the
engine throws a warning code (which IBO ignores). Fb 1.5 doesn't tolerate
ambiguous queries at all (it will thow exceptions on your query), and also
disallows mixing qualified and unqualified column references, even if the
unqualified column appears in only one of the tables.

I set out to give you the legal syntax for Fb 1.5 but realised I can't tell
which table some of those unqualified columns belong to, either. Anyway,
the clean layout should help to show you which columns you need to attend to.

INSERT INTO PI_INQ (
USERID,
COMPANY,
DIVISION, INVLOC,
PRODID,
WRAPCODE,
SALESID,
YARDLOC,
INBORDERNO,
INBSUFFIX,
MILLNO,
SPIECES,
SFOOTAGE,
TOTCOST,
WOODCOST,
FRTCOST,
PROCESSCOST, INTEREST
)
SELECT
CURRENT_USER,
s.COMPANY,
s.DIVISION,
s.INVLOC,
S.PRODID,
S.WRAPCODE,
S.SALESID,
S.YARDLOC,
S.INBORDERNO,
S.INBSUFFIX,
0,
S.PIECES,
S.FOOTAGE,
( S.WOODCOST+S.FRTCOST+S.PROCESSCOST+S.INTEREST ),
/**/ WOODCOST,
/**/ FRTCOST,
/**/ PROCESSCOST,
/**/ INTEREST
FROM PI_SUM S
LEFT OUTER JOIN PROD_XREF X
ON S.PRODID = X.PRODID
WHERE
S.COMPANY = :COMPANY
AND S.DIVISION = :DIVISION
AND S.INVLOC = :INVLOC

Fb 2 gets even tighter. It won't tolerate mixing table qualifiers and
alias qualifiers in the same statement, either.

Helen