• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2138
  • Last Modified:

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

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
Ralf Klatt
Asked:
Ralf Klatt
  • 4
  • 3
1 Solution
 
stefan73Commented:
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
 
Ralf KlattPrincipal ConsultantAuthor Commented:
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
 
BearselCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ralf KlattPrincipal ConsultantAuthor Commented:
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
 
stefan73Commented:
hmmm, does SQL server understand the "decode" function?

nvl(column,null_default) is just shorthand for decode(column,NULL,null_default,column)
0
 
Ralf KlattPrincipal ConsultantAuthor Commented:
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
 
stefan73Commented:
Why don't you get your points refunded, then?
0
 
Ralf KlattPrincipal ConsultantAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now