?
Solved

Manipulating empty (NULL) field result in a LEFT OUTER JOIN

Posted on 2005-04-03
8
Medium Priority
?
2,126 Views
Last Modified: 2008-02-01
Hi,

I have a complex query using a "LEFT OUTER JOIN" ... I'd like to prevent having to manipulate an empty (NULL) field result on programming level ... I'd prefer to replace possible NULL results with a simple '0' (of type nvarchar) ...

... my question is: is there a way to do it with the following sproc ... or will I have to manipulate the data on programming level?


create procedure GetAllProducts
as
SELECT
      dbo.Products.*,
      dbo.Categories.topcatID AS topcatID,
      dbo.Manufacturers.manuName AS part1Man,
      Manufacturers_1.manuName AS part2Man,
        dbo.Clients.clientName AS cName,
      dbo.Clients.clientMail AS cMail,
      dbo.Clients.clientWeb AS cWeb,
      dbo.Clients.clientPhone AS cPhone,
        dbo.Clients.clientFax AS cFax,
      dbo.Clients.clientContact AS cContact,
      dbo.Clients.clientContinent AS cContinent,
      dbo.Clients.clientCountry AS cCountry,
        dbo.Clients.clientRegion AS cRegion,
      dbo.Clients.clientZIP AS cZIP,
      dbo.Clients.clientCity AS cCity,
      dbo.Clients.clientStreet AS cStreet,
        dbo.Clients.clientGender AS cGender,
      dbo.Models.mnName AS ModelName
FROM
      dbo.Products
INNER JOIN
        dbo.Categories
ON
      dbo.Products.ogID = dbo.Categories.CategoryID
INNER JOIN
        dbo.Manufacturers
ON
      dbo.Products.ModelBrand = dbo.Manufacturers.manuID
INNER JOIN
        dbo.Clients
ON
      dbo.Products.prodAnbieter = dbo.Clients.clientID
INNER JOIN
        dbo.Models
ON
      dbo.Manufacturers.manuID = dbo.Models.manuID AND
      dbo.Products.ModelNameID = dbo.Models.mnID
LEFT OUTER JOIN
        dbo.Manufacturers Manufacturers_1
ON
      dbo.Products.ModelBrand2 = Manufacturers_1.manuID


Best regards,
Raisor
0
Comment
Question by:Ralf Klatt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
stefan73 earned 1000 total points
ID: 13691287
Hi Raisor,
> I'd prefer to replace possible NULL results with a simple '0'
Sure, you can use NVL(some_column,'0') in the select.

Cheers!

Stefan
0
 
LVL 15

Author Comment

by:Ralf Klatt
ID: 13691309
Hi,

This is what I've received:
Server: Msg 195, Level 15, State 10, Line 5
'NVL' is not a recognized function name.


Best regards,
Raisor
0
 
LVL 2

Expert Comment

by:Bearsel
ID: 13691328
When running across null in numeric fields you can use coalesce to force the null value to anything.  

Select
          Field1
         , coalesce( FieldAmount, 0 )  -- if Field amount is null then 0

From
          Your Table
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 15

Author Comment

by:Ralf Klatt
ID: 13691333
Hi,

Well, NVL wasn't the right solution -> ISNULL is the appropriate syntax!

This is how it works:

create procedure GetAllProducts
as
SELECT
      dbo.Products.*,
      dbo.Categories.topcatID AS topcatID,
      dbo.Manufacturers.manuName AS part1Man,
      ISNULL(Manufacturers_1.manuName, '0') AS part2Man,
        dbo.Clients.clientName AS cName,
      dbo.Clients.clientMail AS cMail,
      dbo.Clients.clientWeb AS cWeb,
      dbo.Clients.clientPhone AS cPhone,
        dbo.Clients.clientFax AS cFax,
      dbo.Clients.clientContact AS cContact,
      dbo.Clients.clientContinent AS cContinent,
      dbo.Clients.clientCountry AS cCountry,
        dbo.Clients.clientRegion AS cRegion,
      dbo.Clients.clientZIP AS cZIP,
      dbo.Clients.clientCity AS cCity,
      dbo.Clients.clientStreet AS cStreet,
        dbo.Clients.clientGender AS cGender,
      dbo.Models.mnName AS ModelName
