From: Chris Travers <chris@travelamericas.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Coalesce() in outer join between views
Date: Tue, 19 Jul 2005 13:44:22 -0700
Message-ID: <42DD6626.8010705@travelamericas.com> (raw)
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
UNION
SELECT 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)
UNION
SELECT 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chris@travelamericas.com
Subject: Re: Coalesce() in outer join between views
In-Reply-To: <42DD6626.8010705@travelamericas.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox