COBOL SQL not giving results expected

[TonyG]

When i use the following SQL statement with START-REG-NO = "1 " and END-REG-NO = "2 ",
then when i do the FETCH, i get all of the EVENT_ID's in the PS_RGY table including event id "100000 ".
Even if i replace START-REG-NO and END-REG-NO
with a hard coded "1 " and "2 ", i get the same thing.

I took it thru the debugger and it shows the fields in hex, as they should be.

I can't figure this out. Using Fujitsu Net COBOL for Windows version 7.0L10.

Any help would be greatly appreciated.

Thanks,
Tony

EXEC SQL
DECLARE CUR1 CURSOR FOR SELECT
TYP,
EVENT_ID,
EVENT_DESC,
EVENT_DAT,
LST_PURCH_DAT,
BRIDE_1ST_NAM,
BRIDE_LST_NAM, BRIDE_ADRS_1, BRIDE_ADRS_2,
BRIDE_CITY, BRIDE_STATE, BRIDE_ZIP_COD,
BRIDE_PHONE_NO_1, BRIDE_PHONE_NO_2,
GROOM_1ST_NAM, GROOM_LST_NAM,GROOM_ADRS_1,
GROOM_ADRS_2, GROOM_CITY, GROOM_STATE,
GROOM_ZIP_COD, GROOM_PHONE_NO_1,
GROOM_PHONE_NO_2, COMMNT, STAT, NO_ITEMS_REG
FROM PS_RGY
WHERE TYP = 'H' AND
EVENT_ID BETWEEN :START-REG-NO AND :END-REG-NO
ORDER BY EVENT_ID
END-EXEC.

 

[TonyJollans]

Hi Tony,

Well, "100000 " is between "1 " and "2 ".

Your strings are not numbers, so normal alphanumeric comparison rules should apply.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TonyG]

Hi Tony.

Thanks for your reply.

I'm sorry, but i can't see how what you are saying is true.

If you convert these fields to hex:
"100000 " = 313030303030202020202020
"1 " = 312020202020202020202020
"2 " = 322020202020202020202020

There is no way that a '30' is between the two '20''s

Thanks again
Tony

 

[fredericofonseca]

In all implementations I know of a "SPACE" is less than any number or letter.
This means that the following is a correct sort order.
"1 "
"1 1 2"
"1 111"
"11111"
"2 "

So on the above data a select between "1 " and "2 " would retrieve all the 5 records.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[TonyJollans]

Hi Tony,

The first character is more significant than the second in the comparison, so the fact that, as you say, There is no way that a '30' is between the two '20''s is not the relevant one - '3130' does come between '3120' and '3220'

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TonyG]

Boy what a dope i am.

Thanks guys for all your patience, understanding and help.

Tony