CAST and SUBSTR can be applied on the same column at a time in the same query

Database

CAST and SUBSTR can be applied on the same column at a time in the same query

Hi All,

one of my field having unwanted characters ,below is the scenario

COL1

xyzCRICKET

ABXFOOTBALL

DDGTENNIS

i need to remove the 3 unwanted charcters ,so i have applied 

SEL SUBST(COL1 ,4) FROM DBC.TABLE;

issue resolved

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?

like

SEL (CAST(COL1(SUBST(COL1 ,4) AS <DATA TYPE>)) ||','||

?

MY Query

SELECT CAST(ORG 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) 

FROM USER_DBC.PARTY;

the column having extra characters is "ORG"

I have experimented with the below logic  it's working is this is the right way ,correct me if iam wrong

SELECT 
--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)
FROM USER_DBC.PARTY;

Kindly excuse if iam confusing .........

4 REPLIES
N/A

Re: CAST and SUBSTR can be applied on the same column at a time in the same query

Rakesh,

Yes you can and this is an acceptable way to do it.

Rglass

Re: CAST and SUBSTR can be applied on the same column at a time in the same query

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 :).

Re: CAST and SUBSTR can be applied on the same column at a time in the same query

Thanks Glass.

Raj below is the i/p and o/p just now i have ran in BTEQ.

Input

.OS del EXPORT_DATA_PARTY_REPORT.txt

.SET SESSION TRANSACTION ANSI

.SET FORMAT ON

.SET INDICDATA OFF

.LOGON 127.0.0.1/dbc,dbc

 DATABASE USER_DBC;

.SET FORMAT OFF

.EXPORT REPORT FILE = EXPORT_PARTY_REPORT_FIXED_WIDTH.txt

.SET WIDTH 800

SELECT 

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) 

FROM USER_DBC.PARTY

ORDER BY PARTY_ID;

.EXPORT RESET

.LOGOFF

.QUIT

DATA(O/P)

((((((((((((Substr(ORG,4)||',')||PARTY_ID)||',')||PARTY_FST_

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Deloitte,          1,Rakesh Reddy,Gopidi,Hyderabad,     850000,12/11/26

Oracle,          2,Sneha ,Srivastav,Pune,     750000,10/12/16

Microsoft,          3,Nishitha Reddy,Velma,Banglore,     950000,09/06/18

IBM,          4,Sruthi Reddy,Kanumula,Chennai,     670000,13/01/09

Accenture,          5,Anushri,Dixit,Mumbai,     550000,14/01/26

Capgemin,          6,Rakul Singh,Preeth,Delhi,     780000,10/10/27

Dell,          7,Suguna Reddy,Duvulla,Assam,     380000,08/08/14

CSC,          8,Snigdha Reddy,Gaddam,Gujarath,     650000,11/01/06

TCS,          9,Hyma Reddy,Pilla,Kolkatta,     876000,14/07/17

UHG,         10,Divya Chowdary,Manne,Orrissa,     732000,07/12/16



N/A

Re: CAST and SUBSTR can be applied on the same column at a time in the same query

Hi Rakesh,

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.

 SELECT 
TRIM(SUBSTR(ORG ,4)) ||','||
TRIM(PARTY_ID) ||','||
TRIM(PARTY_FST_NM)

A CAST is only needed for TIME and TIMESTAMP, which will error out when you try to TRIM it.