Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Concatenating or Splitting the Column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 2:17 pm    Post subject: Concatenating or Splitting the Column
Reply with quote

Hi,
I have 2 tables:
Table A
CIN SORT ACCOUNT
A 10000 10001
A 10000 10002
B 10000 10001

Table B
ROUTER ID
A0000 1000010001
B1000 1000010002
C2000 1000010003

In table B the column ID is made by combining SORT & ACCOUNT.

I need to get the SORT , ACCOUNT & ROUTER of any CIN that is provided as input

Ex:
If CIN is A
10000 10001 A0000
10000 10002 B1000

If CIN is B
10000 10001 A0000

Could someone explain if we can Combine or Split the columns while comparing?
** ALL are numberic columns.
Back to top
View user's profile Send private message

raviprasath_kp
Warnings : 1

New User


Joined: 20 Feb 2005
Posts: 65
Location: chennai

PostPosted: Wed Oct 17, 2007 2:25 pm    Post subject: Reply to: Concatenating or Splitting the Column
Reply with quote

can u explain little bit more
bucause i cannot understand what u want exactly
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 2:33 pm    Post subject: Reply to: Concatenating or Splitting the Column
Reply with quote

I need to get the Sort (table A) , Account (table A) & Router (table B) for any CIN (table A) provided as input.

Table A & Table B acn be joined on ID
where ID(table B) has combined SORT & ACCOUNT....
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Wed Oct 17, 2007 3:24 pm    Post subject:
Reply with quote

Hi Gaurav,

You can combine columns but I am not sure about spliting up of columns.


Use Concatenation OR STRING option.

Like this

Code:
SELECT SORT.A||ACCOUNT.A, ROUTER.B
FROM A, B
WHERE
CIN = :C-IN


Let us know if you have any doubt.
Eleborate your question so that someone can help you.
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 3:41 pm    Post subject:
Reply with quote

Hi Hemant,

As i had mentioned, all are Numeric columns and || gives error.

Is there any way to convert number to string in DB2 and then i can concatenate
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Wed Oct 17, 2007 3:52 pm    Post subject:
Reply with quote

Hi Gaurav,

Quote:
A0000


Do you think this is an Numeric? Its ok.

But you can achieve this using Cobol-DB2 Programming Logic. Try out with Pgm, If you stuck out somewhere, Let us know...
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 4:28 pm    Post subject: Reply to: Concatenating or Splitting the Column
Reply with quote

I am sorry for mentioning that A000 as number.. Actually i had put assumed data here.
Well I have got a solution and it is this...
Code:
SELECT                                                               
   ROUTER                                                         
FROM                                                                 
   TABLE B
WHERE                                                               
  B.ID  =                                                         
 (SELECT CONCAT(RTRIM(CHAR(SORT)), RTRIM(CHAR(ACCOUNT))) 
   FROM           TABLE A
   WHERE          CIN = <input>);     

                         



I have checked it and its working.. Only performance is a concern now icon_smile.gif

Thanks everyone who tried to help...
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Wed Oct 17, 2007 4:50 pm    Post subject:
Reply with quote

Hi Gaurav,

You're welcome.

Quote:
Well I have got a solution and it is this


Nice to hear. icon_lol.gif
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Wed Oct 17, 2007 5:34 pm    Post subject:
Reply with quote

Hi Hemanth,

The CHAR function has a problem.

If the SORT is 01001 then CHAR(SORT) gives only 1001, due to which it is not being selected.

Can you help me retain the leading 0's when converting INTEGER to CHAR.

I dont mind if there is some other function available for the same. Thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts splitting a file abdulrafi DFSORT/ICETOOL 3 Fri Mar 24, 2017 11:51 am
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us