--- In IBObjects@y..., Daniel Rail <drail@n...> wrote:

> No. Use ITEMSUSED.ITEMNO instead. I'm not sure about CAT1NUM

though, but

> don't use it like a parameter. If for CAT1NUM doesn't work, maybe

use a

> select with a join and use ITEMNO in the where clause.

Thanks Daniel,

I first tried:

SELECT ITEMUSED_ID
, JOBNO
, ITEMNO
, QTY
, (SELECT CAT1NO FROM ITEMS WHERE ITEMS.ITEMNO=ITEMSUSED.ITEMNO)
AS CAT1NUM
, CAT1
FROM ITEMSUSED
JOIN CATEGORY1 C1 ON C1.CAT1NO=:CAT1NUM

which prepares OK & gives the right values for CAT1NUM, but not for
CAT1.

Then I tried:
SELECT ITEMUSED_ID
, JOBNO
, ITEMNO
, QTY
, I.CAT1NO
, C1.CAT1
FROM ITEMSUSED IU
JOIN ITEMS I ON I.ITEMNO=IU.ITEMNO
JOIN CATEGORY1 C1 ON C1.CAT1NO=I.CAT1NO

which works fine! I just thought there was some reason (speed?)to
prefer sub-selects where possible. I guess there's no way to have the
server use one of the values it is currently retrieving as part of
its select statement.

I wonder why the :CAT1NUM syntax is accepted OK? Couldn't find any
reference to this in the docs, but I'm probably not asking the right
questions in the search.

Thanks again,
Steve