>CREATE OR ALTER VIEW SALDO_UMOWY(
> ID_UMOWY,
> SALDO)
>AS
>SELECT faktury.id_umowy AS id_umowy, IIF((SELECT EXTRACT(YEAR FROM
>umowy.data_podpisania) FROM umowy WHERE
>umowy.id_umowy=faktury.id_umowy)<=2010, (SELECT
>SUM(COALESCE(faktury.brutto-umowy.kwota_fn,0)) FROM faktury WHERE
>faktury.id_umowy=faktury.id_umowy), (SELECT
>SUM(COALESCE(faktury.netto-umowy.kwota_fn,0)) FROM faktury WHERE
>faktury.id_umowy=faktury.id_umowy)) AS saldo FROM faktury, umowy
Isn't the select above (if it at all works) just a 'complification' of
SELECT f.id_umowy, SUM(IIF(u.data_podpisania < '1.1.2011', f.brutto,
f.netto)-u.kwota_fn)
FROM faktury f
JOIN umowy u on f.id_umowy = u.id_umowy
GROUP BY 1
>FROM rdb$dependencies d
>LEFT JOIN rdb$relation_fields r ON d.rdb$dependent_name = r.rdb$relation_name
> AND d.rdb$field_name = r.rdb$base_field
>WHERE r.rdb$system_flag = 0
> AND d.rdb$dependent_type = 1 --VIEW
>ORDER BY r.rdb$field_position
>
>The query returns information about the fields that are in the view
>but returns only the fields that are in the table,
>when the field is a function that is not returned as a change to be
>returned or fields that are a function of how SALDO
I'm not certain whether I understand your question or not, but if you're asking
why your query doesn't return information about SALDO, then my answer is that
it is because SALDO is not in rdb$dependencies and that you should rewrite your
from clause to something like:
FROM rdb$relations r
JOIN rdb$relation_fields rf on r.rdb$relation_name = rf.rdb$relation_name
JOIN rdb$fields c ON rf.rdb$field_source = c.rdb$field_name
LEFT JOIN rdb$dependencies d ON d.rdb$dependent_name = r.rdb$relation_name
AND d.rdb$field_name = rf.rdb$base_field
WHERE r.rdb$relation_type = 1
ORDER BY rf.rdb$field_position
HTH,
Set
