Wild Char for a Single Character Position

Zoltan_Gyimesi

Legendary Poster
Please, do not laugh me and my issue that I do not know the answer after I was working with OneWorld more years long :)

Is there (and if there is then which is) a wild card character for a single character position, to use it in QBE columns and filter fields on forms (like "?" question mark is in many systems)?

Currently I was unable to locate it in the documentations and helps but if my memory serves me well then I have read about it somewhere somtimes ago, maybe "%" percent sign was it. I have tried it in QBE and noticed that OneWorld handles it very strangely.

Any enlightening will be greatly appreciated!

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Yes, you can use % as a wild card in ERs, but in a single CHAR case I think that would be just blank. For example, if you have Fetch Single against table with keys AN8, CO, and EV01 where EV01 would be something like "Dues paid: Y/N", map only AN8 and CO, so you get all records for particular AN8 and CO, no matter what you have in EV01.

Ainars

Ainars
 

Christian Audet

Moderator
Staff member
Zoltan,

I don't know for * but if you have a list of caracter it would be [].

Example in QBE : "ZOL[A,B,T]AN" and that will find your name. the [] let you give a list of possible caracters. I still don`t know how to do "ZOL[*]AN"

Try the first one it's working and tell me if you find a way for the *.

Thank

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 

Zoltan_Gyimesi

Legendary Poster
Hi Ainars,

Thanks for your reply. Let me some question to you and a bit clarification of my issue.

1.) You have written "Yes, you can use % as a wild card in ERs". Would you be so kind as to tell me where and how?

2.) Your FetchSingle example was trivial for me (and not this is my issue) but thank you anyway.

3.) My issue is "Can the USER use wild-card character for a single character position in Query By Example field in the Grid Header column on a Form in an Interactive Application. Here is an example:
Entering
BICYCLE2%%MB
into the QBE field to find
BICYCLE200MB
BICYCLE299MB
BICYCLE21WMB
etc.
but not to find
BICYCLE20MB
BICYCLE2000MB
BICYCLE2MB

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Zoltan_Gyimesi

Legendary Poster
Hi Christian,

Thanks for your reply.
Have you meant the QBE as Query By Example of Grid of Form of Interactive Application of OneWorld?

I have tried your suggestion with [ ] and list of characters and asterisk too, with and without commas but it doesn't work for me (this time on XE SP 13). Does it really work on your site? Have you tried it?

On the other hand it couldn't be a really solution because sometimes I have to enter longer string into the field than it fits in it.

Regards,
Zoltán
P.S.: Please, see my other reply to Ainars where I tried to explain what is my real problem.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
ER example:
var1 = BICYCLE2%
fetch single table with key = var1
(you can test it with SQL, putting word "like" insead of = )

I don't think you can put Wild card in the middle, like 123%g, it has to be at the end, like 123% or at the beginning, like g.

Sorry for the examples, I didn't put much effort into coming up with better ones.


Ainars
 

Christian Audet

Moderator
Staff member
Zoltan,

Yes the QBE line over the grid column. Maybe I was wrong with the example, try this one, I just tested it under Demo Xe and Live B733.2 (SQL): in P0006 column Description type F[i,r]* and you will find
Financial ...
French...
France...
Fill ...

But it does not seem to work on every field.

If it's still not working, maybe it's database specific.

The me if it's now working in P0006

Christian Audet


Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 

Emad_S_Banoub33

Active Member
The only wild card available in QBE is '*':

- ab* to give you a list of values start with ab
or
- *ab* to give you a list of values contains ab

if you replaced '*' with '%' you will get the same results.

Emad Banoub
Montgomery Watson
Pasadena, CA
626-568-6529
A7.3 CU 12/X3 - Xe SP 13.0




Zoltan_Gyimesi <[email protected]>@jdelist.com on 06/15/2001
11:15:20 AM
 
[x,x] doesn't work for me either, so I guess it is database specific, also I don't have SQL client installed on my machine, that also might be the case. I am on AS/400, DB2

Ainars
 

Zoltan_Gyimesi

Legendary Poster
Hi Christian,

You have a hit.

I tried you example exactly and it doesn't work on B7331/Oracle and works on XE/SQL.

I followed with some experiment on the XE/SQL:

1.) On the same place:
Frame Line [1,2]
doesn't work but
Frame Line [1,2]*
works to retrieve
Frame Line 1
Frame Line 2

2.) In the Description field of P92001 (Data Dictionary):
Math Numeric - length [2,3] decimal 0
does not work to retrieve
Math Numeric - length 2 decimal 0
Math Numeric - length 3 decimal 0
but
Math Numeric - length [2,3]*
works but retrieves:
Math Numeric - length 29 decimal 9
too.

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Christian Audet

Moderator
Staff member
Zoltan,

Try this with your last test in P92001 : "Math Numeric - length [2,3] decimal 0*"

It does work to retreive :
Math Numeric - length 2 decimal 0
Math Numeric - length 3 decimal 0
(Only)

Christian


Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 

Zoltan_Gyimesi

Legendary Poster
Hi Christian,

Yes, it works!

Who can understand the logic of this mysterious * asterisk (or Asterix :) combined with [x,y,z].

On the other hand, the comma is not required in the list. I suppose, the engine interprete this also as a character not as a separator.
For example [x,y] is interpreted as a list with 3 character x and y and , (comma).

Regards,
Zoltán
P.S.: Could it really be possible that USER can not use a single character wild card in OneWorld?

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

BBushley

Active Member
I just tested the QBE in the submitted jobs application panel and I found
that the underscore character is a place holder for the wildcard ie:
BICYCLE2__MB

Bob Bushley
Haskel International
Burbank, Ca. U.S.A.

JDE XE(B7333)ver SP13
AS400 ver V4R5
DB2
Windows 2000
 

Christian Audet

Moderator
Staff member
Re: RE: Wild Char for a Single Character Position

Bob,

Thank you I tried you trick with the "_" and it's working replacing a single caracter.

Zoltan ! that's a great news for you ! OneWorld is not that limited for USER selection in QBE.

Christian Audet


Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 

Zoltan_Gyimesi

Legendary Poster
Re: RE: Wild Char for a Single Character Position

Hi Bob,

Thanks a lot for your suggestion.
I have tested it on the Description field on the Work With Data Items form.

It works for me ONLY when I place an asterisk onto the beginning or onto the end of the string BUT then greatly. It exactly handles when I use it on one or two positons (two underscore retrieve 15 but not 2 and 3) or in other place of the string.
My test were made on the "Math Numeric - length * decimal *" type of description.

I have tested it on B7331/Oracle and XE/SQL and it works on BOTH!

Thanks again,

Zoltán
P.S.: Bob, does it work for you without using asterisk?

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Zoltan_Gyimesi

Legendary Poster
Re: RE: Wild Char for a Single Character Position

Hi Bob and Forum / List,

As I have mentioned in my previous reply to Bob, I have to place an asterisk to the front or to the end of the string make the underscore working as a single character wild-card. It has a side effect now.
1st example:
============
ABC_*
will retrieve
ABC1
ABC2
but will
ABC12
ABC123
too what is not required
and will not
XABC1
XYABC2
what ist OK.

2st example:
============
*ABC_
will retrieve
ABC1
ABC2
but will
XABC1
XYABC1
too what is not required
and will not
ABC12
ABC123
what ist OK.

Any further suggestion (like Bob's great one)?

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Christian Audet

Moderator
Staff member
Re: RE: Wild Char for a Single Character Position

Zoltan,

I finaly found the reason why !

To answer you question about the *, with the help of the log I was able to find out.

When using the * the SQL translator is using "LIKE" instead of "=" and then all caracters you are using in your query is transfer to the LIKE clause.

T1.FRDSCA = '1 or _ Phase Picking' (without the *)
T1.FRDSCA LIKE '1 or _ Phase Picking%' (with the *)

Look at this text from SQL Server Help.

LIKE Operator
=============================


Wildcard character Description Example
% Any string of zero or more characters. WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title.

_ (underscore) Any single character. WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).

[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.

[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE ‘de[^l]%’ all author last names beginning with de and where the following letter is not l.
escape_character

Is any valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character.

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Top