Updating a file based on the value of a field in another file

[LucyB]

I'm trying to use SQL to populate a cycle count frequency field in file ItemMaster based on the dollar value contained in another file, SRPFX. So far, no cigar.

UPDATE ItemMaster a
SET a.icyc =
CASE
WHEN b.value < 500.00000000 THEN 1
WHEN b.value >= 500.00000000 and b.value < 1000.00000000 THEN 2
WHEN b.value >= 1000.00000000 and b.value < 3000.00000000 THEN 4
WHEN b.value >= 3000.00000000 THEN 12
END
WHERE a.iwhs = 'SR' and
EXISTS(select * from srpfx b where b.ITEM = a.ITEM) and
b.BOH > 0

The above statement fails to run because the global variable "value" is not defined. I thought that the presence of file SRPFX in the WHERE clause would make its fields available throughout the sql statement but it looks as if I'm wrong. Is there a way I can make this update work?

The error text is: Position 1 Column or global variable VALUE not found.
TIA

[jj_dahlheimer]

UPDATE ItemMaster a
SET a.icyc =
(select CASE
WHEN b.value < 500.00000000 THEN 1
WHEN b.value >= 500.00000000 and b.value < 1000.00000000 THEN 2
WHEN b.value >= 1000.00000000 and b.value < 3000.00000000 THEN 4
WHEN b.value >= 3000.00000000 THEN 12
END from srpfx b where b.ITEM = a.ITEM)
WHERE a.iwhs = 'SR' and
EXISTS(select * from srpfx b where b.ITEM = a.ITEM) and
b.BOH > 0

[jj_dahlheimer]

Ignore previous, you cannot access fields in a subquery, you need to code the subquery in both places.

UPDATE ItemMaster a
SET a.icyc =
(select CASE
WHEN b.value < 500.00000000 THEN 1
WHEN b.value >= 500.00000000 and b.value < 1000.00000000 THEN 2
WHEN b.value >= 1000.00000000 and b.value < 3000.00000000 THEN 4
WHEN b.value >= 3000.00000000 THEN 12
END from srpfx b where b.ITEM = a.ITEM and
b.BOH > 0)
WHERE a.iwhs = 'SR' and
EXISTS(select * from srpfx b where b.ITEM = a.ITEM and
b.BOH > 0)

[LucyB]

Yes! Thank you, jj_dahlheimer. The second example worked exactly as I needed it to. I appreciate your help.