Following different select variations returns selectempty


sSystemActivity.IN is set to a value.For eg "010101 "
act = sSystemActivity.IN(1;2)

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :act
==============================
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :1
wherebind(1, act)
=====================================
select tpppc215.*
from tpppc215
where (tpppc215._index6 = {:sProject.IN})
AND (tpppc215.cact(1;2) = :1)
wherebind(1, sSystemActivity.IN(1;2))
======================================
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN }
and (:1 = :act)
wherebind(1,tpppc215(1;2))
=========================================
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN }
and (:1 = :2)
wherebind(1,tpppc215(1;2))
wherebind(2,act)
===========================================



It will work well if the value is hardcoded, or Substring is not used on both side of '=' sign
Following cases works

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = "01"
=================================
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact = :1 |* System Level Activity
wherebind(1, "010101 ")
================================

We can use 'LIKE' operator instead of wherebind logic. But felt that 'wherebind' would give us better performance than 'Like' .

Anybody with hint or idea.

Thanks and Regards,
Dilip

Following is the situation in Baan IV. Think the same situation in Baan V too.

1. Substring don't work SQL statements
2. Like can be used with hard coded values only . For general values , dynamic SQL may used. Think u r using olesql . There also the same should apply.

So How do i achieve the objective of selecting a project with first two chars of activity equals to first two chars of a variable ?. What are the altenatives for above mentioned non-working SQLS. I am surprised, they compiles well but did not work as intended.??

Anybody knows how to find out the Oracle level SQLs of above Baan SQLs, by putting TRACE or DBSLOG. ?

Thanks
Dilip

here is an example that works:
select tdpur045.*
from tdpur045
where tdpur045._index1 = {:tdpur041.orno, :tdpur041.pono}
and tdpur045.spur = 9
and :1 <> "e"
wherebind(1, tdpur045.pmsk(stno.4223;1))
selectdo
osta.del_lines = true
endselect


So I think you need to do this:
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and 1 = "01"
wherebind(1, sSystemActivity.IN(1;2))

A clarification is required. Substrings does not work only for variables in SQLs . For string table fields the substrings work.

If u see ur case , it works well with table field .

All u need is to declare a string with 2 characters and use it in the SQL. like


declaration:
extern string tstr(2)


tstr = "01"
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :tstr


Have u declared variable as extern ?

Hitesh,
I tried with declaring extern variable, But still the same problem.


Nick,
It works while hardcoding the values in where clause.
For eg:

Following select works

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and 1 = "01"
wherebind(1, tpppc215.cact(1;2))
--------------------------------------------------------------------------
but Following ARE NOT working

extern string tstr(2) --(putting extern as suggested by Hitesh)

tstr = "01"

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and 1 = :tstr
wherebind(1, tpppc215.cact(1;2))
OR
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :tstr
OR
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :1
wherebind(1, tstr)
--------------------------------------------------------

Can this be a bug in Baan toolset ?

Thanks
Dilip

Originally posted by dilip_mehta
Following different select variations returns selectempty


sSystemActivity.IN is set to a value.For eg "010101 "
act = sSystemActivity.IN(1;2)

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :act
==============================
select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = :1
wherebind(1, act)

[snip]

It will work well if the value is hardcoded, or Substring is not used on both side of '=' sign
Following cases works

select tpppc215.*
from tpppc215
where tpppc215._index6 = {:sProject.IN}
and tpppc215.cact(1;2) = "01"

[snip]

We can use 'LIKE' operator instead of wherebind logic. But felt that 'wherebind' would give us better performance than 'Like' .

Anybody with hint or idea.

Thanks and Regards,
Dilip

If the two failing examples also fail when 'act' is declared extern, then this looks like a bug. I don't know on which Baan release and which database you are, but I understood that this is possibly solved in the latest Porting Set.

What probably happens is that in "tpppc215.cact(1;2) = :1" the right-hand side of the comparison is internally padded with blanks, which causes the comparison fail always.

