Thread: Standard syntax?

Standard syntax?

From
"Franz J Fortuny"
Date:
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




Re: Standard syntax?

From
Josh Berkus
Date:
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


Re: Standard syntax?

From
Tomas Berndtsson
Date:
"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


Re: Standard syntax?

From
Jie Liang
Date:
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





Re: Standard syntax?

From
Peter Eisentraut
Date:
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/



Re: Standard syntax?

From
Tom Lane
Date:
"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