Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi

Read-Only Read-Only Topic
Go
Search
Notify
Tools
LIKE '000%'
 Login/Join
 
Member
posted
Can someone explain this behavior to me; If I execute a statement containing; xxxxxx LIKE '006%', all records like 0061234, 0065432, etc. are returned. If I execute a statement containing; xxxxxx LIKE '000%', any and all records could be returned. I know Focus treats zeros in an interesting way, but this behavior is confusing.

Allen
 
Posts: 28 | Location: New Jersey | Registered: July 11, 2005Report This Post
Virtuoso
posted Hide Post
I can't say as I've ever used the LIKE command, however, to me LIKE '000%' would return an alpha fields values where the first three digits are zeros. What happens if no quotes aroung 000%.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Master
posted Hide Post
LIKE operator works similar like SQL query. It will return the records that consists those characters.

For ex: if you give LIKE '000%'

output: 0001
0002
....000xxxxxxxxxx

% - zero or more characters.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Member
posted Hide Post
Kamesh,
I understand what you are saying and I have used it in SQL with the results as mentioned, however the results that I am getting were listed in my initial message. And that is why I am confused.
Leah to answer your ?, I have not attempted it without quotes, I will.

Allen
 
Posts: 28 | Location: New Jersey | Registered: July 11, 2005Report This Post
Silver Member
posted Hide Post
I think the key here is what Kamesh said --- "% - zero or more characters". So what '000%' matches is any string that starts with '00' followed by zero or more '0's, which is in effect any string starting with 2 zero's.

It kind of looks like the examples you gave all start with 2 zero's, no?
 
Posts: 40 | Registered: March 10, 2004Report This Post
Expert
posted Hide Post
As Allen originally posted:
quote:
If I execute a statement containing; xxxxxx LIKE '000%', any and all records could be returned.
What should be happening is that only records that begin with 000 should be returned.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
You are correct Francis. I was confusing regular expressions with SQL syntax. "%" in SQL means zero or more of any character, not zero or more of the previous character.

Allen, if you are not getting what you are expecting, you may need to save a TRACE file to find out exactly what the SQL statement that is being generated looks like (assuming this is being passed off to a SQL compatible database engine). I believe it is something like 'FILEDEF FSTRACE4 DISK tracefile' that will cause it to be generated and placed into the file name you provide in place of 'tracefile'.

Good Luck
 
Posts: 40 | Registered: March 10, 2004Report This Post
Platinum Member
posted Hide Post
I'd be curious to see what is getting generated in the SQL. I

n the mean time can you get around it by coding WHERE EDIT(fieldname,'999') EQ '000'? Does this result in the same problem? Not sure what RDBMS you are accessing but I think they generate different SQL. I think the EDIT syntax results in SQL that uses the SUBSTR instead of LIKE.
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
Member
posted Hide Post
Thanks, I will give everything mentioned a shot. By the way the RDBMS that is being used here is MySQL.
 
Posts: 28 | Location: New Jersey | Registered: July 11, 2005Report This Post
<toby mills>
posted
Hey Allen - post a little of your code for us to see...

I don't know what platform / release you're on... If you're on a recent version of webfocus, you can use the following statements to direct the output to your browser session:

SET TRACEON = STMTRACE//CLIENT
SET TRACEUSER = CLIENT

Just do a 'view source' and scroll down to the bottom and you should see your generated SQL.

Last thing, and this is going to sound odd to a bunch of you guys. And its probably not your situation, but here goes:

Is the field mentioned in this WHERE test in a table that you are JOINed to? Do you have SET ALL=PASS and are there NO records that start with three 0's in the file? I've seen this happen a long time ago. felt like a bug in the SET ALL=PASS sort of logic at the time (SET ALL=PASS was not actually supported for the SQL interface at the time).

The way ALL=PASS worked then was, as long as you got at least one record that passed your where test, you'd get the results you expect. If your where test was too restrictive and shouldn't return any records, you'd get everything instead of nothing.

Nifty, right?

Probably not your issue as this happened a long time ago (probably in 92 or 93 on MVS against DB2).

Let us see your code, I bet the answer is fairly simple.

-Toby
 
Report This Post
Member
posted Hide Post
Hi
There was additional code that I found in the app that needed modifying; there was a compare of 0 to ' ', which was causing issues as well. I did some hacking and now all is well. Thanks for all of the suggestions.

Allen
 
Posts: 28 | Location: New Jersey | Registered: July 11, 2005Report This Post
Guru
posted Hide Post
If you are having a problem with zero and space you may want to try using the function ASIS().
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Member
posted Hide Post
Thanks, that function is good to have in my toolbox.

Allen
 
Posts: 28 | Location: New Jersey | Registered: July 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders