Thread: Standard syntax?
I have been using (with success) this SQL statement in PostgreSQL: select col1, case when col2 = true then col3 else col4 end as colw, colM where .... etc. The above syntax, however, does not work for Interbase (6.01). For those who have had experience in other SQL servers, is this a "standard" or ANSI 9X compatible syntax or should I refrain from such syntax if I want my statements to be transportable from SQL Server to SQL Server? Best regards, Franz Fortuny
Franz, You'd better stay away from that syntax if you want to make your applications portable. I can tell you that it won't work on MS SQL Server or MySQL. I can't speak for Oracle. -Josh Berkus P.S. Thanks for the nifty construction ... I wouldn't have thought of it! -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
"Franz J Fortuny" <ffortuny@ivsol.com> writes: > I have been using (with success) this SQL statement in PostgreSQL: > > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where .... etc. > > The above syntax, however, does not work for Interbase (6.01). For those who > have had experience in other SQL servers, is this a "standard" or ANSI 9X > compatible syntax or should I refrain from such syntax if I want my > statements to be transportable from SQL Server to SQL Server? It is not part of SQL92, and I don't think it's part of SQL-3 either. Many databases (PostgreSQL, MSSQL, Oracle, ...) have such things though, but the syntax is different between them. If you can do without them, that's what you should try to do. Tomas
Hi, This is not SQL92, I believe it's an extention of Pg , Oracle uses 'decode' to implement if-then elsif then-else structure. Franz J Fortuny wrote: > I have been using (with success) this SQL statement in PostgreSQL: > > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where .... etc. > > The above syntax, however, does not work for Interbase (6.01). For those who > have had experience in other SQL servers, is this a "standard" or ANSI 9X > compatible syntax or should I refrain from such syntax if I want my > statements to be transportable from SQL Server to SQL Server? > > Best regards, > > Franz Fortuny -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com
Franz J Fortuny writes: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where .... etc. > is this a "standard" or ANSI 9X compatible syntax Yes. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
"Franz J Fortuny" <ffortuny@ivsol.com> writes:
> I have been using (with success) this SQL statement in PostgreSQL:
> select col1,
> case when col2 = true then
> col3
> else
> col4
> end as colw,
> colM
> where .... etc.
> The above syntax, however, does not work for Interbase (6.01).
CASE expressions are specified in SQL92, but they're labeled as an
"intermediate SQL" feature rather than an "entry SQL" feature.
So I'm not surprised that some other DBMSes don't have them.
Entry SQL is a pretty impoverished subset (no VARCHAR type, to take
a random example), so nearly everyone implements at least some
intermediate- and full-SQL features. But exactly which ones is
highly variable.
regards, tom lane