Products | Versions |
---|---|
TIBCO Data Virtualization | All supported versions. |
When the user performs any function on CHAR-type data, null or empty values are getting padded in the results.
And this happens only for CHAR-type data and not for VARCHAR.
For e.g., while using the LEFT (5) function on "TIBCO DATA VIRTUALIZATION" and the datatype of column is CHAR(11) ,
then we get result as -> "TIBCO <then 6 spaces>".
But this is seen only if the results are saved as a CSV file or published the view as either a database or a web service and access it via a browser or a JDBC/ODBC client tool and don't see it when we just view results in the studio.
Note: Open the CSV file using Notepad or any other text editor.
For example, if we create and publish a view (vTest) as below:
-------------------------
SELECT
vDmy.WORKNO,
LEFT(vDmy.WORKNO, 4) L1, --CHAR(11)
LEFT('01100090004', 4) L2, --CHAR(11)
LEFT(vDmy.WORKNO || '', 4) L3, --VARCHAR
LEFT('01100090004' || '', 4) L4, --VARCHAR
LEFT(vDmy.WORKNO || 'A', 4) L5, --CHAR(12)
LEFT('AB' || vDmy.WORKNO, 4) L6, --CHAR(13)
LEFT(CAST(CAST(vDmy.WORKNO AS DECIMAL(11,0)) AS CHAR), 4) L7, --CHAR(255)
LEFT(CAST(CAST(vDmy.WORKNO AS DECIMAL(11,0)) AS VARCHAR), 4) L8, --VARCHAR
LEFT(TO_CHAR(CAST(vDmy.WORKNO AS DECIMAL(11,0))), 4) L9, --CHAR(255)
RIGHT(vDmy.WORKNO, 4) R1 --CHAR(11) same as LEFT()
FROM
/shared/ntt_padding/T2199/vDmy vDmy
-------------------------
and these are the results seen after accessing this published view via HTTP or HTTPS JSON endpoint URLs.
-------------------------
{
"test.vTestResponse": {
"test.vTestOutput": {
"test.row": [
{
"test.WORKNO": "01100090004",
"test.L1": "0110 ",
"test.L2": "0110 ",
"test.L3": "0110",
"test.L4": "0110",
"test.L5": "0110 ",
"test.L6": "AB01 ",
"test.L7": "1100 ",
"test.L8": "1100",
"test.L9": "1100 ",
"test.R1": "0004 "
}
]
}
}
}
-------------------------
Spaces are padded only for CHAR-type data and not VARCHAR.