FROM
      dbo.Products
INNER JOIN
        dbo.Categories
ON
      dbo.Products.ogID = dbo.Categories.CategoryID
INNER JOIN
        dbo.Manufacturers
ON
      dbo.Products.ModelBrand = dbo.Manufacturers.manuID
INNER JOIN
        dbo.Clients
ON
      dbo.Products.prodAnbieter = dbo.Clients.clientID
INNER JOIN
        dbo.Models
ON
      dbo.Manufacturers.manuID = dbo.Models.manuID AND
      dbo.Products.ModelNameID = dbo.Models.mnID
LEFT OUTER JOIN
        dbo.Manufacturers Manufacturers_1
ON
      dbo.Products.ModelBrand2 = Manufacturers_1.manuID


... where ... ISNULL(Manufacturers_1.manuName, '0') AS part2Man ... does what I was looking for ...
... thanks ... at least ;-)) ... for pointing me to the right direction!


Best regards,
Raisor
0
 
LVL 12

Expert Comment

by:stefan73
ID: 13691368
hmmm, does SQL server understand the "decode" function?

nvl(column,null_default) is just shorthand for decode(column,NULL,null_default,column)
0
 
LVL 15

Author Comment

by:Ralf Klatt
ID: 13691791
Hi,

@stefan73 ... the ISNULL approach lead me to the solution I was looking for ... I've found it at google using the search phrases "replace NULL database function sql server" ... http://www.akadia.com/services/dealing_with_null_values.html ... the search phrases I entered were a (just to me but not obvious!) logical result of what you'd offered to me as a solution ... so, to give you points for your answer seemed reasonable to me ... that it's "only" a C Grade has it's reason because I had to find the approach I was looking for by myself ... I'm really sorry if that's a problem for you!


Best regards,
Raisor
0
 
LVL 12

Expert Comment

by:stefan73
ID: 13691869
Why don't you get your points refunded, then?
0
 
LVL 15

Author Comment

by:Ralf Klatt
ID: 13692153
Hi,

===>"Why don't you get your points refunded, then?"<===

Well, infact you've offered a Informix/Oracle solution:

NVL Function:
The NVL (null value) function allows the SQL statement to return more meaningful values when it encounters a null value in the column. With the NVL function, if the first argument has a non-null value, this non-null value is returned. If the first argument is null, the second value is returned.

DECODE Function:
The DECODE statement performs an action similar to the CASE expression. DECODE has been around in Oracle for years; it allows the SQL statement to return different results based upon values found in a column.

While there's some difference between NVL function and DECODE function (according to your statement "hmmm, does SQL server understand the "decode" function?") ... the "translation" from the informix-/oracle-based "NVL" function to SQL Server is ISNULL.

I should have added the fact that I'm using MS SQL Server in my original question, that is my fault!

To finally (!) answer your latest question "Why don't you get your points refunded, then?" ...

... I repeat: You're answer has pointed me to the right direction -> nothing more and nothing less! -> if I was using Perl, C, Informix or Oracle this would have resulted in an A-Grade for your answer ... but, as this was a question on MS SQL Server there was no way of using NVL or DECODE -> so, you pointed me to the fact that I'm in need of a similar SQL Server function <- that I finally found using Google!

stefan73, if you're feeling that I was unfair or that I wasn't allowed to accept your answer (according to EE guidelines) and if you think that a moderator should have a look at this, then please feel free to open a question at http://www.experts-exchange.com/Community_Support/Expert_Input/ ...


Best Regards
Raisor
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question