Hi Meena!

The result set doesn't depend on which table the optimizer chooses to
take first when preparing, your problem is a different one:

FLOAT is an approximate data type that doesn't hold exact values and
shouldn't be used for primary keys or linking tables at all. Change to
INTEGER, NUMERIC or DECIMAL which are fixed decimal types. Then Firebird
will get the result you expect.

Rewriting Helen's rule of thumb: You may use floating point types for
things you measure, use fixed point for things you count and any other
number.

If you insist on keeping FLOAT, you have to take into account that the
values are approximate and do something like

select ss.yrc, ss.stoseg, m.co_name
from seg_scr ss
join master m
on ss.co_code between m.co_code - 0.001 and m.co_code + 0.001
order by m.co_name

(rewritten from SQL-89 to SQL-92, add more 0's if necessary)

HTH,
Set

Meena_Sethu wrote:

> Hi All,
>
> I am new to Firebird.I have my Access database ported to firebird 2.0
> release. I have a master file with the following structure
>
> SC_CODE VARCHAR(6),
> CO_CODE FLOAT NOT NULL,
> CO_NAME VARCHAR(16),
> LNAME VARCHAR(50),
> CM_SYMBOL VARCHAR(16),
> ALIASMEMO BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> IND_L_NAME VARCHAR(50),
> IND_S_NAME VARCHAR(16),
>
> with Co_Code as the primary key
> And the transaction file with the following structure
>
> CO_CODE FLOAT NOT NULL,
> YRC FLOAT NOT NULL,
> SGTYPE FLOAT NOT NULL,
> NOM FLOAT,
> SRNO FLOAT NOT NULL,
> STOPRC VARCHAR(8),
> STOPRN VARCHAR(35),
> STOSEG VARCHAR(35),
> STO FLOAT,
> ISR FLOAT,
> NOTE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> "YEAR" FLOAT,
> FLAG VARCHAR(1)
>
> with CO_CODE,YRC,SGTYPE,SRNO as the primary keys
>
> when I run the following query
> 'select yrc,stoseg,co_name from seg_scr,master where seg_scr.co_code
> = master.co_code order by co_name'
>
> In MS Access the index of the transaction table is considered first
> and then of the master table,
> But in Firebird the smallest index is considered first and then the
> index with two or more fields.
> As a result the output in both differ
> Is there a method in which i could specify which index has to be
> considered first.
> Please help
>
> Thanks in advance
> Meena