Because thats the result I get.
I'll send the complete procedure but it is verry long.
All values that I set manualy to 0 have this problem sometimes.
The biggest problem I have is when select/insert/update the table
RESM_STOCK_ADAPTION_DETAILS

////////////////////////////////////////////////
CREATE PROCEDURE PROC_RESM_CRE_DROP (
ACTIONIDX INTEGER,
USEMAXQTY SMALLINT,
MAXQTY INTEGER,
CHECKWITHSTOCK SMALLINT,
DROPDATE DATE)
RETURNS (
DROPQUANTITY INTEGER)
AS
declare variable OrderIdx INTEGER;
declare variable OrderLine SMALLINT;
declare variable DropIdx INTEGER;
declare variable InvoiceIdx INTEGER;
declare variable ItemDescription VARCHAR(100);
declare variable Quantity INTEGER;
declare variable UnitPrice NUMERIC(6,2);
declare variable Discount SMALLINT;
declare variable Vat SMALLINT;
declare variable InvoiceNbr VARCHAR(8);
declare variable Dummy VARCHAR(150);
declare variable Language VARCHAR(3);
declare variable PaymentReference VARCHAR(15);
declare variable CurrentYear SMALLINT;
declare variable AdaptionIdx integer; /* The stock adaptionidx */
declare variable AdaptionLine SMALLINT; /* The current Adaption Line */
declare variable MaxAdaptionLine SMALLINT; /* The last Adaption Line for
this Drop */
declare variable ItemIdx integer; /* The itemidx used for the stock
adaptionidx */
declare variable CustomerIdx INTEGER; /* This variable is used as link in
the table RESM_STOCK_ADAPTIONS */
declare variable StockQty INTEGER; /* This variable is used for
1: To check if there are any items in stock
2: To check if the current item is still in
stock*/
declare variable StockAdaptionQty INTEGER; /* Used for the stock of the
current item */
declare variable AnoughInStock SMALLINT;

