|
View previous topic :: View next topic
|
| Author |
Message |
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
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 |
|
 |
raviprasath_kp
Warnings : 1
New User
Joined: 20 Feb 2005
Posts: 65
Location: chennai
|
|
|
|
can u explain little bit more
bucause i cannot understand what u want exactly |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi Gaurav,
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 |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
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 
Thanks everyone who tried to help... |
|
| Back to top |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi Gaurav,
You're welcome.
| Quote: |
| Well I have got a solution and it is this |
Nice to hear.  |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
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 |
|
|
|
|
