Coalesce() in outer join between views
От | Chris Travers |
---|---|
Тема | Coalesce() in outer join between views |
Дата | |
Msg-id | 42DD6626.8010705@travelamericas.com обсуждение исходный текст |
Ответы |
Re: solved: Coalesce() in outer join between views
(Chris Travers <chris@metatrontech.com>)
|
Список | pgsql-sql |
Hi everyone. I am trying to create a view that fills in missing values from a secondary source. I am using PostgreSQL 8.0.3 on Fedora Linux Core 3. I have two important views and two important tables. Everything works find by itself but when I try to create an outer join between views (that hit the same table) coalesce is giving bad results. The first view is day_source_pre: View "reporting.day_source_pre"Column | Type | Modifiers --------+------------------+-----------day | date |amount | double precision |source | text | View definition: ( SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount, payment_types.id AS source FROM acc_trans, payment_types WHERE (acc_trans.chart_id IN ( SELECT chart.id FROM chart WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~ (('%source='::text || payment_types.id) || '%'::text) GROUP BY acc_trans.transdate, payment_types.id UNIONSELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount, 'over/under' AS source FROM acc_trans WHERE (acc_trans.chart_id IN ( SELECT chart.id FROM chart WHERE chart.accno~~ '1300.%'::text)) AND acc_trans.source ~~ '%Over/under%'::text GROUP BY acc_trans.transdate) UNIONSELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount, 'Reset' AS source FROM acc_trans WHERE (acc_trans.chart_id IN ( SELECT chart.id FROM chart WHERE chart.accno~~ '1300.%'::text)) AND acc_trans.source ~~ '%Reset%'::text GROUP BY acc_trans.transdate; This works as expected by itself. The second view is: View "reporting.day_inc_source"Column | Type | Modifiers --------+------------------+-----------day | date |sum | double precision |source | text | View definition:SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS sum, acc_trans.source FROM acc_trans WHERE acc_trans.source IS NOT NULL GROUP BY acc_trans.transdate, acc_trans.source; This works OK by itself. The third view (which is where tthe problem is) is defined thuswise: View "reporting.day_source"Column | Type | Modifiers --------+------------------+-----------day | date |source | text |amount | double precision | View definition:SELECT day_inc_source."day", day_inc_source.source, COALESCE(day_source_pre.amount, day_inc_source.sum * -1::double precision) AS amount FROM reporting.day_source_pre RIGHT JOIN reporting.day_inc_source ON day_source_pre.amount = day_inc_source.sum AND day_source_pre."day" = day_inc_source."day" WHERE (day_inc_source.source IN ( SELECT payment_types.id FROM payment_types)) ORDER BY day_inc_source."day"; The problem seems to be somehow assuming that all amount columns in day_source_pre are null. Is there something wrong in how this view is working, or is it (more likely) my SQL syntax? That I want to do is fill in a value from day_inc_source if and only if it is not found in day_source_pre with the same date and amount. Best Wishes, Chris Travers Metatron Technology Consulting
В списке pgsql-sql по дате отправления: