Stack Overflow
Stack Overflow

Questions tagged [ansi-sql]

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

SQL/PSM (SQL/Persistent Stored Modules) is the ISO standard defining an extension of SQL with a procedural language for use in stored procedures.

In Oracle PL/SQL (for stored procedures) and SQL (for queries) are two completely different languages (and are internally executed by different engines). DB2, PostgreSQL and Firebird also make clear distinction between the query language (called SQL) and the procedural language (for which every vendor uses a different name).

Microsoft SQL Server doesn't make this distinction: only T-SQL is used which covers the query language and the procedural language.

355 questions
8
votes
5 answers

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…
Dennis C
  • 24,511
  • 12
  • 71
  • 99
8
votes
9 answers

SQL Joins: Future of the SQL ANSI Standard (where vs join)?

We are developing ETL jobs and our consultant has been using "old style" SQL when joining tables select a.attr1, b.attr1 from table1 a, table2 b where a.attr2 = b.attr2 instead of using the inner join clause select a.attr1, b.attr1 from table1 as a…
atricapilla
  • 2,560
  • 12
  • 36
  • 39
8
votes
2 answers

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?…
user201887
8
votes
1 answer

Ansi SQL type casting

Is it allowed to cast types in ANSI SQL like in postgres for example: SELECT SUM( CAST(qnty AS int) - CAST(reserve AS int) ) AS sum ... qnty and reserve are character columns.
Mikhail.Mamaev
  • 7,879
  • 5
  • 24
  • 28
7
votes
2 answers

Comparisons with NULLs in SQL

ANSI-92 SQL mandates that comparisons with NULL evaluate to "falsy," eg: SELECT * FROM table WHERE field = NULL SELECT * FROM table WHERE field != NULL Will both return no rows because NULL can't be compared like that. Instead, the predicates IS…
NullUserException
  • 83,810
  • 28
  • 209
  • 234
7
votes
2 answers

Using the same table alias twice in a query

My coworker, who is new to ANSI join syntax, recently wrote a query like this: SELECT count(*) FROM table1 t1 JOIN table2 t2 ON (t1.col_a = t2.col_a) JOIN table3 t3 ON (t2.col_b = t3.col_b) JOIN table3…
AndyDan
  • 749
  • 2
  • 13
  • 29
7
votes
2 answers

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…
Kim Stebel
  • 41,826
  • 12
  • 125
  • 142
7
votes
5 answers

Select 1000 distinct names from 100 million records via standard sql

I have a table tb_FirstName with one field FirstName. The table has 100 million non null records with lots of repetitions e.g. John occurs 2 million times. The distinct count of FirstName is over 2 million. How do I select 1000 distinct names as…
whytheq
  • 34,466
  • 65
  • 172
  • 267
6
votes
2 answers

'LEFT JOIN' vs 'LEFT OUTER JOIN'

I know there is really no difference, but is 'LEFT JOIN' an ANSI form or are there any RDBMS's that will fail 'LEFT JOIN' and require 'LEFT OUTER JOIN'. [I am asking here so I can save a few clicks, form fillings, etc to get the correct ANSI…
ManiP
  • 713
  • 2
  • 8
  • 19
6
votes
5 answers

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…
pearcewg
  • 9,545
  • 21
  • 79
  • 125
6
votes
1 answer

Netezza UPDATE from one table to another

This is my query that does not work in Netezza: UPDATE TABLE1 A SET A.COL1= (SELECT DISTINCT B.COL1 FROM TABLE2 B WHERE B.ID= A.ID AND B.DeptID=104) WHERE A.DeptID=3 How do I re-write this query? Please help.
ozzboy
  • 2,672
  • 8
  • 42
  • 69
6
votes
2 answers

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…
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
6
votes
8 answers

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…
strike_noir
  • 4,080
  • 11
  • 57
  • 100
6
votes
4 answers

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.
David Wright
  • 235
  • 1
  • 4
  • 8
6
votes
3 answers

ANSI equivalent of IS NULL

I am trying to find the ANSI way to write the T-SQL 'IS NULL'. (corrected, was 'IN NULL') Some posts on the internet say you can use coalesce to make it work like 'IS NULL' The reason I like to do this: portable code. And the query must return the…
chihwah li
  • 85
  • 1
  • 1
  • 4
1 2
3
…
23 24