View previous topic :: View next topic
|
Author |
Message |
Tone
New User
Joined: 01 Oct 2008 Posts: 12 Location: India
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Tone
New User
Joined: 01 Oct 2008 Posts: 12 Location: India
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Tone
New User
Joined: 01 Oct 2008 Posts: 12 Location: India
|
|
|
|
Sorry for typo - ABCD is DECIMAL(12, 0)
Thanks |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Tone
New User
Joined: 01 Oct 2008 Posts: 12 Location: India
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
Tone
New User
Joined: 01 Oct 2008 Posts: 12 Location: India
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Hat's off to CICS_Guy - good thinking. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
|