ansi-sql

How do you write your applications to be database independent?

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...

How to test SQL for validity from the command line?

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?

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. ...

Are all SQL Geospatial implementations database specific?

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...

Standard alternative to CONNECT BY?

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...

ANSI SQL Manual

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 ...

Number of days between two dates - ANSI SQL

I need a way to determine the number of days between two dates in SQL. Answer must be in ANSI SQL. ...

Someway to do `where booleanvalue=false` on both Sql Server and PostgreSQL?

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...

Clone a row with a primary key in SQL?

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 ...

SELECT any FROM system

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. ...

Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

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 ...

Why do I need to explicitly specify all columns in a SQL "GROUP BY" clause - why not "GROUP BY *"?

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", ...

Eliminate subquery for average numeric value

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...

How to write these two queries for a simple data warehouse, using ANSI SQL?

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...

using ansi sql syntax for formatting Numeric

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...

Where to put conditionals in ANSI-syntax SQL queries

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 ...

Standard SQL alternative to Oracle DECODE

Is there an ANSI SQL equivalent to Oracle's DECODE function? Oracle's decode function is the IF-THEN-ELSE construct in SQL. ...

Unrolling procedural code into 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...