one of my field having unwanted characters ,below is the scenario
i need to remove the 3 unwanted charcters ,so i have applied
SEL SUBST(COL1 ,4) FROM DBC.TABLE;
but now i want to use Export fixed width data using BTEQ on top of that table (above table TABLE)
My ask oer here is in order export the fixed data file we ned to us the below commands
SEL CAST(COL1 AS <DATATYPE>) ||','||
CAST(COL2 AS <DATATYPE>) ||','||
. FROM DBC.TABLE;
Since i already have unwanted characters from the above COL1 (xyz,ABX,DDG) ,how can i place a logic to remove those unwanted characters from COL1
can we use CAST on top of SUBSTR?
SEL (CAST(COL1(SUBST(COL1 ,4) AS <DATA TYPE>)) ||','||
--SUBSTR(ORG ,4) CAST(ORG AS VARCHAR(35)) ||','||
CAST(SUBSTR(ORG ,4) AS VARCHAR(35)) ||','||
CAST(PARTY_ID AS INTEGER) ||','||
CAST(PARTY_FST_NM AS VARCHAR(30)) ||','||
CAST(PARTY_LST_NM AS VARCHAR(30)) ||','||
CAST(PARTY_LOC AS VARCHAR(30)) ||','||
CAST(PARTY_INCOME AS INTEGER) ||','||
CAST(JDATE AS DATE)
Kindly excuse if iam confusing .........
You can check your output and see if this is what you want.
To avoid confusion ,maybe you can show what you have as input and what you want as output. This is just one way I feel, to interpret better. There maybe other better ways, who knows :).
Raj below is the i/p and o/p just now i have ran in BTEQ.
your session is set to ANSI mode and this results in casting numeric values right-aligned with leading zeroes.
You might switch to BTET mode or simply apply a TRIM, e.g.
TRIM(SUBSTR(ORG ,4)) ||','||
A CAST is only needed for TIME and TIMESTAMP, which will error out when you try to TRIM it.