how get the value

[rainylsh]

eg. table_1 field_A field_B field_C
01 A 2
02 B 3
03 A 4
table_2 field_B field_D
A 1
B 2
how can i get the first value from table_1 that field_C <= 3 and field_D eq 1

 

[StuartT]

eg. table_1 field_A field_B field_C
01 A 2
02 B 3
03 A 4
table_2 field_B field_D
A 1
B 2
how can i get the first value from table_1 that field_C <= 3 and field_D eq 1

You do not say if these tables are related, so I will assume not.
Find first table_1 where
table_1.field_C <= 3 no-error.
find first table_2 where
table_2.field_D = 1 no-error.

If the tables are related by field_B then you can do the following:

for each table_2 where
table_2.field_D = 1,
first table_1 where
table_1.field_B = table_2.field_B and
table_1.field_C <= 3:
<do what you want with table_1>
end.

 

[mirekpa]

Hi,

In yours example, if relation key is field_B,
neither of record table_1 (where field_C <= 3),
could not find a record in table_2 (where field_D = 1)
find returns null

Mirek

 

[StuartT]

Hi,

In yours example, if relation key is field_B,
neither of record table_1 (where field_C <= 3),
could not find a record in table_2 (where field_D = 1)
find returns null

Mirek

Don't talk nonsense I use the following example
Table_1
Field_A = 01
Field_B = A
Field_C = 2
Table_2
Field_B = A
Field_D = 1

Quite clearly these two tables are related and satisfy the conditions required:awink:

 

[mirekpa]

you right

DEF TEMP-TABLE table_1 
    FIELD field_A AS CHAR 
    FIELD field_B AS CHAR 
    FIELD field_C AS INT.

DEF TEMP-TABLE table_2 
    FIELD field_B AS CHAR 
    FIELD field_D AS INT.

CREATE table_1. table_1.field_A = "01". table_1.field_B = "A". table_1.field_C = 2.
CREATE table_1. table_1.field_A = "02". table_1.field_B = "B". table_1.field_C = 3.
CREATE table_1. table_1.field_A = "03". table_1.field_B = "A". table_1.field_C = 4.

CREATE table_2. table_2.field_B = "A". table_2.field_D = 1.
CREATE table_2. table_2.field_B = "B". table_2.field_D = 2.

FOR EACH TABLE_1 WHERE 
    TABLE_1.field_C <= 3 NO-LOCK,
    FIRST TABLE_2 WHERE 
        TABLE_2.field_B = table_1.field_B AND
        table_2.field_D = 1
        NO-LOCK:
    LEAVE.
END.

/**/
MESSAGE table_1.field_A
    VIEW-AS ALERT-BOX INFO BUTTONS OK.