Обсуждение: Problem with Subquery
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
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: > 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 >
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