Hi,
I am trying to get list of items with some wildcards in sql query. My requirement is get all items starting with "XB-????". The question marks can be anything, but the resultset should have items of length 7 characters (staring with XB- and anything in question marks). Can someone help me?

I have used tiitm001.item like "PLM7...... .*"
to find all items starting with PLM7, had any 6 additional characters, then a space and any remaining characters. So try "XB-.... .*". I have only done this using EZ-sql, not a Baan Script. Also the space .* was needed to get the rest of the part number. In my case I new it would be spaces since we do not use the space in part numbers.

Good Luck!

Mark

Hi Ravi,

You have to explore the possiblity using the "like" operator and use and "selectbind/wherebind" options.

You might like to refer to the script tsmdm0280, if you have the source scripts.

Hi Mark,
The .... pattern works but it is getting other items which are of pattern XB-ABC. Infact I am also trying in Easy SQL with following select

select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm like " XB-.... *"
and tisfc001.osta = 1


The result is having all items with 4 or less characters after "XB-", but I am looking at exact 4 characters

:-(

regards

Try using: LIKE "XB-[^ ][^ ][^ ][^ ].*".

The '.' represent any single character. By using '[^ ]', this takes any character but a space. You can look at the Baan Programmers Manual for more info on LIKE expressions: http://www.baanboard.com/programmers_manual_baanerp_help_functions_expressions_runtime_expr_compile

Hi Evesely,
I tried your pattern, but I am getting "No Data with in Selection". As of now, I coded in the 3GL script and checking number of character after "XB-" and skipping unmatched records.

regards

Ravi,

Ed's solution should work. Make sure there is a space after the caret "^ ". The non-elegant more effective solution (Lisa / Ed and I were involved in a similar discussion - browse the forum to find that thread) isinrange {"XB- "} and {"XB-ZZZZ"}

I see you have leading spaces in your previous listing. Did you put those in when you tried my code? I.e., LIKE " XB-[^ ][^ ][^ ][^ ].*" I didn't notice them when I first posted. I tried my solution in both EasySQL and in a program script on IVc4, albeit without leading spaces, and it worked. I also tested in a Vc program script (which I suspect is where you are running it because of the leading spaces), and it worked fine.

Ravi,

I think Vamsi's idea is good, but if can try this -

This might not be the best of all the solution...


long found
domain tcitem temp.item

temp.item = "XB-.... *"

select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.osta = 1
and tisfc001.mitm like " XB-.... *"
selectdo
if temp.item = shiftl$(tisfc001.mitm(1;7)) then
found = 1
else
found = 0
endif
selectempty
found = 0
endselect

Ed,
I put the space after "^ " in the first place, still it did not work.
Here is the code


select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm like " XB-[^ ][^ ][^ ][^ ].*"
and tisfc001.osta = 1



Vamsi,
I tried giving inrange, but I got bhsell error message saying that "Type mismatch with combined and non-combined". Here is the code

select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm inrange {" XB- "} and {" XB-ZZZZ"}
and tisfc001.osta = 1


regards

Ravi,

:). Debug the error. I posted it for a index. You are using a field. If you want to do what you are doing, then remove the braces.

Vamsi,
It was my stupidity, I didnt even notice why I am using { for field. I modified the query but still it is showing all lines


select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm inrange " XB- " and " XB-ZZZZ"


regards

Have you tried like like " XB-[^ ][^ ][^ ][^ ]"? Notice I left off the ".*". From you posts it looks like you part numbers are right justified and that ".*" was probably causing the problem.

Mark

Hi Mark,
I tried that and when I run I get "No data within Selection" message.

regards

Mark,

Ravi is on 5c. In BaanERP, the item field is 47 characters long. Baan also introduced the concept of segmentation. With this the usual practice is to reserve the first 9 characters for the project segment. So in case of non-project items, the first 9 characters should be spaces.

Ravi,

Please post the complete query and the resulting dataset. Also indicate which rows you dispute and why.

Guys,
Sorry for the confusion. As Vamsi mentioned we are on Baan Vc and I need 9 spaces to take care of project segment. Here is the complete problem.

I need to select production orders which are created for end items with certain pattern for which I need to add a operation. The pattern for select is XB-XXXX. That means I should select production orders with main item satisfying above pattern. If I have following items

QB-1324-01
QB-235
QB-1353
QB-2353-003
QB-2521
QB-2135
...
...

I should get only

QB-1353
QB-2521
QB-2135

But when I tried all suggested patterns in the SQL, I was getting other items as well. When I tried the latest suggestion from Mark, I got "no data with in selection".



select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm like " QB-[^ ][^ ][^ ][^ ]"


regards

Vamsi,
Thanks for great solution. Everything is in ".". According to regular expressions "." means any character and when "*" is followed it means multiple occurances of any character that comes in ".".

THerefore when we tried " XB-[^ ][^ ][^ ][^ ].*", the query was bringing everything that comes after these four characters. Now Vamsi suggested to put " " (space) instead of "." in the above expression.

THe final code is ...


select tisfc001.pdno, tisfc001.mitm
from tisfc001
where tisfc001.pdno like "4640.*"
and tisfc001.mitm like " XB-[^ ][^ ][^ ][^ ] *"


regards

Hello,
one question, with embedded SQL, are you allow to use variable with "LIKE"?

It gives me an error, I also did use ":"

select tpptc101.cspc:element, tpptc101.cprj:cprj
from tpptc101
where tpptc101.cprj=:tpcnv998.cprj
and tpptc101.cspp=:cspa
and tpptc101.cspc like child


child is my variable, I also tried :child, still gives me error --> syntax error.

thanks

By reading other threads, it seems it is not possible.

I see you asked aboud embedded SQL. However, in dynamic SQL (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_dynamic_sql) it is possible.

- George