on 07-29-2016 10:19 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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é
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 )
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.
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
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.
Check out:
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.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
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)
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.