[SOLVED] if else statements for finding values from two different table

if else statements for finding values from two different table

I want to match a number from two different table and if it's found in any table it will display that..

INPUT FROM VALUE("/mydirectory/test.csv").
REPEAT:

    IMPORT c.
    FOR EACH table1 NO-LOCK WHERE table1.ordernumber=c.

        IF AVAILABLE table1 THEN
            DISPLAY table1.ordernumber table1.orderdate.
        ELSE IF NOT AVAILABLE table1 THEN
            FIND FIRST table2 NO-LOCK WHERE table2.ordernumber=c.
        IF AVAILABLE table2 THEN
            DISPLAY table2.ordernumber table2.orderdate.

    END.

END.

So far I have written this much but it's not giving correct results. Appreciate help on this regards. Thanks.

Solution

Using IF AVAILABLE table1 within the FOR EACH makes no sense, as the FOR EACH only iterates matching records in table1.

Try this:

INPUT FROM VALUE("/mydirectory/test.csv").

REPEAT:
    IMPORT c.

    FIND table1 WHERE table1.ordernumber=c NO-LOCK NO-ERROR.

    IF AVAILABLE table1 THEN
        DISPLAY table1.ordernumber table1.orderdate.
    ELSE DO:
        /*IF NOT AVAILABLE table1 THEN*/
        FIND FIRST table2 WHERE table2.ordernumber=c NO-LOCK NO-ERROR.

        IF AVAILABLE table2 THEN
            DISPLAY table2.ordernumber table2.orderdate.
    END.
END.

Without knowing your tables/indexes I can only guess if you need to do unique finds (current code) or add the FIRST option to your FIND statements.

If you want the display of table2.ordernumber and table2.orderdate in place of the fields from table1, you can change the second DISPLAY statement like this - using the place holder syntax:

DISPLAY table2.ordernumber @ table1.ordernumber 
        table2.orderdate @ table2.orderdate.