I have an integer field, which is an auto-value primary key.
How do I convert this to a fixed-length 4-digit field with leading zeros for use in queries/reports.
I have tried formatting the field as having 4 leading zeros so the field appears as I want it when I look at the table.
However, queries and views that use this field don’t inherit this format and it reverts to variable-length integer format without the leading zeros.
As format is not inherited use something like the following in your queries:
SELECT RIGHT( '0000' || id, 4) AS idzero
I was over-complicating it, using various functions inclouding CONVERT, LENGTH etc. Your solution is delightfully simple - thanks!
Bill