I think something is wrong.

If have tried a TIB_DSQL and a TIB_Query, with the same results

This query executes fine on either component:

insert into Session
(SessionID, SessionBatchID, ID)
select 0,
37,
ShopProducts.ShopProductsID
from ShopProducts
left join Session
on Session.SessionBatchID = 37 and
Session.ID = ShopProducts.ShopProductsID
where ShopProducts.ShopGroupLookupID = 123 and
Session.SessionID is null;

However, this query will not execute on either:

insert into Session
(SessionID, SessionBatchID, ID)
select :SessionID,
:SessionBatchID1,
ShopProducts.ShopProductsID
from ShopProducts
left join Session
on Session.SessionBatchID = :SessionBatchID2 and
Session.ID = ShopProducts.ShopProductsID
where ShopProducts.ShopGroupLookupID = :ShopGroupLookupID and
Session.SessionID is null;

I am executing the query like this:

with qryInsertShopGroupToSession do
begin
parambyname('SessionID').value := 0; //trigger on table increments
generator
parambyname('SessionBatchID1').value := SessionBatchID;
parambyname('SessionBatchID2').value := SessionBatchID;
parambyname('ShopGroupLookupID').value := ShopGroupLookupID;
execsql;
end;

It inserts no records presumably because the parameters are not being set
properly

If I wrap the sql into a stored procedure, it works fine.

I think it could be an IBO thing - are there properties I need to set to
handle all the different types of parameters I am using here (ie. parameter
in the select, left join, and where clause?). I only renamed the
SessionBatchID1 and 2 parameters as such to avoid confusing IBO, but I have
tried using the same name in both places in the query as well.

Any comments appreciated

Thanks


Josh

ps. The purpose here is to only insert ShopProductIDs into the Session
table that haven't already been inserted into the current SessionBatch.