SQL != SQL...
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
When does the hurting stop?
When you start using NoSQL, No SQL at all :laugh:
-
Sander Rossel wrote:
When does the hurting stop
When you stop doing presentation logics in the database. I also agree with Phil, why do you need to support more than one database?
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
why do you need to support more than one database?
Because one features premium pay and the other features ubiquitous jobs?
-
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.CDP1802 wrote:
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
But it's the minor differences that cause the subtle bugs which take the most time and require the greatest pulling of hair to resolve.
-
So what you are saying is T-SQL <> PL/SQL? :)
Mongo: Mongo only pawn... in game of life.
For some of us, "Transact-SQL" <> [Transact-SQL]. In Sybase SQL Anywhere, you can get a current date-time value using "CURRENT_TIMESTAMP", "GETDATE( )" , "CURRENT TIMESTAMP" or "NOW( )". Good luck using either "CURRENT TIMESTAMP" or "NOW( )" in Microsoft SQL Server.
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
When you stop writting SQL and let your ORM handle it! ;P
Eric
-
CDP1802 wrote:
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
But it's the minor differences that cause the subtle bugs which take the most time and require the greatest pulling of hair to resolve.
Ok, then it should be the probabilty of a subtle bug, weighted by its severity. :-O
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns. -
Ok, then it should be the probabilty of a subtle bug, weighted by its severity. :-O
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.Don't forget to also weight according to the severity of the motivational floggings that are part of the debugging process. ;)
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server.
Why is that important? If you're running some kind of application to interface with the database, seems like the call to the database should just invoke a sproc (for example--maybe a query, whatever). You have the same named sproc on two instances, but they work differently. The code layer is effectively calling an interface (i.e., "whatever I'm connected to, execute the 'selectMyStuff' sproc"), and each database is the concrete implementation of that interface. This is a simple example, but what if the databases had completely different structures? You wouldn't expect to deploy the same SQL to both, you'd have to write custom procedures which happened to take the same parameters and return the same result set (i.e., implement the 'interface'), even though they perform that operation in significantly different ways.
-
Sander Rossel wrote:
But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server.
Why is that important? If you're running some kind of application to interface with the database, seems like the call to the database should just invoke a sproc (for example--maybe a query, whatever). You have the same named sproc on two instances, but they work differently. The code layer is effectively calling an interface (i.e., "whatever I'm connected to, execute the 'selectMyStuff' sproc"), and each database is the concrete implementation of that interface. This is a simple example, but what if the databases had completely different structures? You wouldn't expect to deploy the same SQL to both, you'd have to write custom procedures which happened to take the same parameters and return the same result set (i.e., implement the 'interface'), even though they perform that operation in significantly different ways.
Yeah, normally I'd do that, but this time I'm generating the query client side :) Anyway, it's not all that important, I should abstract away such stuff and implement it for each database anyway. I was just amazed that something so simple can't be done uniformly by two of the biggest databases that both work with the same language that has an ANSI standard...
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Sounds like a good time for some dynamic sql. I know lots/most folks dis the idea of such, but it certainly has its place imho.
Especially when you know how it works[^] :)
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
