cancel
Showing results for 
Search instead for 
Did you mean: 

How to use LIKE operator with string retrieval argument

Former Member
0 Kudos

Hi All,

(With PB12.5 here)

I have a datasource, which is with string retrieval argument (arg_1) in where clause.

When use LIKE operator, it seems not working, but the query is working fine with = operator.

Fyi, I did tried the following

1. .....where col_name like '%' || :arg_1 || '%'

2. .....where col_name LIKE :arg_1 + '%'

Kindly advise.

Thank you in advance.

BR,

Yow

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi All,

I have tried the following but still not working.

Fyi, the db with  SQL Server 2008 R2, and it retrieves no data when use LIKE operator, btw, there is one record when "colname = :arg_1". And sample data = BTM/EPPO/16/0038.

1.

We have always added the "%" to the string before passing the argument.

So string contains "%mytext%"

where clause is colname like :as_arg

2.

Append the '%' in PowerBuilder before the retrieve...

String                      ls_arg

ls_arg                      = 'value' + '%'

dataWindow.Retrieve(ls_arg)

your where clause looks like: where col_name LIKE :arg_1

3.

... WHERE col_name LIKE  '%' + :arg_1 + '%'

Kindly advise.

Thank you.

BR,

Yow

Former Member
0 Kudos

Hello Yow,

Can you supply the entire SQL?

Have you checked the return information from the retrieve (SQLCA.SQLCode and/or SQLCA.SQLErrText)?

If you don't have DBError coded, add some "dummy" code in DBError like:

integer xyzzy

xyzzy = 1

Then you can set a break and see if there is an error returned.

Thank You,

Christopher

ricardojasso
Participant
0 Kudos

Yow,


What is the length of col_name? Is it Char or Varchar? Other?

Have you tried WHERE col_name LIKE '%BTM/EPPO/16/0038%' to see if there might be some issue with the argument arg_1?

Have you tried WHERE col_name LIKE '%' + LTRIM(RTRIM(:arg_1)) + '%'?


Regards,

Ricardo

Former Member
0 Kudos

Hi Ricardo,

1. The colname is varchar (20).

2. If directly put the "...WHERE col_name LIKE '%BTM/EPPO/16/0038%'", the one record will appear


3. With "..WHERE col_name LIKE '%' + LTRIM(RTRIM(:arg_1)) + '%'", it will throws error below:


---

Select Error: SQLSTATE = 37000

{Microsoft][SQL Server Native Client 10.0][SQL Server] Error converting data type varchar to numeric.

---

Fyi, the argument is set datatype = string, tried with number is also give the same error as above.


Kindly advise.

Thank you.



Former Member
0 Kudos

Hi Christopher,


The SQLCA.SQLCode is 0.


Entire SQL as below. Kindly advise.



----


select SpareType.PartTypeNo,

SpareType.PartName,

m.code as makercode,

m.name as makername,

sparetype.makerref,

stockclass.code as stockclasscode,

stockclass.descr as stockclassdescr,

spareunit.stockmax as stockitem_stockmax,

sum(sparelocation.instock) totalinstock,

spareunit.stockmax,

spareunit.stockmin,

spareunit.reorderlevel,

spareunit.reorderquantity, 

SpareUnit.NoRequisitioned as wanted,

spareunit.stockeditem,

(select formno from orderform where orderform.orderid = orderline.orderid) FormNo,

(select createddate from orderform where orderform.orderid = orderline.orderid) PR_Date,

(select name from amosuser where userid in (select createdby from orderform where orderform.orderid = orderline.orderid)) PR_Enteredby,

orderline.desired as PR_Qty,

(select comment1 from orderform where orderform.orderid = orderline.orderid) NAVAPONo,

(select comment2 from orderform where orderform.orderid = orderline.orderid) LundinPONo,

(select Cst_EpomsPONo from orderform where orderform.orderid = orderline.orderid) EpomsPONo,

(select approveddate from orderform where orderform.orderid = orderline.orderid) PO_Date,

(select name from amosuser where userid in (select approvedby from orderform where orderform.orderid = orderline.orderid)) PO_Enteredby,

orderline.quantity as PO_Qty,

(select descr from workflowstatus where statusid in (select workflowstatusid from orderform where orderform.orderid = orderline.orderid)) OrderStatus,

(select latestdeliverydate from orderform where orderform.orderid = orderline.orderid) PO_ETA,

orderline.received as PO_Receipts

