At first, I was trying to make the stored procedure work..
then , I copied the SQL and just tried to make a statement.. then I got the error..
"no current record for fetch operation", which gave me a clue, that there was something wrong with my left join..

So, I moved some stuff around, and got it to work..


/// THIS DID NOT WORK
CREATE PROCEDURE INVENTORY_PROC
returns (
    product varchar(40),
    si_rec_id integer)
as
begin
   For select P.product,SI.Rec_ID
   FROM stores S,Products P
   LEFT OUTER JOIN Stores_Inventory SI ON (SI.Products_Rec_ID=P.rec_id
        and S.Rec_ID=SI.stores_rec_id
        And SI.Inv_Date = '04/11/2014'
        )
   Where S.REC_ID=:VStoreID
   and P.upc_code = '17072-22000'
   Into  :product,  :si_rec_id
   do
   Begin
      suspend;
   End
end




/// THIS DID WORK.. Notice the VStore ID, in my left join too..
CREATE PROCEDURE INVENTORY_PROC
returns (
    product varchar(40),
    si_rec_id integer)
as
begin
   For select P.product,SI.Rec_ID
   FROM stores S,Products P
   LEFT OUTER JOIN Stores_Inventory SI ON (SI.Products_Rec_ID=P.rec_id
        and :VStoreID=SI.stores_rec_id              ****///Changed this line!
        And SI.Inv_Date = '04/11/2014'
        )
   Where S.REC_ID=:VStoreID
   and P.upc_code = '17072-22000'
   Into  :product,  :si_rec_id
   do
   Begin
      suspend;
   End
end



Got this to work, but still not sure why.. Just glad to have gotten it to work..


Gordon