Good day
I have a numerical column that can only be interpreted by our front end if there are 2 numerical digits, as such, 1 is captured as 01, 2 is captured as 02 etc.
In order to format data I am importing, I am using to_char(FIELD,'00') when extracting from staging tables(The staging tables are external tables - not sure if that info will be useful)
This function usually works and I have it setup on extracts where I need to pad numerical columns with zeroes i.e to_char(FIELD,'00000000000.00') - This would output 1234.5 as 00000001234.50
For some frustrating reason that evades me, this conversion is not working in my query when I have 3 of the conversions occurring. The number remains 1 instead of being converted to 01. The format of the fields being converted is NUMBER(2)
Query Sample - Please refer to FIeld 1 and 2, these fields are not converting:
/* Formatted on 2017/02/10 02:42:21 PM (QP5 v5.149.1003.31008) */
SELECT t.HIDDEN
|| ','
|| t.HIDDEN
|| ','
|| TO_CHAR (td.HIDDEN, 'YYYYMMDD')
|| ','
|| ''
|| ','
|| td.HIDDEN
|| ','
|| td.HIDDEN
|| ','
|| TO_CHAR (td.HIDDEN, 'YYYYMMDD')
|| ','
|| DECODE (td.HIDDEN, 'Monthly', 4, 'BiAnnual', 6)
|| ','
|| t.HIDDEN
|| ','
|| '7'
|| ','
|| DECODE (td.HIDDEN,
'Percentage', td.HIDDEN,
'0')
|| ','
|| DECODE (td.HIDDEN,
'Amount', td.HIDDEN,
'0')
|| ','
|| CASE
WHEN td.FIELD2 <=
(SELECT TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) FROM DUAL)
THEN
REPLACE (
('2018' || TO_CHAR (td.FIELD2, '00')
|| (DECODE (td.FIELD1,
0, 31,
TO_CHAR (td.FIELD1, '00')))),
' ',
'')
WHEN td.FIELD2 >
(SELECT TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) FROM DUAL)
THEN
REPLACE (
('2017' || TO_CHAR (td.FIELD2, '00')
|| (DECODE (td.FIELD1,
0, 31,
TO_CHAR (td.FIELD1, '00')))),
' ',
'')
END
|| ','
|| ''
|| ','
|| TO_CHAR (td.HIDDEN, 'YYYYMMDD')
|| ','
|| '0'
|| ','
|| ''
|| ','
|| (SELECT TO_CHAR (CURRENT_DATE, 'YYYYMMDD')
FROM HIDDEN
WHERE HIDDEN = 1)
|| ','
|| ''
|| ','
|| 'HIDDEN'
|| ','
|| ''
|| ','
|| '1'
|| ','
|| REPLACE (
(DECODE (td.FIELD1,
0, 31,
TO_CHAR (td.FIELD1, '00'))),
' ',
'')
FROM t
JOIN
td
ON ( td.D = t.D
AND TD.A = t.A
AND td.B = t.B
AND td.C = t.C
AND td.E = t.E
AND td.F = t.F
AND td.G = t.G);
If a run a to_char conversion on the fields in isolation, the conversion is working fine
Thank you in advance for any assistance you may offer!