[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sapdb-general
Subject:    AW: like (:code||'%') in DBPROC gets wrong result
From:       "Zabach, Elke" <elke.zabach () sap ! com>
Date:       2005-06-30 11:28:29
Message-ID: 235EE6AC8AE9264CBF763FC6749E27010270B79A () dewdfe22 ! wdf ! sap ! corp
[Download RAW message or body]

Alexey Gaidukov wrote:
> 
> These two procedures get different results.
> 
> No resultset. Wrong result
> 
> CREATE DBPROC REG.GetAllTowns(IN code varchar(3))
> RETURNS CURSOR AS
> BEGIN
>     DECLARE :$CURSOR CURSOR FOR
>         SELECT TOWN,CODE, DISTRICT FROM gis.all_towns where code like
> (:code||'%') order by town;
> END;
> 
> 
This is no special behavior of dbprocs. Even the pure select will not
return a result in case the value of :code contains leading blanks. This
leading-blank-topic is a topic we know about and we will change. No time
schedule.

To have the behaviour you want use
(TRIM(:code)||'%')   or the second variant you found.

Elke
SAP Labs Berlin

> Correct variant
> 
> 
> CREATE DBPROC REG.GetAllTowns(IN code varchar(3))
> RETURNS CURSOR AS
> VAR
>     t varchar(4);
> BEGIN
>     SET t = code||'%';
>     DECLARE :$CURSOR CURSOR FOR
>         SELECT TOWN,CODE, DISTRICT FROM gis.all_towns where code like
:t
> order by town;
> END;
> 
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/maxdb?unsub=elke.zabach@sap.com


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=sapdb-general@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]