Concern related to SQL Result and access path

tetrion · New User Joined: 22 May 2005 Posts: 8

Hello all,
Please see the scenario below. If i write a query like

SELECT * FROM POPR.VHIS WHERE COMPANY_NO = 4
AND TERMINAL = 1 ;
The result of this query doesn't fetch any rows.

But if i write a SQL like this
SELECT * FROM POPR.VHIS WHERE COMPANY_NO = 4;
will fetch quite a number of records of which some is having a value
TERMINAL = 1.

TERMINAL is a S9(2)V COMP-3 field. Inother words DECIMAL 2.

Does this have anything to do with the access path. If yes, how do i know what went wrong ?

dick scherrer · Posted: Wed May 02, 2007 9:24 pm

Hello,

How are you displaying the "one" after the select for "COMPANY_NO = 4"?

Might it be that the "one" is stored differently than it displays? What is the definition of the TERMINAL field in various places?

Have you tried to change yor query to "TERMINAL < 2"? This would be for testing and not necessarily for long-term use.

mithun123 · New User Joined: 13 Apr 2006 Posts: 11

Hi tetrion,
Can u define a working storage variable as
01 HOST-TERMINAL PIC S9(2)V COMP-3.
pass the value 1 to this variable and use this in the SQL. I believe this would work. Can you please try this and let me know whether this works or not. U can also use the DCLGEN variable for the same . I believe when you are checking with fileaid for the rows that retrieved by the second query the terminal value is shown as '1.' not '1'.

Thanks and Regards,
Mithun