At 02:27 AM 4/02/2010, tomkrej wrote:

>Hi, I'm using firebird 2.1.3 on Windows.
>
>I have a VIEW that is constructed
>
>CREATE VIEW VIEW_MAIN (
>...
>)
>as
>select distinct
>...
>from MAIN_TABLE
>join SUB_TABLE1 on ...
>left join SUB_TABLE2 on ...
>where MAIN_TABLE.STATUS != 'V'
>
>when I try to sort rows in view it doesnt work, and the row order is allways the same, order by clause is ignored
>
>but, when I remove - DISTINCT, or change join SUB_TABLE1 to LEFT join SUB_TABLE1
>
>view work properly.
>
>Is this behavior OK?? I dont think so.

I see that, in a later posting, you said "In Fb 1.5 it works properly". Well, in Fb 1.5 your syntax "worked" because the absence of properly qualified identifiers for multi-table queries caused a WARNING (ignored by most people), whereas from v.2.0-forward, it causes exceptions.

It is STRONGLY RECOMMENDED to study the release notes when you change to a new version. Implementations don't change from one SUB-release to another (e.g. 1.5.5 to 1.5.6) but they usually change A LOT from one major release to the next.

>I can send the full source code for tests.

Not needed. You just have to fix up your syntax, e.g., for the view definition:

CREATE VIEW VIEW_MAIN (
...
)
as
select distinct
mt.x, mt.y, ...
st1.x, st1.y,...
st2.x, st2.y...
...
from MAIN_TABLE mt
join SUB_TABLE1 st1 on
st1.blah = mt.blah...
left join SUB_TABLE2 st2 on ...
st2.blah......
where mt.STATUS != 'V'

Then, when you invoke the view, e.g.,

select afield1, afield2, ....
from view_main
order by 1, 2, ....

NB, you can use the actual relation names as qualifiers, instead of aliases, if all of the relations are different. If your query accesses the same table more than once, you must use aliases. You cannot mix aliases and relation names.

./heLen