| View previous topic :: View next topic |
| Author |
Message |
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
 Posted: Thu Mar 25, 2010 12:15 am Post subject: Cursor to compare variable length field in Join - DB2 and co |
 |
|
We have a Query mentioned below
| Code: |
EXEC SQL
DECLARE FOGCSE10-CURSOR CURSOR FOR
SELECT A.CUSTOMER_KEY,
A.CCY,
A.SFX,
B.MESSAGE_TYPE,
B.AUTOGEN_TIME,
B.FREQUENCY,
B.UPDATE_TIMESTAMP,
B.LAST_RUN_DATE,
C.MT101_SENDER_BIC
FROM TFOCPAC_CORP_ACCTS A,
TQE0520_AUTO_FREQ B,
TQE0700_MANDATE C
WHERE B.VISIBILITY = 'V'
AND A.CUSTOMER_KEY = SUBSTR(B.STATEMENT_ACC_ID,7,8 )
AND A.CCY = SUBSTR (B.STATEMENT_ACC_ID,16,3)
AND A.SFX = SUBSTR (B.STATEMENT_ACC_ID,19,3)
AND B.STATEMENT_ACC_ID = C.STATEMENT_ACC_ID
WITH UR
FOR FETCH ONLY
END-EXEC.
|
We were comparing Customer Key with Statement_Acc_Id assuming that it will always be a length of 8, BUT in production we saw some cases where Cutomer Key was always 8 ( 6 character + 2 spaces (padding done)) but in Statmenet_Acc_id we can have the length 0f 6 and no padding then a '-' character and then CCY and SFX starts.
Please advise if we can do some kind of length comparison in this cursor where the length in the substring can vary or any other way we can handle it.
_________________
Thanks |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 25, 2010 10:27 am Post subject: |
|
|
JOSHI.SHOBHA,
post some sample input data for B.STATEMENT_ACC_ID and A.CUSTOMER_KEY
kolusu |
|
| Back to top |
|
|
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
| Posted: Thu Mar 25, 2010 9:56 pm Post subject: |
|
|
B.STATEMENT_ACCT_ID is in the form
| Code: |
837584PSCOT-EURC
831806DIPOSP-USDA
17400ALEHO-CAD106
560400ALEHO-EURA
460008RITT-EURA
|
and A.STATEMENT is in the form
| Code: |
CUSTOMER_KEY CCY SFX
CHAR(8) CHAR(3) CHAR(3)
------------ ------- -------
****************************
/FERBTYO AED 000
/ACBCILO ATS
/ACBCILO AUD
/ACBCILO BEC
|
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 26, 2010 11:06 am Post subject: |
|
|
JOSHI.SHOBHA,
I don't see a single matching record. Anyway here is an untested sql which I think will give you the desired results
| Code: |
DECLARE FOGCSE10-CURSOR CURSOR FOR
SELECT A.CUSTOMER_KEY,
A.CCY,
A.SFX,
B.MESSAGE_TYPE,
B.AUTOGEN_TIME,
B.FREQUENCY,
B.UPDATE_TIMESTAMP,
B.LAST_RUN_DATE,
C.MT101_SENDER_BIC
FROM TFOCPAC_CORP_ACCTS A,
TQE0520_AUTO_FREQ B,
TQE0700_MANDATE C
WHERE B.VISIBILITY = 'V'
AND A.CUSTOMER_KEY = SUBSTR(B.STATEMENT_ACCT_ID,7,
(POSSTR(B.STATEMENT_ACCT_ID,'-')-7))
AND A.CCY = SUBSTR(B.STATEMENT_ACCT_ID,
POSSTR(B.STATEMENT_ACCT_ID,'-')+1,3)
AND A.SFX = SUBSTR(B.STATEMENT_ACCT_ID,
POSSTR(B.STATEMENT_ACCT_ID,'-')+4,3)
AND B.STATEMENT_ACC_ID = C.STATEMENT_ACC_ID
WITH UR
FOR FETCH ONLY
|
|
|
| Back to top |
|
|
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
| Posted: Sun Mar 28, 2010 10:18 pm Post subject: |
|
|
Thanks Kolusu .. it worked 
_________________
Thanks |
|
| Back to top |
|
|
Terry_Heinze
Supermod
Joined: 31 May 2004
Posts: 391
Topics: 4
Location: Richfield, MN, USA
|
| Posted: Sun Mar 28, 2010 10:20 pm Post subject: |
|
|
Please post in the more appropriate forum (Database) next time.
_________________
....Terry |
|
| Back to top |
|
|
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
| Posted: Mon Mar 29, 2010 9:59 pm Post subject: |
|
|
Sorry ... Will take care Terry
_________________
Thanks |
|
| Back to top |
|
|
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
| Posted: Mon Apr 05, 2010 4:16 am Post subject: |
|
|
Kolusu
Actually when we ran it thru our DBA he said that this might have prformance issues.... and asked us to provide alternative ... please advise can we remove the substring from this query and do it as a part of program or DFSORT utility using parse or some other option. Not sure if we are going on the correct path. Please advise.
_________________
Thanks |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Apr 05, 2010 7:00 am Post subject: |
|
|
Joshi.Shobha,
a lot has to do with the number of rows in the 3 tables.
if you want to take the time to explain the size and relationship of the 3 tables,
members could provided better suggestions.
as an aside,
anytime you off-load the work from DB2,
you will not have your DBA's complaining.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 05, 2010 9:58 am Post subject: |
|
|
Joshi.Shobha,
The other alternative would be just selecting the STATEMENT_ACCT_ID alone and parse it using unstring and then use it as a host variable for the main table. Btw you did have a SUBSTR function in the original SQL.
How do you plan to use DFSORT here? Unload all the 3 tables? and then perform the matching?
Kolusu |
|
| Back to top |
|
|
JOSHI.SHOBHA
Beginner
Joined: 28 Jul 2007
Posts: 20
Topics: 7
|
| Posted: Mon Apr 05, 2010 10:48 pm Post subject: |
|
|
Kolusu
Actually we were planning to keep the comparisons for TQE0520_AUTO_FREQ and TQE0700_MANDATE table as it is just remove the TFOCPAC_CORP_ACCTS thing. And after the work is completed for the 2 tables then dump the O/p of cursor and TFOCPAC_CORP_ACCTS table in a file and use DFSORT to compare the STATEMENT_ACCT_ID.
Thanks
Shobha
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Tue Apr 06, 2010 7:40 am Post subject: |
|
|
JOSHI.SHOBHA,
If you need help with DFSORT , open another topic with all the details. Post the LRECL and RECFM of both files and how you want the final output.
Kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
