Message-ID: <42DD6626.8010705@travelamericas.com>
Date: Tue, 19 Jul 2005 13:44:22 -0700
From: Chris Travers <chris@travelamericas.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Coalesce() in outer join between views
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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