NULLs won't show up in VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NULLs won't show up in VIEW

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-30 : 11:05:22

I'm running a VIEW against a MASTER base table. The VIEW does several calculations to create new fields on the fly (division, subtraction, LOG10).

To do the calculations noted above, I'm using four lookup tables which contain IDs that match up with specific field values in the MASTER table. Once an ID is found in the lookup table, various other values are pulled from other fields in the lookup, as below:

ID/Ratio/Score

0/1/25
1/2/50

and the numbers are crunched to create the new fields in the VIEW.

I have 921 total records in my MASTER table. When I perform the calculations noted above for the VIEW I get only 880 records returned. I notice that records in the MASTER table that have NULL entries in the field that is is matched to the ID field in the LOOKUP table are simplY skipped and not returned.

Is there an easy way to return all 921 records in the VIEW even if the key column value is NULL? I suppose I need to shoehorn ISNULL into my SELECT statement somewhere but I can't see where.

Thanks,

steelkilt.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-30 : 11:11:43

How are you going to match an ID value in the lookup table if the master table has nulls? Can you provide the SQL for the view you have now? And also the structures of all of the tables used, even the ones not in the view?

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-30 : 11:19:18

Rob,

I wondered the same thing! How about a NULL in the ID column ;-)

Here's the code for the VIEW:

SELECT HD_the_end.school,
HD_the_end.WOSTCorrect,

HD_the_end.WOSTCorrect / 128 AS WOSTPctCorrect,

BOE_LOOKUP.BOE_ZSCORE - BOC_LOOKUP.BOC_ZSCORE AS Dsig,

BOC_LOOKUP.BOC_ORD / BOE_LOOKUP.BOE_ORD AS Betasig,

LOG10(BOC_LOOKUP.BOC_ORD / BOE_LOOKUP.BOE_ORD)
AS LogBetasig,

COE_LOOKUP.COE_ZSCORE - COC_LOOKUP.COC_ZSCORE AS Ddist,

COC_LOOKUP.COC_ORD / COE_LOOKUP.COE_ORD AS BetaDist,

LOG10(COC_LOOKUP.COC_ORD / COE_LOOKUP.COE_ORD)
AS LogBetaDist

FROM HD_the_end, BOE_LOOKUP, BOC_LOOKUP, COE_LOOKUP, COC_LOOKUP

WHERE HD_the_end.sigilanceoverallerror = BOE_lookup.BOE_id AND

HD_the_end.sigilanceoverallcorrect = BOC_lookup.BOC_id AND

HD_the_end.distractabilityoverallerror = COE_lookup.COE_id AND

HD_the_end.distractabilityoverallcorrect = COC_lookup.COC_id

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-10-17 : 13:48:05

Just getting around to this now...

Lookup table looks like this:

ID/value1/value2/value3

0 3 5 6
1 3 6 6

My SELECT query is using an "anchor" field, we'll call it CORE_ID, already in the base table, which has a value that corresponds to the ID column in the lookup table above.

Once the ID is found in the lookup table, we go across and grab various values, use them to do calculations, then feed the results of these calculations to newly created fields in the VIEW.

My problem arises when the entry for CORE_ID in the base table is NULL. Obviously, any records containing NULL values for CORE_ID will not show up in the VIEW results because a NULL value does not correspond to a valid value in the lookup table.

I have skirted the issue thusfar by asking the data consumers to decide on a value that marks the CORE_ID field as "unfilled" and to use that value when necessary. I list this value in the lookup with NULL entries in the adjacent columns. The result is that I get all my records returned.

I don't like this solution because it means more legwork when searching on NULL values and it basically means I have real NULLs and synthetic NULLs (i.e. the placeholder value).

Does anyone know of a way to tell SQL to include all records in the answer table, even when the CORE_ID has a NULL entry?

I guess something like

If CORE_ID IS NULL then lookup.ID = [placeholder value]

Thanks.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-17 : 19:18:56

What you have here is old style joins. If you move to the new style join syntax, I think most of your problems are solved once you figure out what to do with the select statement.

I'll help you out with the FROM clause, but you'll have to figure out the select statement on your own. My advice is look up ISNULL in the transact sql refence in the books online.


FROM HD_the_end HD LEFT JOIN BOE_LOOKUP BOE
  ON (HD.sigilanceoverallerror = BOE.BOE_id)
  LEFT JOIN BOC_LOOKUP BOC
  ON (HD.sigilanceoverallcorrect = BOC.BOC_id)
  LEFT JOIN COE_LOOKUP COE
  ON (HD.distractabilityoverallerror = COE.COE_id)
  LEFT JOIN COC_LOOKUP COC
  ON (HD.distractabilityoverallcorrect = COC.COC_id)

The idea behind an outer join (ie. LEFT, RIGHT, or FULL) is that records that don't have a matching record, instead have nulls inserted for the other columns from the other tables instead of being discarded. What differs is which records that don't match or kept. Look in the Books Online for more information.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-10-18 : 13:15:58

Thanks for the tip. Interestingly, and annoyingly, SQL converted my old style WHERE syntax automatically to INNER JOINS when I saved my original VIEW.

I went back to my original old-style code and inserted the following (example code) into the WHERE clause:

WHERE

ISNULL (HD_the_end.sigilanceoverallerror, 99) = BOE_lookup.BOE_id

and all is well.

Thx again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 13:30:18

quote:
Interestingly, and annoyingly, SQL converted my old style WHERE syntax automatically to INNER JOINS when I saved my original VIEW.

You really should try to use the JOIN syntax as much as possible. The WHERE style will work for INNER JOINs, but not for outer *= syntax, which is going away. Since the semantics differ it makes more sense (to me at least) to be consistent with the syntax. There is also less likelihood of optimization quirks if the JOIN and WHERE logic are separated from each other.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources