coalesce view error

From: mikeo <mikeo(at)spectrumtelecorp(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: coalesce view error
Date: 2000-06-15 20:48:37
Message-ID: 3.0.1.32.20000615164837.00a92100@pop.spectrumtelecorp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-patches

hi, i have this view defined as:

create view mikeotest as
select
coalesce(topflow_application.rpt_name,topflow_application.tfap_name) AS
ipd_desc,
graphics_03.ipd_date,
graphics_03.day,
graphics_03.gr_bill_amt_total,
graphics_03.gr_byte_qty_total,
graphics_03.gr_ipd_sessions
FROM graphics_03, topflow_application
WHERE (graphics_03.ct_key = topflow_application.ib_ct_key
or graphics_03.ct_key = topflow_application.ob_ct_key);

which postgres generates this from:

SELECT CASE WHEN (topflow_application.rpt_name NOTNULL)
THEN topflow_application.rpt_name
WHEN (topflow_application.tfap_name NOTNULL)
THEN topflow_application.tfap_name
ELSE NULL::unknown
END AS ipd_desc,
graphics_03.ipd_date, graphics_03."day",
graphics_03.gr_bill_amt_total,
graphics_03.gr_byte_qty_total, graphics_03.gr_ipd_sessions
FROM graphics_03, topflow_application
WHERE ((graphics_03.ct_key = topflow_application.ib_ct_key)
OR (graphics_03.ct_key = topflow_application.ob_ct_key));

when i try to run this query against this view, i get the following:

select sum(gr_bill_amt_total), ipd_desc from mikeotest group by ipd_desc;
ERROR: Unable to identify an equality operator for type 'unknown'

i've played with the postgres definition changing NULL::unknown to 'stuff'
and other variations without success. any help would be appreciated.

the underlying table looks like this and has 2200+/- rows in it:

Table "graphics_03"
Attribute | Type | Modifier
-------------------+-------------+----------
ct_key | float8 | not null
ipd_date | date | not null
day | varchar(10) | not null
ipd_sessions | float8 |
ipd_bill_amt | float8 |
ipd_byte_qty | float8 |
gr_ipd_sessions | float8 |
gr_bill_amt_total | float8 |
gr_byte_qty_total | float8 |

thanks,

mikeo

Responses

Browse pgsql-general by date

  From Date Subject
Next Message W. van den Akker 2000-06-15 22:09:32 Locked record.
Previous Message W. van den Akker 2000-06-15 20:22:52 Error-message in other language

Browse pgsql-patches by date

  From Date Subject
Next Message Hiroshi Inoue 2000-06-15 21:48:21 RE: Big 7.1 open items
Previous Message Bruce Momjian 2000-06-15 19:35:45 Re: Big 7.1 open items