|
View previous topic :: View next topic
|
| Author |
Message |
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
Hi,
I am trying to run the following query in SPUFI.
| Code: |
SELECT TAB1.OUT_FLG
FROM DEVB.TAB2_DEPT TAB2,
DEVB.TAB1_PROD_TYP TAB1
WHERE TAB2.UNIT_ID = 'ABD'
AND TAB2.DEPT_ID = '01'
AND TAB1.PROD_TYP_DSC LIKE
CHAR('''%'||RTRIM(TAB2.DEPT_DSC)||'%''')
FETCH FIRST 1 ROW ONLY
WITH UR;
|
It is giving the SQLCODE=-132 which says that "ERROR: AN OPERAND OF LIKE IS NOT VALID".
I tried to use concat function also instead of using the operator '||'. Does anybody having any idea what is wrong in the query? Any help is highly appreciated. Thanks. |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Its becuase DB2 will convert the the entire string to CHAR and then it will use for LIKE operation where it will not get the '% ' required for LIKE parameter
regards,
Chandan |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
I have given CHAR after LIKE operator because TAB1.PROD_TYP_DSC column is having datatype CHAR. I could not get what you want to say.
I used CHAR('''%'||RTRIM(TAB2.DEPT_DSC)||'%''') in the select clause instead of using it in WHERE clause. There it is getting displayed as '%MEAT%' if the column TAB2.DEPT_DSC is having the value MEAT in the table TAB2. And, '%MEAT%' should be the value which can be used with LIKE operator. but i am getting SQLCODE=-132 when i use it in WHERE clause. . |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Here as you are getting '%MEAT%' as a result of CHAR function DB2 will consider '%MEAT%' as char entire string including '%'.
This % will not be considered as one which DB2 uses for LIKE operation. |
|
| Back to top |
|
|
GaganGarg
Active User
Joined: 31 Mar 2010
Posts: 134
Location: India
|
|
|
|
| Ohk. Any other way to do the same in query? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
I am not sure but I think you cant use another DB2 column in LIKE parameter..
But let other people go through this post as they will be able to guide you correctly |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
You can try locate instead.
locate(TAB2.DEPT_DSC,TAB1.PROD_TYP_DSC) <> 0 |
|
| Back to top |
|
|
|
|
