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
-
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
-
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 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
-
Works in Oracle
Wrong is evil and must be defeated. - Jeff Ello
-
Sander Rossel wrote:
When does the hurting stop? :((
When you stop using both dbms at the same time? Or when you stop using them totally? :)
I never finish anyth
phil.o wrote:
When you stop using both dbms at the same time?
Well, that may be a solution(if he wants stop getting hurt ) ;)
phil.o wrote:
Or when you stop using them totally?
Don't tell me that you want him to us MS access :sigh:
-
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
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. -
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
Why? Layers are soooo last decade.
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. -
phil.o wrote:
When you stop using both dbms at the same time?
Well, that may be a solution(if he wants stop getting hurt ) ;)
phil.o wrote:
Or when you stop using them totally?
Don't tell me that you want him to us MS access :sigh:
-
Sander Rossel wrote:
When does the hurting stop? :((
When you stop using both dbms at the same time? Or when you stop using them totally? :)
I never finish anyth
Maybe when I retire?
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 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
Our company uses Oracle and SQL Server, both from C#. SQL Server support isn't really a requirement (now), but I was pretty sure it would work as it's SQL in it's simplest form (although apparently there is no 'simple' form of SQL)... Anyway, screw SQL Server support.
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
-
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:
stay away from anything that has to do with browsers
As a full-stack web developer that'll be difficult. And yes it makes me cry and gives me nightmares, why can't we all just get along? Sometimes I want to go back to my safe and simple WinForms, now that's good technology :)
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
-
CDP1802 wrote:
stay away from anything that has to do with browsers
As a full-stack web developer that'll be difficult. And yes it makes me cry and gives me nightmares, why can't we all just get along? Sometimes I want to go back to my safe and simple WinForms, now that's good technology :)
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
Very true, but some people think it just does not feel right if it is not as complicated and convoluted as possible. Browsers, CSS, JavaScript HTMl, throw them all away and build a native client where ever possible. Then you will certainly have a better UI. As for the databases, perhaps you should use a ORM as abstraction. Then you can be fairly independent of the actual database that is used. At the price (as someone already noted) that you will do everybody a favor and not do any more presentation layer stuff in the data layer.
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. -
Our company uses Oracle and SQL Server, both from C#. SQL Server support isn't really a requirement (now), but I was pretty sure it would work as it's SQL in it's simplest form (although apparently there is no 'simple' form of SQL)... Anyway, screw SQL Server support.
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
It'll get even funnier when you realize that even when the SQL is completely compatible, the results may not be. For example: Oracle doesn't have an empty string.
Wrong is evil and must be defeated. - Jeff Ello
-
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 what you are saying is T-SQL <> PL/SQL? :)
Mongo: Mongo only pawn... in game of life.
-
Very true, but some people think it just does not feel right if it is not as complicated and convoluted as possible. Browsers, CSS, JavaScript HTMl, throw them all away and build a native client where ever possible. Then you will certainly have a better UI. As for the databases, perhaps you should use a ORM as abstraction. Then you can be fairly independent of the actual database that is used. At the price (as someone already noted) that you will do everybody a favor and not do any more presentation layer stuff in the data layer.
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:
perhaps you should use a ORM as abstraction
This is the 'dynamic everything should be possible' kind of code. In my experience ORM's don't handle that very well... We've tried some solutions, but ultimately decided to build our own solution, which is what I'm now doing :laugh:
CDP1802 wrote:
you will do everybody a favor and not do any more presentation layer stuff in the data layer
I'm not ;)
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
-
It'll get even funnier when you realize that even when the SQL is completely compatible, the results may not be. For example: Oracle doesn't have an empty string.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
For example: Oracle doesn't have an empty string.
Or a bit/bool data type...
Jörgen Andersson wrote:
It'll get even funnier
I'm not laughing ;p
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
-
CDP1802 wrote:
perhaps you should use a ORM as abstraction
This is the 'dynamic everything should be possible' kind of code. In my experience ORM's don't handle that very well... We've tried some solutions, but ultimately decided to build our own solution, which is what I'm now doing :laugh:
CDP1802 wrote:
you will do everybody a favor and not do any more presentation layer stuff in the data layer
I'm not ;)
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:
This is the 'dynamic everything should be possible' kind of code.
Good luck. Everybody and his dog must give it a try, I guess.
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. -
Works in Oracle
Wrong is evil and must be defeated. - Jeff Ello
-
Maybe when I retire?
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
I don't think so. At that time, extensive usage of both systems during your carreer will have caused severe brain damages, displacing the moral pain to a physical, unsustainable pain. Better stick to Excel as early as possible ;P
I never finish anyth
