From sgnerd@yahoo.com.sg Tue Nov  25 12:41:02 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 16DB8D1CC81
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Nov 2003 12:41:02 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 34873-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Nov 2003 08:40:32 -0400 (AST)
Received: from smtp005.mail.ukl.yahoo.com (smtp005.mail.ukl.yahoo.com
	[217.12.11.36])
	by svr1.postgresql.org (Postfix) with SMTP id EA698D1B575
	for <pgsql-sql@postgresql.org>; Tue, 25 Nov 2003 08:40:28 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp1.mail.vip.ukl.yahoo.com with SMTP; 25 Nov 2003 12:40:30 -0000
Message-ID: <037f01c3b350$fa38ae00$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: ::text problem
Date: Tue, 25 Nov 2003 18:07:58 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_037C_01C3B37F.0E8A0290"
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-Spam-Status: No, hits=1.7 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	UPPERCASE_25_50
X-Spam-Level: *
X-Archive-Number: 200311/281
X-Sequence-Number: 16096

This is a multi-part message in MIME format.

------=_NextPart_000_037C_01C3B37F.0E8A0290
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi ,

I am running postgres 7.3.4 on Linux 7.2. I wanna print 'Not Supplied' if '=
c1' column of datatype - 'timestamp' is null. For that I am using case stat=
ement

SELECT=20
    CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as approval_date
FROM  t1;
ERROR:  Bad timestamp external representation 'Record_Not_Found'=20


SELECT=20
    CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as appro=
val_date
FROM  t1;
ERROR:  CASE types 'timestamp without time zone' and 'text' not matched


Where I am doing wrong? Please shed some light.

Regards
Senthil Kumar S
DBA

------=_NextPart_000_037C_01C3B37F.0E8A0290
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi ,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I am&nbsp;running postgres 7.3.4 on Linux 7.2. I wanna =
print=20
'Not Supplied' if&nbsp;'c1'&nbsp;column of datatype - 'timestamp' is null. =
For=20
that I am using case statement</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>SELECT </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp; CASE WHEN c1 IS NULL THEN=20
'Record_Not_Found'ELSE c1 END as approval_date</FONT></DIV>
<DIV><FONT size=3D2>FROM&nbsp; t1;</FONT></DIV>
<DIV><FONT size=3D2>ERROR:&nbsp; Bad timestamp external representation=20
'Record_Not_Found' </DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>SELECT </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp; CASE WHEN c1 IS NULL THEN=20
'Record_Not_Found'::text ELSE c1 END as approval_date</FONT></DIV>
<DIV><FONT size=3D2>FROM&nbsp; t1;</FONT></DIV></DIV>
<DIV>ERROR:&nbsp; CASE types 'timestamp without time zone' and 'text' not=
=20
matched</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>Where I am doing wrong? Please shed some light.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Regards</DIV>
<DIV>Senthil Kumar S</DIV>
<DIV>DBA</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_037C_01C3B37F.0E8A0290--




From dev@archonet.com Tue Nov  25 16:16:33 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B659D1CA61
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Nov 2003 16:16:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 88910-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Nov 2003 12:16:00 -0400 (AST)
Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net
	[194.217.242.92])
	by svr1.postgresql.org (Postfix) with ESMTP id 5A408D1DB38
	for <pgsql-sql@postgresql.org>; Tue, 25 Nov 2003 12:14:35 -0400 (AST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1AOfpV-0005Ep-0Y; Tue, 25 Nov 2003 16:14:21 +0000
Received: by mainbox.archonet.com (Postfix, from userid 529)
	id 63D7D178F1; Tue, 25 Nov 2003 16:14:19 +0000 (GMT)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id C1616178EF; Tue, 25 Nov 2003 16:14:17 +0000 (GMT)
From: Richard Huxton <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: ::text problem
Date: Tue, 25 Nov 2003 16:14:16 +0000
User-Agent: KMail/1.5
References: <037f01c3b350$fa38ae00$7502a8c0@hdsc.com>
In-Reply-To: <037f01c3b350$fa38ae00$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200311251614.16957.dev@archonet.com>
X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/282
X-Sequence-Number: 16097

On Tuesday 25 November 2003 12:37, Kumar wrote:
>
> SELECT
>     CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as
> approval_date FROM  t1;
> ERROR:  Bad timestamp external representation 'Record_Not_Found'
>
>
> SELECT
>     CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as
> approval_date FROM  t1;
> ERROR:  CASE types 'timestamp without time zone' and 'text' not matched

Almost - you're on the right lines. Try:
SELECT
  CASE
    WHEN c1 IS NULL THEN 'Record_Not_Found'::text
    ELSE c1::text
  END as approval_date FROM  t1;

You need to make sure both options return type text.

-- 
  Richard Huxton
  Archonet Ltd



From sgnerd@yahoo.com.sg Wed Nov  26 07:06:08 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 35061D1B439
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Nov 2003 07:06:08 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 99874-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Nov 2003 03:05:38 -0400 (AST)
Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com
 [216.136.173.57])
	by svr1.postgresql.org (Postfix) with SMTP id 43A8CD1B44E
	for <pgsql-sql@postgresql.org>; Wed, 26 Nov 2003 03:05:36 -0400 (AST)
Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67)
	(sgnerd@203.196.190.138 with login)
	by smtp-v1.mail.vip.sc5.yahoo.com with SMTP; 26 Nov 2003 07:05:36 -0000
Message-ID: <00e301c3b3eb$5c26f7d0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Richard Huxton" <dev@archonet.com>,
	"psql" <pgsql-sql@postgresql.org>
References: <037f01c3b350$fa38ae00$7502a8c0@hdsc.com>
	<200311251614.16957.dev@archonet.com>
Subject: Re: ::text problem
Date: Wed, 26 Nov 2003 12:33:04 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0
	tests=QUOTED_EMAIL_TEXT, REFERENCES, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200311/286
X-Sequence-Number: 16101

Thanks a lot. It worked.

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>; "psql" <pgsql-sql@postgresql.org>
Sent: Tuesday, November 25, 2003 9:44 PM
Subject: Re: [SQL] ::text problem


> On Tuesday 25 November 2003 12:37, Kumar wrote:
> >
> > SELECT
> >     CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as
> > approval_date FROM  t1;
> > ERROR:  Bad timestamp external representation 'Record_Not_Found'
> >
> >
> > SELECT
> >     CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as
> > approval_date FROM  t1;
> > ERROR:  CASE types 'timestamp without time zone' and 'text' not matched
> 
> Almost - you're on the right lines. Try:
> SELECT
>   CASE
>     WHEN c1 IS NULL THEN 'Record_Not_Found'::text
>     ELSE c1::text
>   END as approval_date FROM  t1;
> 
> You need to make sure both options return type text.
> 
> -- 
>   Richard Huxton
>   Archonet Ltd