"Max Coppin" <maxcoppin@humphreyfarms.com> wrote in
news:bgdq0s$s56$1@titan.btinternet.com:

> I have a hopefully simple question:-
>
> I'm trying to adapt some code written by someone else to set a flag
> based on a record existing or not. The code is below. Basically I
> want to set a flag based on whether or not a record exists for a
> specific record in another table. E.G if there is an entry in the
> orders table for a specific customer then set the flag to YES else NO.
> Please help! I've included the code below if that helps. I want to
> set a flag to YES if there is an entry in the order_line_additive
> table that coresponds to a line in the order_line table.
>
>
> Max
>
>
>
> FOR EACH ORDER_LINE NO-LOCK
> WHERE ORDER_LINE.REQUIRED_ARRIVAL_FROM_DATE >=
> DATE(fiStartDate:SCREEN-VALUE) AND
> ORDER_LINE.REQUIRED_ARRIVAL_FROM_DATE <=
> DATE(fiEndDate:SCREEN-VALUE) AND (ORDER_LINE.STATUS_NUMBER
> = 42 OR ORDER_LINE.STATUS_NUMBER = 44),
> /* FIND FIRST db.ORDER_LINE_ADDITIVE
> WHERE order_line.order_line_number =
> order_line_additive.order_line_number NO-LOCK NO-ERROR.
> /*IF AVAILABLE (order_line_additive) THEN chrAdditive = "Y".
> ELSE chrAdditive = "N".*/*/
> FIRST ORDER NO-LOCK
> WHERE ORDER.ORDER_NUMBER = ORDER_LINE.ORDER_NUMBER
> AND ORDER.SALE_OR_PURCHASE = "SAL"
> AND (ORDER.STATUS_NUMBER = 72 OR ORDER.STATUS_NUMBER =
> 74),
> FIRST PACKAGED_ITEM NO-LOCK
> WHERE PACKAGED_ITEM.PACKAGED_ITEM_NUMBER =
> ORDER_LINE.PACKAGED_ITEM_NUMBER AND
> SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "B"
> AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <>
> "b" AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1)
> <> "S" AND
> SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "s",
> FIRST ITEM NO-LOCK
> WHERE ITEM.ITEM_NUMBER = PACKAGED_ITEM.ITEM_NUMBER:
>
> EXPORT STREAM rm_usage DELIMITER ","
> STRING(ORDER_LINE.REQUIRED_ARRIVAL_FROM_DATE,"99/99/9999")
> ORDER_LINE.ORDERED_QUANTITY_1
> PACKAGED_ITEM.PACKAGED_ITEM_CODE
> PACKAGED_ITEM.PACKAGED_ITEM_DESC.
> END.


Find is not allowed in a for each mode read. Try this (not tested):

FOR EACH ORDER_LINE NO-LOCK
WHERE ORDER_LINE.REQUIRED_ARRIVAL_FROM_DATE >= DATE
(fiStartDate:SCREEN-VALUE)
AND ORDER_LINE.REQUIRED_ARRIVAL_FROM_DATE <= DATE
(fiEndDate:SCREEN-VALUE)
AND (ORDER_LINE.STATUS_NUMBER = 42 OR
ORDER_LINE.STATUS_NUMBER = 44),
FIRST db.ORDER_LINE_ADDITIVE
WHERE order_line.order_line_number =
order_line_additive.order_line_number NO-LOCK,
FIRST ORDER NO-LOCK
WHERE ORDER.ORDER_NUMBER = ORDER_LINE.ORDER_NUMBER
AND ORDER.SALE_OR_PURCHASE = "SAL"
AND (ORDER.STATUS_NUMBER = 72 OR ORDER.STATUS_NUMBER = 74),
FIRST PACKAGED_ITEM NO-LOCK
WHERE PACKAGED_ITEM.PACKAGED_ITEM_NUMBER =
ORDER_LINE.PACKAGED_ITEM_NUMBER
AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "B"
AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "b"
AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "S"
AND SUBSTRING(PACKAGED_ITEM.PACKAGED_ITEM_CODE,1,1) <> "s",
FIRST ITEM NO-LOCK
WHERE ITEM.ITEM_NUMBER = PACKAGED_ITEM.ITEM_NUMBER:
IF AVAILABLE (order_line_additive) THEN chrAdditive = "Y".
ELSE chrAdditive = "N".

Export etc..

Kevin