declare variable CommCustomerIdx INTEGER;
BEGIN
/*Check if there are any orders to process */
SELECT COUNT(*) FROM RESM_ORDERS WHERE OrderStatus = 0 AND ActionIdx =
:ActionIdx INTO :DropQuantity;
if ((DropQuantity = 0) OR (DropQuantity IS NULL)) then
BEGIN
SUSPEND;
EXIT;
END
/* Check if there are any items if in stock if this options is
selected, if not we don't even create a drop*/
SELECT SUM(Stock) FROM RESM_STOCK WHERE ItemIdx IN (SELECT ItemIdx FROM
RESM_ACTION_ITEMS WHERE ActionIdx = :ActionIdx) INTO :StockQty;
if (StockQty < 1) then
begin
DropQuantity = -1;
SUSPEND;
EXIT;
end
/* Create a Drop */
DropIdx = GEN_ID(GEN_RESM_DROPS_IDX,1);
INSERT INTO RESM_DROPS (DropIdx,ActionIdx,DropDate) VALUES
(:DropIdx,:ActionIdx,:DropDate);
DropQuantity = 0;
/* Get The customerIdx */
SELECT CustomerIdx FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO
:CustomerIdx;
/* Create a stock adaption line */
AdaptionIdx = GEN_ID(GEN_RESM_STOCK_ADAPTION_IDX,1);
INSERT INTO RESM_STOCK_ADAPTIONS
(AdaptionIdx,CustomerIdx,AdaptionTypeIdx,ActionIdx)
VALUES(:AdaptionIdx,:CustomerIdx,'1',:ActionIdx);
MaxAdaptionLine = 0;
SELECT CustomerIdx FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO
:CommCustomerIdx;
/* Process the orders */
FOR SELECT OrderIdx,CustomerIdx FROM RESM_ORDERS WHERE OrderStatus = 0
AND ActionIdx = :ActionIdx ORDER BY CreateDate,OrderIdx INTO
:OrderIdx,:CustomerIdx
DO
BEGIN
if (dropQuantity >= MaxQty and USEMAXQTY = 1) then
begin
/* Update the stock */
FOR SELECT ItemIdx,Quantity FROM RESM_STOCK_ADAPTION_DETAILS
WHERE AdaptionIdx = :AdaptionIdx INTO :ItemIdx,:Quantity
DO
BEGIN
UPDATE RESM_STOCK SET STOCK = STOCK - :Quantity WHERE
ItemIdx = :ItemIdx;
END
UPDATE RESM_DROPS SET Quantity = :DropQuantity WHERE DropIdx =
:DropIdx;
SUSPEND;
exit;
end
AnoughInStock = 1;
/* Check if there are anough items in stock */
Quantity = 0;
FOR SELECT RESM_ACTION_ITEMS.ItemIdx,RESM_ORDER_LINES.Quantity
FROM RESM_ORDER_LINES INNER JOIN RESM_ACTION_ITEMS ON
(RESM_ACTION_ITEMS.ActionItemIdx = RESM_ORDER_LINES.ItemIdx)
WHERE RESM_ORDER_LINES.OrderIdx = :OrderIdx
INTO :ItemIdx,:Quantity DO
BEGIN
StockQty = 0;
StockAdaptionQty = 0;
SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO
:StockQty;
SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO :StockAdaptionQty;
if((StockQty - StockAdaptionQty) < Quantity) then
AnoughInStock = 0;
END
if (AnoughInStock = 1) then
begin
SELECT LANGUAGE FROM
PROC_RESM_GET_CUST_DATA_BY_IDX(:CustomerIdx) INTO :Language;
/* Add A line to drop_lines */
INSERT INTO
RESM_DROP_LINES(DropIdx,OrderIdx)VALUES(:DropIdx,:OrderIdx);
/* Add an invoice */
InvoiceIdx = GEN_ID(GEN_RESM_INVOICE_IDX,1);
SELECT IdNumber FROM
PROC_RESM_GEN_ID_NUMBER(:CommCustomerIdx,0) INTO :InvoiceNbr;
CurrentYear = EXTRACT(YEAR FROM CURRENT_DATE);
/* SELECT ResmGenOrderNbr(:CurrentYear,:ActionIdx,:OrderIdx)
FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO :OrderNbr;*/
SELECT RESMGENORDERNBR(:CurrentYear,ActionIdx,:OrderIdx) FROM
RESM_ACTIONS WHERE ActionIdx = :ActionIdx INTO :PaymentReference;
INSERT INTO
RESM_INVOICES(InvoiceIdx,OrderIdx,InvoiceDate,Status,InvoiceNbr,Language,Cus
tomerIdx,PaymentReference) VALUES
(:InvoiceIdx,:OrderIdx,:DropDate,0,:InvoiceNbr,:Language,:CustomerIdx,:Payme
ntReference);
/* Add the invoice Lines */
FOR SELECT
RESM_ORDER_LINES.ORDERLINE,RESM_ORDER_LINES.DISCOUNT,RESM_ORDER_LINES.QUANTI
TY,RESM_ORDER_LINES.BASEPRICE,RESM_ITEMS.ITEMDESCRIPTION,RESM_ITEMS.VAT,RESM
_ITEMS.ItemIdx
FROM RESM_ORDER_LINES
INNER JOIN RESM_ACTION_ITEMS ON (RESM_ORDER_LINES.ITEMIDX =
RESM_ACTION_ITEMS.ACTIONITEMIDX)
INNER JOIN RESM_ITEMS ON (RESM_ACTION_ITEMS.ITEMIDX =
RESM_ITEMS.ITEMIDX)
WHERE OrderIdx = :OrderIdx ORDER BY OrderLine
INTO
:OrderLine,:Discount,:Quantity,:UnitPrice,:ItemDescription,:Vat,:ItemIdx
DO
BEGIN

/* Add an invoice line */
INSERT INTO
RESM_INVOICE_LINES(InvoiceIdx,InvoiceLine,Description,Quantity,UnitPrice,Dis
count,Vat)
VALUES
(:InvoiceIdx,:OrderLine,:ItemDescription,:Quantity,:UnitPrice,:Discount,:Vat
);
/* Update the stock adaption Line */
AdaptionLine = 0;
SELECT AdaptionLine FROM RESM_STOCK_ADAPTION_DETAILS WHERE
ItemIdx = :ItemIdx AND AdaptionIdx = :AdaptionIdx INTO :AdaptionLine;
if (AdaptionLine IS NULL or (AdaptionLine = 0)) then
BEGIN
MaxAdaptionLine = MaxAdaptionLine + 1;
INSERT INTO RESM_STOCK_ADAPTION_DETAILS
(AdaptionIdx,AdaptionLine,ItemIdx,Quantity)

