Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with Subquery

Problem with Subquery

From
"Marc Polatschek"
Date:
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-Straße 77
        D-90762 Fürth
        phone: +49 (0) 911 2872 - 106
        fax: +49 (0) 911 2872 - 200
        mail: marc.polatschek@computec.de

Re: Problem with Subquery

From
Joel Burton
Date:
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@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

Re: Problem with Subquery

From
Stephan Szabo
Date:
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

It'd help if you sent the schema for the tables involved.  Are the ids
of differing types?

Re: Problem with Subquery

From
Darren Ferguson
Date:
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-Stra�e 77
>         D-90762 F�rth
>         phone: +49 (0) 911 2872 - 106
>         fax: +49 (0) 911 2872 - 200
>         mail: marc.polatschek@computec.de
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: Problem with Subquery

From
"Marc Polatschek"
Date:
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@joelburton.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@joelburton.com  |  joelburton.com  |  aim:
wjoelburton
Independent Knowledge Management Consultant