using case exists

[jgentes]

i am trying to do a query that will select the info from a table where the id match's and also one of the fields in that record is not set, and if it is set, use that id and repeat the process, until that field is not set, thus arriving at the latest / most current product available.

SELECT id, number, description, replaced_by
        FROM parts 
        WHERE number = '19'
              AND CASE EXISTS(replaced_by = NULL) 
              WHEN FALSE THEN ( SELECT id, number, description, replaced_by ) 
              FROM parts WHERE number = replaced_by
 

[pbmods]

Heya, JGentes.

Try using IF instead. CASE is only available in a stored procedure.

[jgentes]

so i would try some like

SELECT id, number FROM parts
   WHERE number = '19' AND
   IF replaced_by = NULL
   ELSE SELECT id, number FROM parts WHERE number = replaced_by 
 

and if that is the right query, that doesn't look like it would check the second part for the replaced_by field to be blank on that one as well. it should be blank because of other queries that update the appropriate fields, but this is sort of a contingency plan