From bc@vio.ro Tue Sep 9 07:09:43 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 658AAD1B4E9 for ; Tue, 9 Sep 2003 07:09:43 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 36957-06 for ; Tue, 9 Sep 2003 04:09:12 -0300 (ADT) Received: from mail1.dr.myx.net (mail1.dr.myx.net [217.10.193.161]) by svr1.postgresql.org (Postfix) with ESMTP id 95D3AD1B4F3 for ; Tue, 9 Sep 2003 04:09:10 -0300 (ADT) Received: from workstation24 (unknown [81.12.137.146]) by mail1.dr.myx.net (mydomain.myx.net) with ESMTP id 7A1FD300841; Tue, 9 Sep 2003 10:09:07 +0300 (EEST) Message-ID: <005701c376a0$ea9b5840$0600a8c0@fix.ro> From: "Viorel Dragomir" To: "Richard Sydney-Smith" , References: <001001c376a0$ee48a8d0$f06832d2@athlon2000> Subject: Re: Conversion from Sybase ASA Date: Tue, 9 Sep 2003 10:06:36 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0054_01C376BA.0DD03790" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/58 X-Sequence-Number: 15036 This is a multi-part message in MIME format. ------=_NextPart_000_0054_01C376BA.0DD03790 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9= /30' then 'U' else 'P' endif) as pres from ftrans select 'T' as src,(CASE WHEN tbon is null or tbon < '1900/12/12' or tbon > = '1900/9/30' THEN 'U' ELSE 'P' END) as pres from ftrans I think it works in Oracle too :) Pay attention at tbon < '1900/12/12'. Try to use conversion on date formats= like to_date() or to_timestamp(). Good luck ----- Original Message -----=20 From: Richard Sydney-Smith=20 To: pgsql-sql@postgresql.org=20 Sent: Tuesday, September 09, 2003 10:06 AM Subject: [SQL] Conversion from Sybase ASA In sybase I can use the following syntax: select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900= /9/30' then 'U' else 'P' endif) as pres from ftrans this returns two columns: src =3D 'T' and pres which has either the value= 'U' or 'P' how would I express this in postgresql? thanks Richard ------=_NextPart_000_0054_01C376BA.0DD03790 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
select 'T' = as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' then 'U' else=20 'P' endif) as pres from ftrans
 
select 'T' as= src,(CASE=20 WHEN tbon is null=20 or tbon < '1900/12/12' or tbon > '1900/9/30' THEN  'U' ELSE  '= P' END)=20 as pres from=20 ftrans
 
I think it works in Oracle too :)
Pay attention at tbon < '1900/12/12'. T= ry=20 to use conversion on date formats like to_date() or=20 to_timestamp().
 
Good luck
----- Original Message -----
Fro= m:=20 Richard Sydney-Smith
To: pgsql-sql@postgresql.org
Sent: Tuesday, September 09, 2003 = 10:06=20 AM
Subject: [SQL] Conversion from Syb= ase=20 ASA

In sybase I can use the following=20 syntax:
 

select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9/30' <= /FONT>then 'U' else 'P' endif) as pres from=20 ftrans

this returns two columns: src =3D 'T' and pres which ha= s either=20 the value 'U' or 'P'

how would I express this in postgresql?

thanks

Richard

------=_NextPart_000_0054_01C376BA.0DD03790-- From richard@ibisaustralia.com Tue Sep 9 07:05:24 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3E4ABD1B527 for ; Tue, 9 Sep 2003 07:05:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 37604-02 for ; Tue, 9 Sep 2003 04:04:53 -0300 (ADT) Received: from server146.hostingrevolution.com (server146.hostingrevolution.com [64.21.143.245]) by svr1.postgresql.org (Postfix) with ESMTP id E6296D1B503 for ; Tue, 9 Sep 2003 04:04:51 -0300 (ADT) Received: from 240.a.003.pth.iprimus.net.au ([210.50.104.240] helo=athlon2000) by server146.hostingrevolution.com with smtp (Exim 4.22) id 19wcYV-00036L-3B for pgsql-sql@postgresql.org; Tue, 09 Sep 2003 02:04:30 -0500 Message-ID: <001001c376a0$ee48a8d0$f06832d2@athlon2000> From: "Richard Sydney-Smith" To: Subject: Conversion from Sybase ASA Date: Tue, 9 Sep 2003 15:06:40 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01C376E3.F8E903A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - ibisaustralia.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN, HTML_FONT_COLOR_UNSAFE X-Spam-Level: X-Archive-Number: 200309/57 X-Sequence-Number: 15035 This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C376E3.F8E903A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In sybase I can use the following syntax: select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > '1900/9= /30' then 'U' else 'P' endif) as pres from ftrans this returns two columns: src =3D 'T' and pres which has either the value '= U' or 'P' how would I express this in postgresql? thanks Richard ------=_NextPart_000_000D_01C376E3.F8E903A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
In sybase I can use the following=20 syntax:
 

select 'T' as src,(if tbon is nul= l or tb= on <=20 '1900/12/12' or= tbon >=20 '1900/9/30' then 'U'=20 else 'P' endif)= =20 as pres = from ftrans

this returns two columns: src =3D 'T' and pres which has = either=20 the value 'U' or 'P'

how would I express this in postgresql?

thanks

Richard

= ------=_NextPart_000_000D_01C376E3.F8E903A0-- From barwick@gmx.net Tue Sep 9 07:18:47 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id DA437D1B503 for ; Tue, 9 Sep 2003 07:18:47 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 35837-10 for ; Tue, 9 Sep 2003 04:18:38 -0300 (ADT) Received: from p15140235.pureserver.info (sql-info.de [217.160.166.164]) by svr1.postgresql.org (Postfix) with ESMTP id C2C80D1B502 for ; Tue, 9 Sep 2003 04:18:36 -0300 (ADT) Received: from iwate.sql-info.de (pD951728F.dip.t-dialin.net [217.81.114.143]) (using SSLv3 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by p15140235.pureserver.info (Postfix) with ESMTP id B9AA31C58; Tue, 9 Sep 2003 09:18:36 +0200 (CEST) From: Ian Barwick To: "Richard Sydney-Smith" , Subject: Re: Conversion from Sybase ASA Date: Tue, 9 Sep 2003 09:17:26 +0200 User-Agent: KMail/1.5.1 References: <001001c376a0$ee48a8d0$f06832d2@athlon2000> In-Reply-To: <001001c376a0$ee48a8d0$f06832d2@athlon2000> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200309090917.26320.barwick@gmx.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/59 X-Sequence-Number: 15037 On Tuesday 09 September 2003 09:06, Richard Sydney-Smith wrote: > In sybase I can use the following syntax: > > select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans > > this returns two columns: src = 'T' and pres which has either the value 'U' > or 'P' > > how would I express this in postgresql? "CASE": http://www.postgresql.org/docs/7.3/static/functions-conditional.html Ian Barwick barwick@gmx.net