IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Comparing concatenated digits against a column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Tue Mar 30, 2010 12:42 am
Reply with quote

Hi

I would like to know which is the best way to compare conctatenated digits againast a numeric column.

I have a column defined in Table A as ABCD as DECIMAL(5, 0)
and in Table B, I have 4 different columns
A-NUM DECIMAL(5, 0)
B-NUM DECIMAL(5, 0)
C-NUM DECIMAL(1, 0)
D-NUM DECIMAL(1, 0)


I need to compare the value of ABCD against the combination of
C-NUM, D-NUM, A-NUM,B-NUM in that order.

The way i am trying now is
"
Where
DIGITS(ABCD) = DIGITS(C-NUM)||DIGITS(D-NUM)||DIGITS(A-NUM)||
DIGITS(B-NUM)||
"
eg: ABCD = 123456789123
A = 34567
B = 89123
C = 1
D = 2

But it is taking way too much time, Please let me know how I can speed it up

Thanks
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Mar 30, 2010 12:54 am
Reply with quote

Tone wrote:
eg: ABCD = 123456789123
A = 34567
B = 89123
C = 1
D = 2

But it is taking way too much time, Please let me know how I can speed it up

Thanks



REDESIGN
Back to top
View user's profile Send private message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Tue Mar 30, 2010 1:09 am
Reply with quote

How I wish I could redesign the system,
The variables mentioned above are defined in multiple applications.

So only thing i can touch is this query.
however i change it, I am not able to escape from this comparison of single column against multiple ones.

If nothing works, will have split this into multiple queries.
Just wanted to know, whether there is a better way to doing this

Thanks
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Mar 30, 2010 1:31 am
Reply with quote

Since A & B are 5 digits each and C & D are 1 digit each you would be comparing the 5 digits from ABCD against 12 digits from the other 4 fields how would you ever expect to find a match? You example doesn't even match what you say ABCD is defined as!
Back to top
View user's profile Send private message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Tue Mar 30, 2010 1:34 am
Reply with quote

Sorry for typo - ABCD is DECIMAL(12, 0)

Thanks
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Mar 30, 2010 1:52 am
Reply with quote

Hello,

Unless i am misreading things, the value for ABCD is not in sync with A, B, C, and D.

How many rows are in table B? Will more than one row be returned from this query? How many?

Is A in table B an index or the high-order part of an index?
Back to top
View user's profile Send private message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Tue Mar 30, 2010 2:04 am
Reply with quote

ABCD is DECIMAL(12, 0) and
A-NUM DECIMAL(5, 0)
B-NUM DECIMAL(5, 0)
C-NUM DECIMAL(1, 0)
D-NUM DECIMAL(1, 0)

I want to find return records that satisfy
ABCD = C-NUM || D-NUM || A-NUM || B-NUM
For whatever values of those columns

Table A has 8 indexes while Table B has one index.
ABCD is part of multiple indexes in Table A, C/D/A & B are part of the single index in Table B

Table A has - 5504845 records
Table B has - 153039 records

Without the above mentioned comparison the query returns ~29000 records. And for the current vlaues in table, the count will be the same with digit comparison

Hope I was clear on that. Please let me know if I missed something.

Thanks
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Tue Mar 30, 2010 2:50 am
Reply with quote

Quote:
ABCD = C-NUM || D-NUM || A-NUM || B-NUM

Can the ABCD be cast as numeric digits that can be sub-stringed against the -NUMs?

Or if A-NUM + B-NUM * 100,000 + D-NUM * 10,000,000,000 + C-NUM * 100,000,000,000 = ABCD?
Back to top
View user's profile Send private message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Wed Mar 31, 2010 12:37 am
Reply with quote

Thanks CICS Guy,

I think i overlooked that,
A-NUM + B-NUM * 100,000 + D-NUM * 10,000,000,000 + C-NUM * 100,000,000,000 = ABCD

Even though the query looks monstrous, it works out really well.
without the DIGITS function, the query is returning record in seconds.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Wed Mar 31, 2010 12:45 am
Reply with quote

Quote:
Even though the query looks monstrous, it works out really well.
without the DIGITS function, the query is returning record in seconds.
Is seconds good? How long for the original method?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Mar 31, 2010 3:08 am
Reply with quote

Hat's off to CICS_Guy - good thinking.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Wed Mar 31, 2010 4:25 am
Reply with quote

Thanks Dick, and welcome back....

Much like most Dinosaurs, I've always tended to look a a problem from alternate views. Experience does tell....grin...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts first column truncated in search result IBM Tools 13
No new posts Split a record with data in a differe... DFSORT/ICETOOL 8
No new posts Want to mask Middle 8 Digits of Debit... COBOL Programming 3
Search our Forums:

Back to Top