At 04:47 PM 5/05/2005 -0500, you wrote:

><<CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
>||'-0'|| A.ADMIT_ORDINAL AS VARCHAR(16)),>>
>
>OK, that worked great. Now I need to know how I can append 2 zeros if a
>value is 1..9, but only one zero if it is 10..99. I have:
>
>CAST(''0'' || substring(A.UNIT_ID from 1 for 1)||''-''|| A.ADMISSION_ID
>||''-0''|| A.ADMIT_ORDINAL AS VARCHAR(16)), ')

This won't work. Mistyping?

>But need the last '0' to be two '00s if A.ADMIT_ORDINAL is < 10.

Try this:

CASE
WHEN A.ADMIT_ORDINAL < 10 THEN
CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
||'-00'|| A.ADMIT_ORDINAL AS VARCHAR(16))
WHEN A.ADMIT_ORDINAL > 99 THEN
CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
||'-'|| A.ADMIT_ORDINAL AS VARCHAR(16))
ELSE
CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
||'-0'|| A.ADMIT_ORDINAL AS VARCHAR(16))
END AS BLAH,

./hb