Strange error while working with derived table

Hi,

Working on postgres8.1 in PHP, I came in the situation I needed to
create a derived table.

Consider the following query:

SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark' AS zoekwoord) UNION (SELECT 'test' AS zoekwoord)) AS
tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');

The part with the UNION simple fills tempwordstable.
This query runs fine.

However, if I have only 1 in tempwordstable (so no UNION), like this:

SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark' AS zoekwoord) ) AS tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');

I get the error:
ERROR:  failed to find conversion function from "unknown" to text

If I DO cast 'mark' to TEXT like:

SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark'::TEXT AS zoekwoord) ) AS tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');

all works fine.


I understand the ::TEXT part. Since I fill this 'table' on the fly, I
should tell WHAT I am using.
What I don't understand is why Postgres8.1 need the cast to TEXT for 1
entry in my derived table, and NOT when I use more (using UNION)??

Any clues much appriciated. :-)

Regards and TIA,
Erwin Moller

--



-------------------
Erwin Moller
Darwine BV

Groenendaal 25f
3011 SK Rotterdam
tel 010-2133996
-------------------