From: | "Marc Polatschek" <Marc(dot)Polatschek(at)computec(dot)de> |
---|---|
To: | |
Cc: | "postgreSQL [GENERAL] (E-Mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problem with Subquery |
Date: | 2002-03-08 08:58:52 |
Message-ID: | 2266D0630E43BB4290742247C89105758DCAE5@dozer.computec.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks for help but im a complete idiot ;-)
DEVELOPER_ID and PUBLISHER_ID are VARCHAR-Datatypes so the error message
is 100% correct.
-----Ursprüngliche Nachricht-----
Von: Joel Burton [mailto:joel(at)joelburton(dot)com]
Gesendet: Donnerstag, 7. März 2002 20:30
An: Marc Polatschek
Cc: postgreSQL [GENERAL] (E-Mail)
Betreff: Re: [GENERAL] Problem with Subquery
On Thu, 7 Mar 2002, Marc Polatschek wrote:
> We recently changed our database system from Oracle 8i to postgreSQL.
Im
> right now changing our cold fusion code and i have get this problem:
>
> select ID,
> NAME,
> WEBSITE_URL,
> (
> select count(*)
> from MAIN_PC_GAME,
> MAIN_COMPANY
> where DEVELOPER_ID = MAIN_COMPANY.ID
> or PUBLISHER_ID = MAIN_COMPANY.ID
> ) as TOTAL
> from MAIN_COMPANY
> where (
> lower(SOFTWARE_DEVELOPER)='yes'
> or lower(SOFTWARE_PUBLISHER)='yes'
> )
>
> This SQL statement isnt working with postgreSQL but it worked without
> any problems in Oracle. Postgres Error Message:
>
> Unable to identify an operator '=' for types 'character varying'
> and 'numeric'
> You will have to retype this query using an explicit cast
>
> I think postgreSQL cant handle the variable MAIN_COMPANY.ID from the
> parent Scope. Probably there is a way to mark this variable to find it
> in the caller.scope. But i dont know how.
Are you sure that both DEVELOPER_ID/PUBLISHER_ID and MAIN_COMPANY.ID are
both numeric? Is one a string?
If so, cast the numeric into an integer/float. Example
CREATE TABLE test (id numeric, str varchar(5));
INSERT INTO test VALUES (1,'1');
SELECT FROM test WHERE id=str; <- same error as you're getting
SELECT FROM test WHERE id::int=str; <- works
--
Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim:
wjoelburton
Independent Knowledge Management Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | rolf.ostvik | 2002-03-08 09:40:47 | Re: PL/pgSQL Syntax Problem |
Previous Message | Frank_Lupo Frank_Lupo | 2002-03-08 07:57:08 | super question??? |