select
M.staff_number,
CASE
WHEN IM.code is not null THEN im.code
WHEN IM.code is null THEN qr_code.next_val
END QR_CODE
FROM master M
LEFT JOIN issue_numbers IM
ON (M.staff_number = IM.primary_key AND IM.domain = 'QR_CODE')
WHERE
M.staff_number = :staff_number
However I found that the sequence was incrementing the numbers faster than expected and leaving gaps of numbers that never got used against a record, this even happened when the report ran the dataset for staff who had a record in the IM table (and as such shouldn't have got past the first WHEN row), I couldn't work out why until it became apparent that the SQL is evaluated in its entirety and as such running the sequence regardless of where the case statement resolved for that record.
After thinking about it the issue was resolved by embedding the sequence within a function that has a parameter passed to it, so that the function only ran the sequence when it received a certain value (which was keyed to an embedded case statement that passed the key value only when IM.code is null).
No comments:
Post a Comment