FROM {oj spareunit spareunit

LEFT OUTER JOIN location ON spareunit.defaultlocationid = location.locationid

RIGHT OUTER JOIN orderline orderline ON spareunit.partid = orderline.partid and orderline.status=1

and orderid in (select orderid from orderform where formstatus in (1,2)

and ( comment1 = :ls_nava_pono

or comment2=:ls_lundin_pono

//or cst_epomspono LIKE :ls_epoms_pono )

//or cst_epomspono LIKE  '%' + :ls_epoms_pono + '%' --> Fail

//or cst_epomspono LIKE  :ls_epoms_pono + '%' --> Fail

//or cst_epomspono LIKE '%BTM/EPPO/16/0038%')  --> The record will appear if test without argument

//or cst_epomspono LIKE '%' + LTRIM(RTRIM(:ls_epoms_pono)) + '%')  --> Fail

)

},

{oj sparetype LEFT OUTER JOIN address pv ON sparetype.primaryvendorid = pv.addressid

LEFT OUTER JOIN address m ON sparetype.makerid = m.addressid

LEFT OUTER JOIN stockclass ON sparetype.stockclassid = stockclass.stockclassid

LEFT OUTER JOIN spareunit spareunit_b ON sparetype.parttypeid = spareunit_b.parttypeid

LEFT OUTER JOIN sparelocation  ON spareunit_b.partid = sparelocation.partid},

installation, department

WHERE ( sparetype.parttypeid = spareunit.parttypeid )

and          ( spareunit.deptid = department.deptid ) 

and ( installation.instcode = :li_instcode )

and ( installation.instid = department.instid )

GROUP BY SpareType.PartTypeNo, SpareType.PartName, m.code, m.name, sparetype.makerref, stockclass.code, stockclass.descr, spareunit.stockeditem, spareunit.stockmax ,spareunit.stockmin, spareunit.reorderlevel,spareunit.reorderquantity, SpareUnit.NoRequisitioned,

orderlineid, orderline.orderid, orderline.desired, orderline.quantity, orderline.received

ORDER BY sparetype.parttypeno          ASC 


----



Thank you.

Former Member
0 Kudos

Hello Yow,

Have you tried getting rid of the PB Outer Join syntax and replacing it with ANSI format?  Then run the query in Management Studio and see what you get.

Former Member
0 Kudos

Hi Yow,

what's you setting for DisableBind (it's an option for DBParm).

