I need to get data if it exists else get another value
In this example, I want to get data from tdpsc006 table
session form week.f = 1
declaration:
week1 = week.f
week2 = week1 +1
week3 = week2 + 1
week4 = week3 +1
week5 = week4 + 1
week6 = week5 +1


Select tdpsc006.*
from tdpcs006
where ..............


if tdpsc006.week = week5
then qnty5 = tdpsc006.totq
else qnty5 = qnty4
endif

data in table tdpsc006 is as follows

week 1 = 100 pcs
week 2 = 120 pcs
week 3 = 300 pcs
week 4 = 320 pcs
week 6 = 400 pcs

The if statment will be false because tdpsc006.week is equal to many different weeks. If I do not include the else statment, the script works if the data is actually equal. In this example, I want to get the data from week 4 because tdpsc006.week <> week5.



:mad:

try something like this:

(Sorry I don't have the definition for tdpsc006 - I'm assuming it's keyed by item and week



select tdpsc006.*
from tdpsc006
where tdpsc006._index1 inrange {:something.f}
and {:something.t}
order by tdpsc006.week asc |* ascending
selectdo
next.week = tdpsc006.week + 1
select tdpsc006_1.*
from tdpsc006 tdpsc006_1
where tdpsc006_1._index1 = {:tdpsc006.item}
and tdpsc006_1.week = :next.week
selectdo
|* use value from next week
selectempty
|* use value from previous week
endselect

endselect




Regards,
Rupert