I need to join many tables with left or outer views. Unfortunatly I had to do it by code. All attempts to save this view in view designer will tend to the error message;
some of the columns in query is not found.
For example
SELECT Tst.*, Val.kv, Val_a.kv;
FROM test!tst LEFT OUTER JOIN test!val Val_a;
LEFT OUTER JOIN test!val ;
ON Tst.val_id = Val.valuta_id ;
ON Tst.valuta_id = Val_a.valuta_id


attempt to save it will lead to error column valuta_id is not found, the view is destroyed after it and can not be opened.
All table relations will desappere, all fields
out
puts will desappeare.
I never can sleep if I don't understand something. I opened help and looked sql sysnax many hours. At last I understand, that help tells something different, from the code view designer generated for me
Try to open it again.
Try to create the same view programmaticaly , only changing line
orderds

create sql view view1 as ;
SELECT Tst.*, Val.kv, Val_a.kv;
FROM test!tst LEFT OUTER JOIN test!val Val_a;
ON Tst.val_id = Val.valuta_id ;
LEFT OUTER JOIN test!val ;
ON Tst.valuta_id = Val_a.valuta_id

without opening view in view designer-query this code produce right view , which can be opened ( without destruction, I guess, view should do anything usefull, but not only can be created and this is all, or I am not correct?) later.
I guess, view designer generates wrong code
Nezt trouble, I solved only partionally.
If you to add one extra table to the view above ( create yourself any extra
table and
add this to view) to this view with where clause, not with join,
you also will see the query gives wrong results!!!!
something like
create sql view view1 as ;
SELECT Tst.*, Val.kv, val.valuta_id as idd, Val_a.kv, val_a.valuta_id as idd1;
FROM test.mytable ,test!tst LEFT OUTER JOIN test!val Val_a;
ON Tst.val_id = Val.valuta_id ;
LEFT OUTER JOIN test!val ;
ON Tst.valuta_id = Val_a.valuta_id ;
where mytable.id=tst.id


This is different bug.
It will give different valuta_id from ts and valuta tables.
I was able only to write two different queries, one with where and one with left outer join and save them like view, which work great.
Who is guilty, obviously me, who does not know new sql sysntax and can not read it from manual and help and thought view designer knows it better and will create me correct syntax. But it did not.
Thanks in advance, Vladimir Zhuravlev