Thread: isnull() or is it?
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
entitledto lecture me about bloat than the federal government" -- Tony Snow
==========================================================================
> 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)unionselect 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
> 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, Pennsylvania19026
> 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
> > 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, Pennsylvania19026