Application Development Software

2875841 wrote:
Hi
So how can I write ANSI sql for below query
SELECT w.test_id,
 (SELECT comp_code FROM compnay WHERE compnay_id = W.VALUE
 )
FROM testTable W where w.value !='Any'
Here testTable contains a column "value(VARCHAR)" and it can have both integer and string values.
But company_id is an integer field .Its working when connecting to oracle .But not working in postgresql.(org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = character varying)
Can anyone help me to write this query to execute in all db.
Regards

do NOT rely upon implicit datatype conversion

WHERE TO_CHAR(compnay_id) = W.VALUE

2875841 wrote:
Hi
So how can I write ANSI sql for below query
SELECT w.test_id,
 (SELECT comp_code FROM compnay WHERE compnay_id = W.VALUE
 )
FROM testTable W where w.value !='Any'
Here testTable contains a column "value(VARCHAR)" and it can have both integer and string values.
But company_id is an integer field .Its working when connecting to oracle .But not working in postgresql.(org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = character varying)
Can anyone help me to write this query to execute in all db.
Regards

Shouldn't you be asking this in a postgresql forum?

I'd be surprised if you can come up with a query that will "execute in all db".  Even the ANSI "standard" is not rigorously implemented in "all db".

And have a column that can have both strings and integer values is a fatally flawed design.  In the oracle world (and I suspect in all other db) that means the column has to be defined as some string data type, and then the app has to figure out when to treat it as a string, and when to treat it as an integer.  This strongly smells of an Entity-Attriute-Value design, which someone else posted just yesterday.

Thank you

No,I haven't asked in postgresql.Google redirecting to this page.

My doubt is,is it possible to write same query in the above scenario to execute in all database.

I just would like to know if any standared way is there to do this .

From above query there i s a condition  to execute the query "W where w.value !='Any' "

Oracle considering this one and postgresql not.

Hi,

2875841 wrote:
Thank you
No,I haven't asked in postgresql.Google redirecting to this page.
My doubt is,is it possible to write same query in the above scenario to execute in all database.

No.  In general, you can't expect to write queries that will work in all databases.   Sometimes, the exact same query may happen to work in 2 different databases, but even then, it may not be the most efficient way to do what you need in either database.  Expect to write separate queries for the separate databases,

I just would like to know if any standared way is there to do this .
From above query there i s a condition to execute the query "W where w.value !='Any' "
Oracle considering this one and postgresql not.

As John said in reply #1, don't use implicit conversions.  One difference between Oracle and other databases is that Oracle tries very hard not to raise errors when you use the wrong datatype.  Don't try to compare a string (like testtable.value) to a NUMBER (like compnay.compnay_id).  Explicitly convert one of them to the datatype of the other.  Since not all strings can be converted to NUMBERs, you should convert the NUMBER to a string, like John showed in reply #1.

Well, it fails in Oracle 11.2.0.2 XE on windOws.

Wrote file afiedt.buf
  1  SELECT w.test_id,
  2        (SELECT comp_code FROM company WHERE company_id = W.VALUE)
  3* FROM testTable W where w.value !='Any'
SQL> /
      (SELECT comp_code FROM company WHERE company_id = W.VALUE)
                                                        *
ERROR at line 2:
ORA-01722: invalid number
SQL> desc company
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPANY_ID                                         NUMBER
 COMP_CODE                                          VARCHAR2(30)
SQL> desc testtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 VALUE                                              VARCHAR2(30)
 TEST_ID                                            NUMBER

You may have got away with it so far due to your data.

Did you consider the (first!) reply given by John Thornton?

What was the result of your testing of his proposal?

Any more errors?

yes i have tried ,that was working fine in oracle(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production).

Ok, and what you didnt say is that there is no to_char(number) in postgresql?!?!

So let me google this for you:

First hit, two answers:

https://stackoverflow.com/questions/13809547/how-do-i-convert-an-integer-to-string-as-part-of-a-postgresql-query

Thank you

Actually i were googled and found this for postgresql.But searching for any standared solution suitable for both.

Thats it.

May be cast is an option:

cast ( compnay_id as varchar2(80))

Well, what do you know, if you had explored the possibility of using to_char() (as mentioned in the very first reply)...

Reading the PostgresSQL documentation talks  about datatype conversion, one way to do it is to use     TO_CHAR()

...hmmm,  to_char() I am sure I have used that in "other"  databases you know, like Oracle for instance...TO_CHAR()

I don't have a postgres db lying around unfortunately so don't know if Postgres to_char can take a single numeric argument, it does how ever accept format masks.

Pick a large format mask to try and cope with random length numeric text strings:

SELECT w.test_id,
      (SELECT comp_code
       FROM   company
       WHERE  TO_CHAR(company_id, '99999999999999999999') = w.value)
FROM   testtable w
WHERE  w.value !='Any'

Note: your query had a table called compnay, Based on no information from you or the table setup, I decided to call my test table company.

Another Note: This has been tested and works in Oracle, up to you @"2875841" to test in postgres.

(@"chris227" postgres uses VARCHAR it does not have a VARCHAR2 datatype unfortunately so I think CAST() is out as Oracle's VARCHAR datatype is deprecated)

May be you are correct, i dont know nothing about this.

However i hoped for an alternative for to _char with format mask since there is always the issue of its length.