SQL Pain in the ...

See this ...

select * from "e:\server\data\AK_Stock" A
left outer join
"e:\server\data\AK_Breakdown" B
on A.StockCode = B.StockCode
where A.StockFree + B.Allocated > 0

I am using Borland Local SQL which is a subset of ANSI92 SQL.

Looks simple yeah. Joins two tables. Stockcode is unique in both tables so the join is not "one to many" (I would use Group By if it was).

If all the stock codes in table A exist in table B then the result is as expected.

Lets say not all the stock code exists in table B, because perhaps table B starts empty and builds over time. I have included a left outer join to ensure that a record still gets returned for every record in table A.

Here's where the stinker comes in ... If a record doesn't exist in Table B, B.Allocated will return null (not zero) which should be fine but it ISN'T because the Where clause just simply decides to exlude the entire record because of the null value, it doesn't treat it as zero. ARG!

I tried doing 0+B.Allocated but that still fails. I tried Cast(0+B.Allocated as Integer) but this gives totally duff results as Cast seems to use the last valid value read for B.Allocated instead of the correct one which is null. I'm sure this is a Borland Local SQL bug. I know it's doing something dumb as if you change the minus sign to a plus sign you get all the records that the other sign missed out which is ridiculous as +0 and -0 are the same.

Anyway I can fix it by boringly building a Breakdown table that has one record for everystock item and then using that so we get 0 instead of null. Or I can change the where line to this:

where A.StockFree - B.Allocated > 0 or (B.Allocated is null and A.StockFree > 0)

Note how I repeat the equasion in the brackets but leaving out B.Allocted as part of it. The problem with this technique is the SQL I have posted is massively simplified from the one I'm working on which uses B.Allocated about 6 times in different places (no it can't be optimised) and thus I'm worried that adding in a null test 6 timescould be slower than building a temp Breakdown table with a record for every stock record beforehand. I guess I won't know unless I test. Plus of course it makes the SQL even more messy and complicated.

I just wondered if any of you SQL heads have ever seen this behaviour of null causing Where to totally exclude a line even with a left outer join instead of just treating it like 0 or if this is perculiar to Borland's Local SQL. Actually I'm pretty sure I found this stinker out a long time ago and had to do a crappy workaround then too.

Thanks in advance if any of you understand what I'm on about and feel like commenting!

having

like he says "HAVING" is a very nice command to have.

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)

select * from "e:\server\data\AK_Stock" A
left outer join
"e:\server\data\AK_Breakdown" B
on A.StockCode = B.StockCode
where A.StockFree + coalesce(B.Allocated,0) > 0

I work with Oracle, so not sure if its the same as this, but it thinks of a NULL value as "unknown". Therefore could the issue be that the where clause is returning false because the answer to "is A.StockFree + Unknown > 0" is unknown (since for all we know the unknown value could be negative).

In Oracle there is a way round this, the NVL function, this can replace NULL values with anything you want. E.g:

nvl(B.Allocated, 0) - this would return the value of B.Allocated, unless it was null in which case it would return 0.

So you where clause would read:

where A.StockFree + nvl(B.Allocated,0) > 0

Ive just Googled for ANSI92 SQL and it looks like you may have to use the "coalesce" function instead of "nvl", looks like it does the same thing.

I know what "having" does (i.e. apply a condition to grouped data after the where clause has been applied and the data has been summarised), but it doesn't help as it can only be used with Group By and my SQL example doesn't use Group By (and there would be no point to it). Unless you meant use it in some other way but surely not?

Sounds like coalesce or nvl would be great but here's the bummer because Local SQL is a "subset" of ANSI92 it doesn't support it, arg. Thanks anyway.

Anyone know who the If statement wotks in SQL, I read that you can do "Select IF(null,0,allocated)" except that this fails miserably in Local SQL, it so damn limited!

case when B.Allocated is null then 0 else B.Allocated end

Yeah thanks Wayne, I'd love it if that worked but crappy Local SQL doesn't support Case ARG!

After looking at the subset of SQL I can understand the pain.

where A.StockFree + nvl(B.Allocated,0) > 0

maybe this will work:

where A.StockFree + cast(B.Allocated as float)>0

Does this work?

select * from "e:\server\data\AK_Stock" A
left outer join "e:\server\data\AK_Breakdown" B on A.StockCode = B.StockCode
group by A.StockCode
having A.StockFree + sum(B.Allocated) > 0

Wayne: I tried cast as integer to no avail and just tried float on the off chance but no luck.

octothorpe: That's not allowed because if you use group by you must have an aggregate function (e.g. SUM, MIN, MAX etc) on each numeric field, you can't just use *.

AL was correct in that null is treated as unknown not 0 and this is what causes my PAIN!

Anyway, In the end I'm going fot the build a Breakdown table with a record for every stock code. Of course the values will still be null so I'm gonna have to use an update SQL with this line Set Allocated=0 where Allocated is null. Then call the SQL at the top of the post an it'll work. Except in my case it's not that simple because the breakdown table has excluded certain values on purpose, but I'm gonna need to put records back in for the excluded stockcodes but with a 0 allocated value instead UNLESS the stockcode already exists, then I leave it alone, what a complicated piece of poo. Probably I'm gonna have to do the first SQL to build the breakdown with excluded values UNIONed to a 2nd SQL with the excluded values but with "(0) As Allocated" in the Select clause, then finally do another SQL on the resulting set to group it again in case there are duplicate stock codes ARG! This is gonna be slow. We'll just have to tell the clients to get faster PCs! Oh well, any more ideas, then hit me with em!

Oh yeah, I remember there being some weirdness with * not being allowed. I always remember being able to get around it by naming specific fields or tables.

If you really are required to use aggregate functions on all the numeric fields (that certainly isn't the case with mysql!) what would be the harm? You know the records are going to be joined 1:1 or 1:0.

If you don't use an aggregate function on numeric fields when useing a group by the results you get will be nonsensical!