How about replacing the like clause with the following:
WHERE LOCATE(DocumentAccounts.dDocAccount , UserSecurityAttributes.dAttributeName) > 0
Thanks,
Ben Keebler
Database Administrator
GTECH Florida
250 Marriott Drive
Tallahassee, FL 32399
Office: (###) ###-####
Mobile: (###) ###-####
-----Original Message-----
From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x99008443.[Email address protected]
Sent: Thursday, June 28, 2007 10:46 AM
To: LazyDBA Discussion
Subject: RE: Re : Using like with a column name
The "select job_title from jobs where job_title like 'Assistant%'" statement works for me (see output below). I am running DB2, version 8.2, Fixpack 7, on Windows XP (my pc).
select job_titles from jobs;
OUTPUT
------------------------
Assistant Administrative
Administrator-Human Resources
Assistant-Administrative
Assistant-Marketing
Assistant
Chief Executive Officer
DBA
DEVELOPER
Assistant-Human-Resources DBA
Assistant_Human_Resources OPS
Assistant_DBA BACKUP
select job_title from jobs where job_title like 'Assistant%'
OUTPUT
-------------------------
Assistant Administrative
Assistant-Administrative
Assistant-Marketing
Assistant
Assistant-Human-Resources DBA
Assistant_Human_Resources OPS
Assistant_DBA BACKUP
-----Original Message-----
From: Cosser Alex [mailto:db2udbdba-ezmlmshield-x67054276.[Email address protected]
Sent: Thursday, June 28, 2007 10:06 AM
To: LazyDBA Discussion
Subject: RE: Re : Using like with a column name
Would some examples help?
First I'll show the data without the LIKE.
select * from GCHWD8.PS_jobcode_tbl fetch first 10 rows only
SETID JOBCODE EFFDT EFF_STATUS DESCR
---------- ------------ ---------- ---------- --------------------
KMMYS 110000 1980-01-01 A Accountant
K1USA 170005 2004-01-01 A Assistant Administrative
K1USA 250000 2004-01-01 A Chief Executive Officer
K1USA 600085 2004-01-01 A Manager Finance
K1USA 830005 2004-01-01 A Staff
K1USA 910005 2004-01-01 A Trainee
K1USA AADUMY 1900-01-01 A Additional Appointment
KMMYS 120010 1980-01-01 A Administrator-Human Resources
KMMYS 170005 1980-01-01 A Assistant-Administrative
KMMYS 170035 1980-01-01 A Assistant-Marketing
Now with a LIKE
select Descr from GCHWD8.PS_jobcode_tbl where descr like 'Assistant%' fetch
first 10 rows only
DESCR
------------------------------------------------------------
Assistant
Assistant
Assistant
Assistant
Assistant
Assistant
Assistant
Assistant Administrative
Assistant I
Assistant I
10 record(s) selected.
Now with a LIKE using a concatenation (results differ)
select Descr from GCHWD8.PS_jobcode_tbl where descr like 'Assistant'||'%'
fetch first 10 rows only
DESCR
------------------------------------------------------------
Assistant Administrative
Assistant-Administrative
Assistant-Marketing
Assistant-Administrative
Assistant-Administrative
Assistant-Administrative Sr
Assistant-Executive
Assistant-Management Staff
Assistant-Nursing
Assistant-Personnel
It doesn't seem to allow using a column on the right of the LIKE. (If you
remove the RTRIM it gives a different error)
select A.setid, A.jobcode, A.Descr
from
GCHWD8.PS_jobcode_tbl a,
GCHWD8.PS_jobcode_tbl b
where A.descr LIKE rtrim(B.Descr)
AND b.setid = 'KMMYS'
and B.JOBCODE = '170005'
SQL0132N A LIKE predicate or POSSTR scalar function is not valid because
the
first operand is not a string expression or the second operand is not a
string. SQLSTATE=42824
SQL0132N A LIKE predicate or POSSTR scalar function is not valid because
the first operand is not a string expression or the second operand is not a
string.
Explanation:
A LIKE predicate or POSSTR scalar function appearing in the
statement is not valid because either the first operand is not a
string expression or the second operand is not a string.
The operand appearing to the left of a LIKE or NOT LIKE
predicate or the first operand of POSSTR must be a string
expression. The value appearing to the right of the predicate or
the second operand of POSSTR can be one of:
o a constant
o a special register
o a host variable
o a scalar function whose operands are any of the above
o an expression concatenating any of the above
with the restrictions that:
o no element in the expression can be of type LONG VARCHAR,
CLOB, LONG VARGRAPHIC, or DBCLOB. In addition it cannot be a
BLOB file reference variable.
o the actual length of the expression cannot be more than 4000
bytes.
A LIKE predicate or POSSTR scalar function cannot be used with
DATE, TIME, or TIMESTAMP.
The statement cannot be processed.
User Response:
Check and correct the syntax of LIKE and POSSTR
sqlcode : -132
sqlstate : 42824
The same thing works without the LIKE.
select A.setid, A.jobcode, A.Descr
from
GCHWD8.PS_jobcode_tbl a,
GCHWD8.PS_jobcode_tbl b
where A.descr = rtrim(B.Descr)
AND b.setid = 'KMMYS'
and B.JOBCODE = '170005'
SETID JOBCODE DESCR
---------- ------------
------------------------------------------------------------
GBR01 170005 Assistant-Administrative
HKG01 170005 Assistant-Administrative
KMMYS 170005 Assistant-Administrative
KPIND 170005 Assistant-Administrative
KRSI1 170005 Assistant-Administrative
MYS01 170005 Assistant-Administrative
NLD01 170005 Assistant-Administrative
NLD01 170005 Assistant-Administrative
NZL01 170005 Assistant-Administrative
PJCSI 170005 Assistant-Administrative
SGP01 170005 Assistant-Administrative
SHARE 170005 Assistant-Administrative
Regards
Alex Cosser
-----Original Message-----
From: Thompson Keith
[mailto:db2udbdba-ezmlmshield-x4061361.[Email address protected]
Sent: 28 June 2007 14:23
To: LazyDBA Discussion
Subject: FW: Re : Using like with a column name
*** WARNING : This message originates from the Internet ***
If I replace the + with || the result is still a SQL0132N error. The issue
is that it does not appear that like can be used to reference the contents
of a column. Unless someone know a way around this. Even a basic statement
like this:
db2 "select * from kt.a a, kt.b b where b.col1 like a.col1"
fails with the same error.
-----Original Message-----
From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x70003537.[Email address
protected]
Sent: Thursday, June 28, 2007 3:59 AM
To: LazyDBA Discussion
Subject: RE: Re : Using like with a column name
Well, well, well!
It's p! hehe. How have you been? If it is still not working for you,
what would happen if you replace the plus sign (+) with the concatenation
sign (||)?
Please take a few minutes to provide feedback on the quality of service you
received from our staff. The Department of Education values your feedback as
a customer. Commissioner of Education Jeanine Blomberg is committed to
continuously assessing and improving the level and quality of services
provided to you.Simply use the link below. Thank you in advance for
completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: p [mailto:db2udbdba-ezmlmshield-x22229732.[Email address protected]
Sent: Wednesday, June 27, 2007 4:51 PM
To: LazyDBA Discussion
Subject: Re : Using like with a column name
If I'm mistaken, somebody tell me; but I think in DB2 you would say:
WHERE UserSecurityAttributes.dAttributeName LIKE
'%DocumentAccounts.dDocAccount %'
Note that this means you will always get a tablescan to resolve this as no
index could be used because of the % front padding.
Hope this helps, Pierre.
----- Message d'origine -----
De: Thompson Keith <db2udbdba-ezmlmshield-x80675655.[Email address
protected]
Date: Mercredi, Juin 27, 2007 4:19 pm
Objet: Using like with a column name
À: LazyDBA Discussion <[Email address protected]
> I need to convert some SQL server sql to db2. The sql I am starting
> with looks like this:
>
> WHERE (UserSecurityAttributes.dAttributeName LIKE '%' +
> DocumentAccounts.dDocAccount + '%')
>
> They are trying to allow padding of extra characters on either side of
> the column name. DB2 returns the error below when a like is used with
> a column name.
>
> SQL0132N A LIKE predicate or POSSTR scalar function is not valid
> because the first operand is not a string expression or the second
> operand is not a string. SQLSTATE=42824
>
> Is there another way to do this in db2?
>
> Thanks,
>
> Keith
>
>
> -----------------------------------------------------------------
> ----
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To Subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
Pierre Saint-Jacques
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB
Authorised and regulated by the Financial Services Authority.
This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
This email may contain confidential and privileged material for the
sole use of the intended recipient(s). Any review, use, retention,
distribution or disclosure by others is strictly prohibited. If you
are not the intended recipient (or authorized to receive for the
recipient), please contact the sender by reply email and delete all
copies of this message. Also, email is susceptible to data
corruption, interception, tampering, unauthorized amendment and
viruses. We only send and receive emails on the basis that we are
not liable for any such corruption, interception, tampering,
amendment or viruses or any consequence thereof.
DB2 & UDB email list listserv db2-l LazyDBA home page