Have you already tried to trace the database connection? (Enable database tracing - Real's PowerBuilder Howto)

Maybe you will find whats going wrong.

Regards,

René

Former Member
0 Kudos

Perhaps the plus signs make the db try to do implicit conversion of '%' to a numeric....



3. With "..WHERE col_name LIKE '%' + LTRIM(RTRIM(:arg_1)) + '%'", it will throws error below:


---

Select Error: SQLSTATE = 37000

{Microsoft][SQL Server Native Client 10.0][SQL Server] Error converting data type varchar to numeric.

If I hear you correctly below SELECT should return something when argument variable ls_epoms_pono contains


     %BTM/EPPO/16/0038%


with no quotes in the variable but returns nothing?


FROM {oj spareunit spareunit

LEFT OUTER JOIN location ON spareunit.defaultlocationid = location.locationid

RIGHT OUTER JOIN orderline orderline ON spareunit.partid = orderline.partid and orderline.status=1

and orderid in (select orderid from orderform where formstatus in (1,2)

and ( comment1 = :ls_nava_pono

or comment2=:ls_lundin_pono

or cst_epomspono LIKE :ls_epoms_pono )


Former Member
0 Kudos

Hi Lars Mosegaard,

with select statement "....or cst_epomspono LIKE :ls_epoms_pono )" and argument variable below, without quotes, it does not return the record.

%BTM/EPPO/16/0038%

Tested with same select statement above with quotes ( '%BTM/EPPO/16/0038%') is also return no record.

I did a testing without the argument as below, will return the record.

" ...or cst_epomspono LIKE '%BTM/EPPO/16/0038%') 

Kindly advise.

Thank you.

Former Member
0 Kudos

Hi René,

First of all, the ini file has no DisableBind setting in.

After I put in, as below, it is still does not return the result.

DbParm=ConnectString='DSN=xxx;UID=xxx;PWD=xxx';DisableBind=0

or

DbParm=ConnectString='DSN=xxx;UID=xxx;PWD=xxx';DisableBind=1



I tried to put in the dbtracefile in ini file as well, there is no file created in stated directory after run the report.

dbtracefile=C:\Users\xxx\Desktop\sqltrace.log

Kindly advise.

Thank you.

BR,

Yow

Former Member
0 Kudos

Hi Christopher,

I never try this before, could you provide some guide for this.

Thank you.

BR,

Yow

Former Member
0 Kudos

You should separate DBParm parameters with commas:

DbParm=ConnectString='DSN=xxx;UID=xxx;PWD=xxx',DisableBind=1


Seems to be an ODBC connection? DisableBind only works if driver support this.


To enable trace you can use the "Generate Trace" option in Database Profile Setup dialog (Connection Tab) or with SQLCA.DBMS = "TRACE ODBC".

PowerBuilder asks you on connect for trace options.


For ODBC you can also trace the ODBC API Calls (see "Options" tab in database profile Setup dialog). But I don't know if it helps.


Former Member
0 Kudos

Check out:

LIKE (Transact-SQL)

I talks about ASCII vs Unicode.  It is possible that the argument in the datawindow becomes unicode and if it does, even if cst_epomsopono is ASCII the comparison gets treated as unicode, which has different handling of trailing spaces:

-- ASCII pattern matching with char column 

CREATE TABLE t (col1 char(30)); 

INSERT INTO t VALUES ('Robert King'); 

SELECT * 

FROM t 

WHERE col1 LIKE '% King'; -- returns 1 row 

 

-- Unicode pattern matching with nchar column 

CREATE TABLE t (col1 nchar(30)); 

INSERT INTO t VALUES ('Robert King'); 

SELECT * 

FROM t 

WHERE col1 LIKE '% King'; -- no rows returned 

 

-- Unicode pattern matching with nchar column and RTRIM 

CREATE TABLE t (col1 nchar (30)); 

INSERT INTO t VALUES ('Robert King'); 

SELECT * 

FROM t 

WHERE RTRIM(col1) LIKE '% King'; -- returns 1 row

Good luck.

ricardojasso
Participant
0 Kudos

Try WHERE col_name LIKE '%' + CONVERT(VARCHAR, :arg_1) + '%'


or


WHERE col_name LIKE '%' + LTRIM(RTRIM(CONVERT(VARCHAR, :arg_1))) + '%'

Former Member
0 Kudos

It works with syntax below:

WHERE col_name LIKE '%' + LTRIM(RTRIM(CONVERT(VARCHAR, :arg_1))) + '%'


Thank you.

Answers (4)

Answers (4)

nayf
Participant
0 Kudos

Hi Yow,

Using PB 10.2 with SQL Server 2008, the following works fine for me -

... WHERE col_name LIKE  '%' + :arg_1 + '%'


Can you be clearer about 'it seems not working'? Are you getting an error, no data retrieved, apparently incorrect data etc?

Former Member
0 Kudos

No data retrieved.

nayf
Participant
0 Kudos

Does the exact same statement return any rows in SQL Server management studio?
Do you get the same results in the DW painter?

Have you checked the return value of the retrieve call?

I can definitely get WHERE col_name LIKE  '%' + :arg_1 + '%' to work so I think there's something else going on, which may be revealed by some basic error checking as Christopher has outlined below.

Good luck!

Former Member
0 Kudos

Maybe the first wild character shouldn't be there.

like '%abc%'  is not going to like   'abcd'

it will like   'zabcd'  and even  'zabc'

whereas like 'abc%' will like 'abcd'

Just a thought...

BTW:  I tend to also like  CharIndex ()

nayf
Participant
0 Kudos

Maybe it depends on the DB but leading % is fine for SQL Server 2008, so far as I can see.

For SQL Server 2008 which Yow is using, the % character can represent 'Any string of zero or more characters'. So a leading % for like '%abc%' will find 'abcd'


As an example, using one of my test DBs, if I do a query of

select * from store where store_code like '%2038%'

this returns me store with store code 2038 (as well as stores with codes 12038 and 20389)

Former Member
0 Kudos

A Well maybe not then...

Former Member
0 Kudos

Hi Yow,

if you don't want to add the '%' in Powerbuilder:

Your first try should work for Oracle. (e.g. works for me).

What's your DB?

Former Member
0 Kudos

It is MSSQL

Former Member
0 Kudos

SQL Server 2008

Former Member
0 Kudos

Hello Yow,

Append the '%' in PowerBuilder before the retrieve...

String                      ls_arg

ls_arg                      = 'value' + '%'

dataWindow.Retrieve(ls_arg)

your where clause looks like: where col_name LIKE :arg_1

Former Member
0 Kudos

We have always added the "%" to the string before passing the argument.

So string contains "%mytext%"

where clause is colname like :as_arg

Hope that helps.