Re: Problem with Subquery

Could you send the schema for the tables

From what the error message is saying you have a numeric field and are
trying to say that it equals a varchar field

Darren Ferguson

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.
>
> Marc Polatschek
> Head of Development
> COMPUTEC MEDIA AG
> Dr.-Mack-Strae 77
> D-90762 Frth
> phone: +49 (0) 911 2872 - 106
> fax: +49 (0) 911 2872 - 200
> mail: marc(dot)polatschek(at)computec(dot)de
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>