
A while back there was some discussion about NULL.  Right now I'm trying
to do something that 'Practical SQL' seems to think is normal yet brings up
the questions from earlier conversations.  I'm querying two tables where
if the second table (specialprice) has nothing that matches sku, I don't 
care.  IIRC this is how I do it in Sybase (don't have the actual calls in
front of me so I don't know if I'm mistaken).  The following select call:

select
products.image,products.sizes,products.colors,products.weight,products.categor
y,products.accessories,products.saleprice,products.ourcost,products.descriptio
n,isnull(specialprice.specialprice,0.0) from products,specialprice where
products.sku = '28434' and (specialprice.sku = '28434' or specialprice is
null);

results in this:

ERROR:  No such function 'isnull' with the specified attributes

I thought that isnull() was something that existed in PostgreSQL 6.4, am
I wrong or is it implemented differently than Sybase?  Or am I not making
sense (it should be easy to explain if I'm not)?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Tue, 08 Dec 1998 19:56:47 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
isnull() or is it?
> I thought that isnull() was something that existed in PostgreSQL 6.4, am
> I wrong or is it implemented differently than Sybase?  Or am I not making
> sense (it should be easy to explain if I'm not)?

isnull() is on our TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 8 Dec 1998 20:24:19 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] isnull() or is it?t
> Right now I'm trying to do something that 'Practical SQL' seems to 
> think is normal yet brings up the questions from earlier 
> conversations.  I'm querying two tables where
> if the second table (specialprice) has nothing that matches sku, I 
> don't care.

>From your example query, it seems that you want to substitute 0.0 for
the "specialprice" field if an entry corresponding to a row in the first
table does not exist. That matches up with the capabilities of a left
outer join, where you would use CASE or COALESCE() to substitute the
zero for a null. Postgres does not have outer joins yet. 

Your example does *not* give you the effect you want, since the inner
join you are specifying will not match up non-existent rows. btw,
neither of my reference/tutorial books mentions ifnull() (or at least
they don't have an index entry for it). I'm looking at "A Guide to the
SQL Standard", Date and Darwen, and "Understanding the New SQL", Melton
and Simon.

In the meantime I think you can mimic the effect using a union:

 select products.image, products.sizes, products.colors, 
  products.weight, products.category, products.accessories, 
  products.saleprice, products.ourcost, products.description, 
  specialprice.specialprice
  from products,specialprice
  where (products.sku = '28434')
  and (products.sku = specialprice.sku)
 union
 select products.image, products.sizes, products.colors, 
  products.weight, products.category, products.accessories, 
  products.saleprice, products.ourcost, products.description, 
  0.0
  from products
  where products.sku = '28434'
  and (products.sku not in (select sku from specialprice);

That last clause could just have the constant rather than products.sku.

Postgres does have ISNULL in the parser, but that is just equivalent to
IS NULL.

Good luck.

                         - Tom

Wed, 09 Dec 1998 03:07:09 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] isnull() or is it?
> isnull() is on our TODO list.

If the one-line description in the TODO list is accurate and complete,
istm that the SQL92-standard function COALESCE() which I put into the
current development tree as part of the CASE feature does that and more.

Neither function would allow an inner join to magically become an outer
join. Speaking of which, remember that you're planning on helping with
outer joins for the next release, right? Or at least you haven't turned
me down yet... :)

                      - Tom

Wed, 09 Dec 1998 05:01:55 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] isnull() or is it?t
> > isnull() is on our TODO list.
> 
> If the one-line description in the TODO list is accurate and complete,
> istm that the SQL92-standard function COALESCE() which I put into the
> current development tree as part of the CASE feature does that and more.
> 
> Neither function would allow an inner join to magically become an outer
> join. Speaking of which, remember that you're planning on helping with
> outer joins for the next release, right? Or at least you haven't turned
> me down yet... :)

I reserve the right to fail, though.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 9 Dec 1998 01:23:07 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] isnull() or is it?t