| View previous topic :: View next topic |
| Author |
Message |
psridhar
Beginner
Joined: 16 May 2004
Posts: 68
Topics: 26
|
 Posted: Sun Nov 14, 2004 9:52 am Post subject: Using "LIKE" predicate |
 |
|
Hi,
I need help in using "LIKE" predicate. Requirement is as follows.
I have a cobol program which inserts the following 01 level variable in to a column TBL_PKEY of DB2 table T1 which is defined C(20).
| Code: | 01 PRIMARY-KEY.
05 ACCOUNT-NO PIC S9(9) COMP.
05 CURRENCY PIC X(003). |
Say ACCOUNT-NO = 40022154 and CURRENCY = CHF
Then TBL_PKEY is inserted with some value having special characters. If I copy that value and put it in one dataset and say 'HEX ON', I am getting the following data.
Now my requirement is if any column of the primary key is given, is it possible to retrieve the row using the 'LIKE' predicate through a BATCH SPFUI.
If the entire primary key is given, I am using the following spufi to retrieve the data.
| Code: | SELECT C1, C2
FROM T1
WHERE TBL_PKEY = X'0262B08AD1D7E8' ; |
|
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Sun Nov 14, 2004 7:15 pm Post subject: |
|
|
psridhar,
What is the column definition of your primary key?. Is it defined as character? If it is defined as character then you can use substr function to extract the first 4 bytes
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psridhar
Beginner
Joined: 16 May 2004
Posts: 68
Topics: 26
|
| Posted: Mon Nov 15, 2004 8:20 am Post subject: |
|
|
Hi Kolusu,
Thank you for the help.
DB2 column T1.TBL_PKEY is defined as CHAR. Let me explain my requirement again. Say T2.ACCOUNT-NO and T2.CURRENCY are the primary keys of the table T2. T1.TBL_PKEY will store the primary key of the table T2. If both ACCOUNT-NO and CURRENCY are given as inputs to T1, then following SPUFI will retrieve the row.
| Code: | SELECT TBL_PKEY
FROM T1
WHERE TBL_PKEY = X'0262B08AD1D7E8' ; |
Say only CURRENCY is given then my spufi should be some thing simillar to the below one.
| Code: | SELECT TBL_PKEY
FROM T1
WHERE TBL_PKEY LIKE %X'D1D7E8'% ; |
Say T1.TBL_PKEY is storing primary key of a table T2 having 4 columns as primary key. The user can give any one of the primary key columns of table T2 or entire primary key value of the table T2. If entire primary key value is given, I can convert it in to HEX and use the where caluse with "=" as below.
| Code: | | WHERE TBL_PKEY = X'0262B08AD1D7E8' ; |
If any one of the primary key columns of table T2 is given then how to retrieve the row in table T1.
Hope now my problem is clear to you. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 15, 2004 8:51 am Post subject: |
|
|
Psridhar,
Try this
| Code: |
SELECT *
FROM T1
WHERE HEX(SUBSTR(TBL_PKEY,1,4)) LIKE 'D1D7E8%'
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psridhar
Beginner
Joined: 16 May 2004
Posts: 68
Topics: 26
|
| Posted: Mon Nov 15, 2004 9:11 am Post subject: |
|
|
Hi Kolusu,
Thank you very much for the help. It works fine.
Sridhar P |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
