My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database systems without modifications. I saw people do different way to make their program database independent. For example: Externalize SQL st...
Is there a good tool for ensuring that an SQL query is valid ANSI SQL, and optionally what DBMSs will fail to interpret it? I've found http://developer.mimer.com/validator but I was wondering whether there is a command line tool, preferably open source. ...
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? The LIMIT keyword limits the number of rows returned by a SELECT e.g: SELECT * FROM People WHERE Age > 18 LIMIT 2; returns 2 rows. SELECT * FROM People WHERE Age > 18 LIMIT 10, 2; returns 2 rows after the first 10. ...
My team is looking into geospatial features offered by different database platforms. Are all of the implementations database specific, or is there a ANSI SQL standard, or similar type of standard, which is being offered, or will be offered in the future? I ask, because I would like the implemented code to be as database agnostic as pos...
I'm trying to convert some Oracle SQL queries to work with (in theory) any SQL database. Some of the queries are hierarchical in nature and are written using CONNECT BY. Is there a standard SQL alternative to Oracle's START WITH...CONNECT BY syntax? Or is there some recommended process I should follow to convert the hierarchical queries...
Can anyone recommend a good ANSI SQL reference manual? I don't necessary mean a tutorial but a proper reference document to lookup when you need either a basic or more in-depth explanation or example. Currently I am using W3Schools SQL Tutorial and SQL Tutorial which are ok, but I don't find them "deep" enough. Of course, each major ...
I need a way to determine the number of days between two dates in SQL. Answer must be in ANSI SQL. ...
Hello, I am attempting to make an application capable of running on both Sql Server and PostgreSQL. I can not seem to find a common expression that is basically select * from table where booleancol=false on SQL Server I must do(which is very confusing because the default value for bit types must be true or false, but you can't assi...
Hello, I am attempting to make some cross-DB(SQL Server and PostgreSQL) compatible SQL. What I am needing to do is clone a row. It is preferable to do it locally on the SQL server without having to pull the entire row down to our client and reinsert it. Also, we prefer to not have to create a new SQL query from scratch putting in column ...
Can any of these queries be done in SQL? SELECT dates FROM system WHERE dates > 'January 5, 2010' AND dates < 'January 30, 2010' SELECT number FROM system WHERE number > 10 AND number < 20 I'd like to create a generate_series, and that's why I'm asking. ...
Is this valid ANSI SQL?: SELECT 1 AS X ,2 * X AS Y ,3 * Y AS Z Because Teradata (12) can do this, as well as this (yes, crazy isn't it): SELECT 3 * Y AS Z ,2 * X AS Y ,1 AS X But SQL Server 2005 requires something like this: SELECT X ,Y ,3 * Y AS Z FROM ( SELECT X ...
This has always bothered me - why does the GROUP BY clause in a SQL statement require that I include all non-aggregate columns? These columns should be included by default - a kind of "GROUP BY *" - since I can't even run the query unless they're all included. Every column has to either be an aggregate or be specified in the "GROUP BY", ...
Quest The query selects all the points beginning with "Vancouver" and are within a 5 minute area from the center of all locations beginning with "Vancouver". For example, Vancouver South Fraser, Vancouver Fairview, and Vancouver Ballantree Place W have latitudes and longitudes within 5 minutes of their average latitude and longitude. Th...
I am writing a simple data warehouse that will allow me to query the table to observe periodic (say weekly) changes in data, as well as changes in the change of the data (e.g. week to week change in the weekly sale amount). For the purposes of simplicity, I will present very simplified (almost trivialized) versions of the tables I am us...
Hi I am using two different databases for my project, Oracle and Apache Derby, and am trying as much as possible to use the ANSI SQL syntax supported by both of the databases. I have a table with a column amount_paid NUMERIC(26,2). My old code, which was using Oracle db, needed to retrieve value in this format SELECT LTRIM(TO...
What's the difference between these two queries? I've been resistant to jumping on the ANSI-syntax bandwagon because I have not been able to unravel various syntactical ambiguities. Is 1) returning the product of the join and only then filtering out those joined records which have weight >= 500? And is 2) filtering out those prior to ...
Is there an ANSI SQL equivalent to Oracle's DECODE function? Oracle's decode function is the IF-THEN-ELSE construct in SQL. ...
The act of transforming procedural code into SQL has been of interest to me lately. I know that not absolutely everything is expressable in a turing complete procedural language. What if you have a special purpose procedural language though? For instance converting something like this: foreach(var row in Table){ if(row.FirstName=="F...