VALUES(:AdaptionIdx,:MaxAdaptionLine,:ItemIdx,:Quantity);
END
else
begin
UPDATE RESM_STOCK_ADAPTION_DETAILS SET Quantity =
Quantity + :Quantity WHERE AdaptionIdx = :AdaptionIdx AND AdaptionLine =
:AdaptionLine;
end
END
DropQuantity = DropQuantity + 1;
/* Add the postalcosts line */
OrderLine = OrderLine + 1;
SELECT PostCosts FROM RESM_ACTIONS WHERE ActionIdx = :ActionIdx
INTO :UnitPrice;

INSERT INTO
RESM_INVOICE_LINES(InvoiceIdx,InvoiceLine,Description,Quantity,UnitPrice,Dis
count,Vat)
VALUES (:InvoiceIdx,:OrderLine,'Portkosten / Frais de
port',1,:UnitPrice,0,0);
/* Set the status of the current order to dropped */
UPDATE RESM_ORDERS SET OrderStatus = 1 WHERE OrderIdx =
:OrderIdx;
END
END
/* Update the stock */
FOR SELECT ItemIdx,Quantity FROM RESM_STOCK_ADAPTION_DETAILS WHERE
AdaptionIdx = :AdaptionIdx INTO :ItemIdx,:Quantity
DO
BEGIN
UPDATE RESM_STOCK SET STOCK = STOCK - :Quantity WHERE ItemIdx =
:ItemIdx;

END
UPDATE RESM_DROPS SET Quantity = :DropQuantity WHERE DropIdx =
:DropIdx;
SUSPEND;
END

//////////////////////////////////////////



regards

johan


""Martijn Tonies"" <m.tonies@...> schreef in bericht
news:00e201c3027c$515f82c0$0a02a8c0@seal...

> Hi Johan,
>
> > The problem is that when the sql statement (eg.)
> > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx INTO

:StockQty;

> > is executed in the procedure and there is no result for the current
> ItemIdx,
> > it containts the result of the previous select where ther was a record

for

> > Itemidx.
> > eg
> > Select ... ItemIdx = 5 -> result 10
> > Select ... itemidx = 1 -> (no record for it) return also 10
>
> I'm pretty sure it doesn't return "10" - what makes you think it does?
>
> --
> Martijn
>
>
> > I must say that I only have this problem when using stored procedures.

In

> > this case the procedure also manipulated a lot of tables.
> >
> > regards
> >
> > Johan
> >
> >
> > ""Martijn Tonies"" <m.tonies@...> schreef in bericht
> > news:009f01c30276$f4f0bcc0$0a02a8c0@seal...
> > > Hi Johan,
> > >
> > > > One of my stored procedures is manipulating a lot of tables and need
> in
> > a
> > > > loop the data that is writen to the db in this same loop.
> > > > The problem I have is that the variables aren't update always when I
> > need
> > > to
> > > > retrive a null from the database.
> > >
> > > Retrieve a NULL where?
> > >
> > > > The current sollution I have is to set the var to 0 and then it

works.

> > > Here
> > > > follows an example
> > > > StockQty = 0;
> > > > StockAdaptionQty = 0;
> > > > SELECT Stock FROM RESM_STOCK WHERE ItemIdx = :ItemIdx
> > INTO
> > > > :StockQty;
> > >
> > > You mean, if this SELECT doesn't return any rows? 'Cause then STOCKQTY
> > will
> > > stay empty (null).
> > >
> > > > SELECT Quantity FROM RESM_STOCK_ADAPTION_DETAILS

WHERE

> > > > ItemIdx = :ItemIdx AND AdaptionIdx =:AdaptionIdx INTO
> > > :StockAdaptionQty;
> > > >
> > > > so if i dont set StockQty and StockAdaptionQty to 0 then I don't get
> the
> > > > correct data.
> > > >
> > > > Mayby this is a bug in FB or is there any other explination for it.
> > > >
> > > >
> > > > Regards
> > > >
> > > >
> > > > Johan
> > > >
> > > > PS: if needed I can send the complete proc source
> > >
> > > Better explain what you mean :)
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>