Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Tone

New User

Joined: 01 Oct 2008
Posts: 12
Location: India

 Posted: Tue Mar 30, 2010 12:42 am    Post subject: Comparing concatenated digits against a column 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

Craq Giegerich

Senior Member

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

Posted: Tue Mar 30, 2010 12:54 am    Post subject: Re: Comparing concatenated digits against a column

 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
Tone

New User

Joined: 01 Oct 2008
Posts: 12
Location: India

 Posted: Tue Mar 30, 2010 1:09 am    Post subject: 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
Craq Giegerich

Senior Member

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

 Posted: Tue Mar 30, 2010 1:31 am    Post subject: 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!
Tone

New User

Joined: 01 Oct 2008
Posts: 12
Location: India

 Posted: Tue Mar 30, 2010 1:34 am    Post subject: Sorry for typo - ABCD is DECIMAL(12, 0) Thanks
dick scherrer

Site Director

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

 Posted: Tue Mar 30, 2010 1:52 am    Post subject: 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?
Tone

New User

Joined: 01 Oct 2008
Posts: 12
Location: India

 Posted: Tue Mar 30, 2010 2:04 am    Post subject: 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
CICS Guy

Senior Member

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

Posted: Tue Mar 30, 2010 2:50 am    Post subject:

 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?
Tone

New User

Joined: 01 Oct 2008
Posts: 12
Location: India

 Posted: Wed Mar 31, 2010 12:37 am    Post subject: 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.
CICS Guy

Senior Member

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

Posted: Wed Mar 31, 2010 12:45 am    Post subject:

 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?
dbzTHEdinosauer

Global Moderator

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

 Posted: Wed Mar 31, 2010 3:08 am    Post subject: Hat's off to CICS_Guy - good thinking.
CICS Guy

Senior Member

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

 Posted: Wed Mar 31, 2010 4:25 am    Post subject: 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...
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Comparing two sequential files Mohan Kothakota COBOL Programming 14 Mon Apr 16, 2018 6:35 pm Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am To get Non matching row after compari... vinu78 DB2 7 Fri Jan 05, 2018 5:43 pm Comparing dates in different formats migusd SYNCSORT 4 Sat Nov 18, 2017 3:02 am Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us