All Forums Database
rakeshsapbo 36 posts Joined 04/13
12 Sep 2014
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 .........

Rakesh Reddy G
Glass 225 posts Joined 04/10
12 Sep 2014

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

Raja_KT 1246 posts Joined 07/09
12 Sep 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

rakeshsapbo 36 posts Joined 04/13
12 Sep 2014

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

 

 

 

 

Rakesh Reddy G

dnoeth 4628 posts Joined 11/04
12 Sep 2014

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.

Dieter

You must sign in to leave a comment.