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

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%.

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.

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

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?

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.

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

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.

Thanks, I will give everything mentioned a shot. By the way the RDBMS that is being used here is MySQL.

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

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

If you are having a problem with zero and space you may want to try using the function ASIS().

Thanks, that function is good to have in my toolbox.

Allen