This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Wednesday, 31 October 2018

Oracle - Caution when using sequences within case statements

Some logic within an SSRS report that I was working on had a case statement that either pulled a QR code from an existing field or ran a sequence to allocate a new QR code, this then got called in a separate dataset in the report that carried out in insert of the new QR code (by sharing it through an internal parameter).  An example of this code is below;

WHEN IM.code is not null THEN im.code
WHEN IM.code is null THEN qr_code.next_val

FROM master M
LEFT JOIN issue_numbers IM
ON (M.staff_number = IM.primary_key AND IM.domain = 'QR_CODE')
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).