Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to avoid technically the null values while concatenating columns Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 9:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:20 PM
Points: 1,615, Visits: 2,116
The problem usually shows up when you are trying to export to a text file, and the presence of any one NULL value throws the entire output record into NULL status, and depending on exactly how poorly the process was designed, it could be a problem such as missing records in the output file, or data showing up in the wrong location within the output data record because they account for everything except the empty string. The original poster has yet to come back with feedback beyond the apparent need to deal with 4 to 6 independent fields. Why that makes a difference is thus still unknown.

Hey manibad - do you have a solution now, or do you need more assistance? Let us know...

Phil Parkin (5/6/2013)
OK, I see what you are getting at, thanks. I still cannot see why that causes a formatting problem when outputting though.


Steve
(aka sgmunson)

Internet ATM Machine
Post #1449763
Posted Monday, May 6, 2013 2:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 87, Visits: 143
sgmunson (5/6/2013)
I'm at the point of making an assumption about what the poster meant by their most recent post. Given the technique of concatenating a series of ISNULL functions whose parameters are all strings or casted or converted into strings, it's not that difficult to eliminate a null value from causing a problem in concatenation, and you just encapsulate the entire concatenation of ISNULLs within a NULLIF and you can preserve a NULL value for the case when all the fields are NULL as opposed to just one or more but not all. The assumption I make is that when they mention the idea of having 4 to 6 fields to deal with, that they mean that they need to concatenate more than just two fields. Whether that assumption holds true or not is something the poster would have to resolve for us by posting again. Does that explain?

Phil Parkin (5/6/2013)
sgmunson (5/6/2013)
The number of fields that can be included in the query's use of ISNULL and NULLIF is not limited to two. You can just keep concatenating an ISNULL function, and then NULLIF the entire concatenation, and thus have as many fields as you need. They just all need to be strings, or alternately, CAST-ed or CONVERT-ed to strings.


Steve - you sound like you understand the problem - can you explain it, because I still cannot comprehend properly what the issue is?


"The assumption I make is that when they mention the idea of having 4 to 6 fields to deal with, that they mean that they need to concatenate more than just two fields. "the assumption what you has is perfect.i have the same situation to be dealt.Moreover i am rookie to SSIS and so i just came to know about the NULLIF function..and i will work on it...to fix my issues.
Post #1449880
Posted Monday, May 6, 2013 11:15 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
manibad (5/6/2013)
sgmunson (5/6/2013)
I'm at the point of making an assumption about what the poster meant by their most recent post. Given the technique of concatenating a series of ISNULL functions whose parameters are all strings or casted or converted into strings, it's not that difficult to eliminate a null value from causing a problem in concatenation, and you just encapsulate the entire concatenation of ISNULLs within a NULLIF and you can preserve a NULL value for the case when all the fields are NULL as opposed to just one or more but not all. The assumption I make is that when they mention the idea of having 4 to 6 fields to deal with, that they mean that they need to concatenate more than just two fields. Whether that assumption holds true or not is something the poster would have to resolve for us by posting again. Does that explain?

Phil Parkin (5/6/2013)
sgmunson (5/6/2013)
The number of fields that can be included in the query's use of ISNULL and NULLIF is not limited to two. You can just keep concatenating an ISNULL function, and then NULLIF the entire concatenation, and thus have as many fields as you need. They just all need to be strings, or alternately, CAST-ed or CONVERT-ed to strings.


Steve - you sound like you understand the problem - can you explain it, because I still cannot comprehend properly what the issue is?


"The assumption I make is that when they mention the idea of having 4 to 6 fields to deal with, that they mean that they need to concatenate more than just two fields. "the assumption what you has is perfect.i have the same situation to be dealt.Moreover i am rookie to SSIS and so i just came to know about the NULLIF function..and i will work on it...to fix my issues.


I now know why I was confused - this problem has nothing to do with SSIS, it's merely a source/selection problem.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1449978
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse