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

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?

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:

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