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
 

 

Comparing concatenated digits against a column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Comparing concatenated digits against a column
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    Post subject: Re: Comparing concatenated digits against a column
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    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

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

Site Director


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

PostPosted: Tue Mar 30, 2010 1:52 am    Post subject:
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    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Tue Mar 30, 2010 2:50 am    Post subject:
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    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Wed Mar 31, 2010 12:45 am    Post subject:
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    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Wed Mar 31, 2010 4:25 am    Post subject:
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    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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
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
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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