Home » SQL & PL/SQL » SQL & PL/SQL » How to unify a query so that it would work on both Oracle and MS SQL Server? (merged b
How to unify a query so that it would work on both Oracle and MS SQL Server? (merged b [message #266379] Mon, 10 September 2007 12:23 Go to next message
L_oracle
Messages: 4
Registered: September 2007
Junior Member
Hi,

I recently wrote some SQL queries for MS-SQL, and now that I'm using Oracle also, I keep getting an Oracle Error 936 message. In the SQL statement, I refer to the fields as [table_name].[field_name] but I'm not sure if that's the right syntax in Oracle. I can't use table.field because in MS-SQL some of the fields don't always work. Do I need to use quotes?

Thanks!
[EDITED by LF - please, do not open a new topic for an old problem. Continue discussion (if any) in existing one. Your topics have been merged and renamed]

[Updated on: Wed, 12 September 2007 01:43] by Moderator

Report message to a moderator

Re: Table and field brackets [message #266382 is a reply to message #266379] Mon, 10 September 2007 12:25 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Don't put brackets around table or column names. That is not valid in Oracle.
Re: Table and field brackets [message #266383 is a reply to message #266382] Mon, 10 September 2007 12:26 Go to previous messageGo to next message
L_oracle
Messages: 4
Registered: September 2007
Junior Member
Would "table_name"."field_name" work?
Re: Table and field brackets [message #266384 is a reply to message #266379] Mon, 10 September 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't use table.field because in MS-SQL some of the fields don't always work.

Are you working with Oracle or MSSQL?
Quote:
Do I need to use quotes?

It depends on your table and field names.

Regards
Michel
Re: Table and field brackets [message #266388 is a reply to message #266379] Mon, 10 September 2007 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Would "table_name"."field_name" work?
This format would NOT generate any SYNTAX error but should be avoided at all costs because Oracle would try to use lower case names & likely fail since this is NOT the default behavior.
Dates in Oracle and MS-SQL [message #266846 is a reply to message #266379] Tue, 11 September 2007 15:12 Go to previous messageGo to next message
L_oracle
Messages: 4
Registered: September 2007
Junior Member
Hi,

I was wondering if there was a common way to use dates in Oracle and MS-SQL.

I want to do SELECT * FROM "table" WHERE "date_field" < '01/01/2007'

It works in MS-SQL but I don't think it will work using Oracle. Is there some sort of syntax that will work with both?
Re: Dates in Oracle and MS-SQL [message #266851 is a reply to message #266846] Tue, 11 September 2007 15:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Many things wrong with Oracle I see here. I have no idea about MS SQL. Here are two.

1. Don't enclose objects (eg. tables, columns) in double quotes. Having a mixed case object name is a whole other can of worms you don't want.

2. '01/01/2007' is a character string, not a date. You MUST use a TO_DATE function against character strings.
Re: Dates in Oracle and MS-SQL [message #266853 is a reply to message #266851] Tue, 11 September 2007 15:46 Go to previous messageGo to next message
L_oracle
Messages: 4
Registered: September 2007
Junior Member
Thanks for your help.

I'm trying to keep the query the same for using MS-SQL and Oracle, and I need to enclose the field and table names somehow, is there another way to do it? When I was just using MS-SQL, I used brackets but they don't work with Oracle so I switched to quotes, and I don't think I should have a problem with case-sensitivity. If I don't enclose the field and table names with quotes or brackets, MS-SQL has a problem with some of the field names.

MS-SQL does not have a TO_DATE function. Is there another way to use dates that will work with both?
Re: Dates in Oracle and MS-SQL [message #266953 is a reply to message #266853] Wed, 12 September 2007 01:40 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, you have two cars: one has Otto engine, while another one has a Diesel engine.

You want to pour the same fuel into both of them.

Can't do.

Otto <> Diesel.

Oracle <> MS SQL Server.
Previous Topic: problem with table ....help
Next Topic: insert statements
Goto Forum:
  


Current Time: Tue Dec 06 04:17:05 CST 2016

Total time taken to generate the page: 0.12136 seconds