If this still doesn't work with the latest Porting Set, the only workaround I see is to use a dynamic query (i.e., calling sql.parse() etc. yourself) which contains the two-character string hardcoded.

Using the LIKE operator probably will not help, since --as another poster also indicated-- you can only use string literals there, so you also have to use a dynamic query. And LIKE is slower than a substring-comparison.

manix, I think you are right.. seems like a Bug somewhere
I am on baan5b, porting set 6.2a.03.03 .. Databse Oracle 8.0.6.3.0. I just took DBS log..
Here's the output: --As manix said, Internally, Right hand side is padded with Blanks.. I think I should go ahead and create a Case with Baan. Thank you guys for helping me out.

====================================
SQL> SELECT /*+ FIRST_ROWS index(a ttpppc215535$idx1) */ a.t$cprj,a.t$cact FROM baan.ttpppc215535 a WHERE (SUBSTR(a.t$cact,:1,:2)) = :3 AND a.t$cprj = :4
qdb_open_select_cursor done.
oracle_prepare_cursor.
SQL> SELECT /*+ FIRST_ROWS index(a ttpppc215535$idx1) */ a.t$cprj,a.t$cact FROM baan.ttpppc215535 a WHERE (SUBSTR(a.t$cact,:1,:2)) = :3 AND a.t$cprj = :4
ora_bind 0x20727268 : nr 1, addr 0x20a62f8c, size 4, ext_type 3, mode 0, indp 0x20a62f84
ora_bind 0x20727268 : nr 2, addr 0x20a62f90, size 4, ext_type 3, mode 0, indp 0x20a62f86
ora_bind 0x20727268 : nr 3, addr 0x20a62f94, size 255, ext_type 96, mode 0, indp 0x20a62f88
ora_bind 0x20727268 : nr 4, addr 0x20a63093, size 9, ext_type 96, mode 0, indp 0x20a62f8a
ora_define 0x20727268 : nr 1, addr 0x20a630ac, size 9, ext_type 96, ind 0x203955d8, rcode 0x20a63200
ora_define 0x20727268 : nr 2, addr 0x20a63160, size 8, ext_type 96, ind 0x203955d8, rcode 0x20a63228
oracle_prepare_cursor done.
oracle_open_cursor : id 0x20a62e80
oracle_exec_cursor 0x20a62e80
------ QPS Input Row -------
Bind :1 : long : <1>
Bind :2 : long : <2>
Bind :3 : string : ''
Bind :4 : string : '200307948'
----- DBMS Where Input ----
Bind nr 1 : Special : long : <1>
Bind nr 2 : Special : long : <2>
Bind nr 3 : Special : string : '01 ' > 80
Bind nr 4 : cprj : string : '200307948'
SQL> SELECT /*+ FIRST_ROWS index(a ttpppc215535$idx1) */ a.t$cprj,a.t$cact FROM baan.ttpppc215535 a WHERE (SUBSTR(a.t$cact,:1,:2)) = :3 AND a.t$cprj = :4
ora_multi_execute 0x20727268 0x206c153c (do 0, offset 0, timeout 900) done; rows count 0.
oracle_fetch_cursor 0x20a62e80
Rows left in buffer : 0.
Fetching 20 rows from DBMS.
ora_multi_fetch cursor 0x20727268 ( no_rows 20 )
ora_error 1403 dbs_errno 110. end of fetch -> return 110, no_rows 0.
DBMS set exhausted.
Fetched 0 rows from DBMS.
Fetch : end of set.
oracle_break_cursor 0x20a62e80
oracle_break_cursor: cancel
Cursor 0x209e68b0 put in break list [6].

Reply from baan regarding above problem:
===========================
If you are still on 6.2a.03.03, it explains the error. Several substring related problems, including this one, should already be fixed in the newer portingsets (7.1c.03 or later).
===========================

I wonder, is this bug affect somewhere in standard Baan applications?. Its up and running in production.. never heard any complaints from users?