BEGINS Keyword Confuses

[saravanakumar]

Hi Team,
I am not sure if am missing out something, may be... I have 2 questions;

Question 1:
I have a Division id field which is a character field that would right align if only the numeric characters are available in the input string, others will be left aligned.

The Division Id value is "1" (numeric) and hence it would have been right aligned and stored in DB. Henceforth in our application there is a function that would create an array with size 12 that would substitue the value to the below query as i have done ("1", " 1", " 1", " 1" and so on)

FIND div NO-LOCK WHERE div.custid EQ "X999"
AND (div.id BEGINS "1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1"
OR div.id BEGINS " 1") NO-ERROR.
IF AMBIGUOUS div THEN
DISP "YES".
ELSE
DISP "NO".

Result:
Eventhough a valid division " 1" is available the above query returns "NO" as output. Why?

Whereas if i use FOR..EACH am getting the Division Id...


Question 2:
Consider i have a division "ABC" and another division "ABCD" and if i execute the below query;

FIND div NO-LOCK WHERE div.custid = "X999"
AND div.id BEGINS "ABC" NO-ERROR.
IF AMBIGUOUS div THEN
DISP "YES".
ELSE
DISP "NO".

For the above query i was expecting "YES" as the answer but i got "NO". When i tried displaying the div table details i was able to get the details of div.id "ABC"...

Any suggestions? Thanks in advance!

 

[jmac13]

might sound stupid but the div.custid = "X999 is the same for division "ABCD" ?

 

[saravanakumar]

Yes, div.id = "ABCD" falls under div.custid = "X999"

 

[saravanakumar]

just now noticed one small correction, if i include space it gets trimmed in this forum. should be i dont no to use the proper option to include space. (if someone can help me out in that as well , i will be more happy)

FIND div NO-LOCK WHERE div.custid EQ "X999"
AND (div.id BEGINS "<sp>1"
OR div.id BEGINS "<sp><sp>1"
OR div.id BEGINS "<sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>1"
OR div.id BEGINS "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>1") NO-ERROR.
IF AMBIGUOUS div THEN
DISP "YES".
ELSE
DISP "NO".

where <sp> stands for SPACE...

 

[sdjensen]

Why do you save the extra spaces in front of the 1 in the db?
Would it not be easier just to store the 1 and then use format statement to display the values?

You could also use FIND div NO-LOCK WHERE div.custid EQ "X999" AND (trim(div.id) begins "1") but I think the speed would be the same to get the data from the database.

Answer to question 1/2: it is because of your indexes.

define temp-table div no-undo
field custid as char
field id as char
index p1 is primary id custid /*returns yes*/
index p2 custid id /*returns no */

 

[tamhas]

Please tell me that we are misunderstanding you... I can't really believe that you have leading spaces on identifiers. Really, Don't Do That!™ Do your formatting in output routines. Never, ever, use meaningful leading or trailing spaces in an identifier.

 

[TomBascom]

Quite aside from the formatting problem... why are you testing with AMBIGUOUS? Shouldn't you be testing for AVAILABLE?

 

[saravanakumar]

Hi Team,
This is an 30 year OLD application and through out the application this is how they have followed. There is no possibility for even to think of changing the field formats and so on.

Tom,
Coming back to your question, There are Div Id like "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>1", "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>11" and "<sp><sp><sp><sp><sp><sp><sp><sp><sp><sp>112". The requirement here is that if more than one division id is found that begins with 1 then we may need to list all of them and user would select one among them. Henceforth i have added the ambiguous check (If ambiguous then will trigger the lookup program that lists the division id that begins with 1)

I hope i haven't confused you...

 

[saravanakumar]

Hi Tom,
I hope i got the answer for the ques 2 that i was searching for... when i went through our forum today i found a similar kind of thread with an example...

http://www.progresstalk.com/showthread.php?115018-Begins-begins-behave-diffrently

I hope answers in the thread are correct. If Yes, that is the answer in my case as well. The Database table has an index on custId + division id........................

 

[tamhas]

Not to quibble, but there was no released version of Progress in 1981...

And, there is always a possibility ... it is just a question of expense and effort. In this case, I am sure that it would be less than you would think since really, all one needs is a couple of quick and dirty programs to trim the extra spaces off the data and then to alter the display format in the places where the affected fields are visible.

 

[TomBascom]

If your query returns a unique value AMBIGUOUS will be false. So in the case that you describe you would want to test both AVAILABLE and AMBIGUOUS. Or use FIRST (or CAN-FIND). (This would be one of those rare cases where FIRST makes some sense -- you just want to know if there is at least one record.)

30 years of wrongs does not make a right.