Received: from p1.selectacast.net (IDENT:root@p1.selectacast.net [206.41.1.251]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA11484 for ; Thu, 30 Mar 2000 18:07:33 -0500 (EST) (envelope-from jks@p1.selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id SAA17977; Thu, 30 Mar 2000 18:07:17 -0500 Message-ID: <38E3DE24.72ACFA25@selectacast.net> Date: Thu, 30 Mar 2000 18:07:16 -0500 From: Joseph Shraibman Organization: Xtenit X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.13 i686) X-Accept-Language: en MIME-Version: 1.0 To: jhunt@chac.qld.edu.au CC: Joseph Shraibman , "pgsql-sql@postgresql.org" Subject: Re: isnull References: <004701bf9901$c2b322c0$9714a8c0@julies> <38E3D461.E80EB898@selectacast.net> <38E3DC17.3F1A865@chac.qld.edu.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200003/8 Julie Hunt wrote: > Yes, you're right. I remember attempting to do an outer left join using the > nullif but without success so I used the case function instead with success. > Maybe someone else knows more about nullif and why it doesn't seem to work. > > Regards, > Julie > I've actually taken a closer look at the web page and not only did the name change, but I now notice that they change what it is supposed to do as well. It does work as currently defined, but I needed something that does what the original documentation said: IFNULL(input,non-NULL substitute) return second argument if first is NULL Example: IFNULL(c1, 'N/A') > > Joseph Shraibman wrote: > > > Julie Hunt wrote: > > > > > Sorry, I haven't come across ifnull > > > > Ah, I think the documentation that came with 6.5.3 was wrong, because > > http://www.postgresql.org/docs/postgres/functions.htm#AEN2257 has the function > > nullif where ifnull was in my documentation. I don't get errors now, but it > > just doesn't seem to do it's job. > > > > playpen=> insert into tablea values (100, null); > > INSERT 23168 1 > > playpen=> select * from tablea; > > indexa|data > > ------+---- > > 1| 1 > > 2| 2 > > 3| 2 > > 4| 4 > > 7| 7 > > 6| 8 > > 5| 8 > > 100| > > (8 rows) > > > > playpen=> select *, data from tablea; > > indexa|data|data > > ------+----+---- > > 1| 1| 1 > > 2| 2| 2 > > 3| 2| 2 > > 4| 4| 4 > > 7| 7| 7 > > 6| 8| 8 > > 5| 8| 8 > > 100| | > > (8 rows) > > > > playpen=> select *, nullif(data, 9) from tablea; > > indexa|data|case > > ------+----+---- > > 1| 1| 1 > > 2| 2| 2 > > 3| 2| 2 > > 4| 4| 4 > > 7| 7| 7 > > 6| 8| 8 > > 5| 8| 8 > > 100| | > > (8 rows) > > > > > > > > Julie > > > > > > >Oops, my mistake. I meant ifnull. Whenever I try to use ifnull I get an > > > >error so I have to use a case when a = null then default else a end. > > > > > > > >Julie Hunt wrote: > > > > > > > >> There is a space between is and null: is null > > > >> > > > >> Regards > > > >> > > > >> Joseph Shraibman wrote: > > > >> > > > >> > Could someone give me an example of isnull? Whenever I try it I get an > > > >> > error message. > > > >