pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Standard syntax?
6+ messages / 6 participants
[nested] [flat]

* Standard syntax?
@ 2000-10-12 17:44  Franz J Fortuny <ffortuny@ivsol.com>
  0 siblings, 5 replies; 6+ messages in thread

From: Franz J Fortuny @ 2000-10-12 17:44 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Standard syntax?
@ 2000-10-12 17:54  Josh Berkus <josh@agliodbs.com>
  parent: Franz J Fortuny <ffortuny@ivsol.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2000-10-12 17:54 UTC (permalink / raw)
  To: pgsql-sql

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 information technology      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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Standard syntax?
@ 2000-10-12 17:54  Tomas Berndtsson <tomas@nocrew.org>
  parent: Franz J Fortuny <ffortuny@ivsol.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Tomas Berndtsson @ 2000-10-12 17:54 UTC (permalink / raw)
  To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Standard syntax?
@ 2000-10-12 18:19  Jie Liang <jliang@ipinc.com>
  parent: Franz J Fortuny <ffortuny@ivsol.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Jie Liang @ 2000-10-12 18:19 UTC (permalink / raw)
  To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Standard syntax?
@ 2000-10-12 18:45  Peter Eisentraut <peter_e@gmx.net>
  parent: Franz J Fortuny <ffortuny@ivsol.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Peter Eisentraut @ 2000-10-12 18:45 UTC (permalink / raw)
  To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql

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/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Standard syntax?
@ 2000-10-12 19:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Franz J Fortuny <ffortuny@ivsol.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2000-10-12 19:32 UTC (permalink / raw)
  To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-10-12 19:32 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-12 17:44 Standard syntax? Franz J Fortuny <ffortuny@ivsol.com>
2000-10-12 17:54 ` Josh Berkus <josh@agliodbs.com>
2000-10-12 17:54 ` Tomas Berndtsson <tomas@nocrew.org>
2000-10-12 18:19 ` Jie Liang <jliang@ipinc.com>
2000-10-12 18:45 ` Peter Eisentraut <peter_e@gmx.net>
2000-10-12 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox