DTS and Null Values

Posted on 2005-05-06
Last Modified: 2013-11-30

I have a DTS package set up that is doing sevral equations.  I use CASE statements in a sub select and then have to Sum() some of the variables in the outer select (its kinda large and bulky so i dont want to post it unless i have to).

I have some values where part of the equation is Null, thus giving me a Null result, which is desired.  However, when i export the Null value i want something to show up in its place, weather it is a "Null" or a "0" i really don't care, i just dont want the absence of anything (which is what im getting now).

I cant use a case statement in the SQL because i am already Sum()'ing columns for my results.  I have messed around with the "Keep Null Values" option in DTS but weather it is checked or not i get blanks where the Null's are.

I am exporting to a fixed width text file.


Question by:Navicerts
    LVL 14

    Accepted Solution

    Have you tried using ISNULL(field, 'Null'), if you can't use it in yor SQL, wrap the entire query in a select

    Select Field1, ISNULL(Field2, 'Null') as Field2
    FROM (Your Query select statement goes here) as tmp
    LVL 7

    Author Comment

    i would be nesting my query for a third time if i did that, wanted to avoid it :(
    LVL 19

    Assisted Solution

    by:Melih SARICA
    if its not really important to use a fixed length text file.. try to use a delimeter..

    LVL 7

    Author Comment

    fixed length is important unfortunatly
    LVL 7

    Author Comment

    i guess the only way to do it would be to nest it again, thanks for the thoughts guys

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now