SEO

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.

"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

try the "can-find" function

e.g. avail_flag = can-find( record where... )

check into it first in your editor help, good luck



"Max Coppin" <maxcoppin@humphreyfarms.com> wrote in message 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.
> --

Change it to:
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 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:

/* 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".*/*/


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.
"Max Coppin" <maxcoppin@humphreyfarms.com> wrote in message 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.