Post reply

Don't show null

  • July 26, 2011 at 7:08 pm

    #240584

    Hi All,

    I have a SQL script that uses select, CTE and a whole bunch of case statements.(see attachment link)

    The script is fine and returns everything I need, But it shows null when there is no data. I have datatypes datetime,int and varchar.

    Is there a way to show a empty or blank field rather than null for all these datatypes? Becuase the application I am uploading to doesn't like null.

  • July 26, 2011 at 7:37 pm

    #1358913

    Look at the ISNULL function ...

    http://msdn.microsoft.com/en-us/library/ms184325.aspx

    By the way many who might have answered your question with a tested procedure are hesitant to open attachments not knowing if there may be some detrimental item / script whatever within the attachment. So for better tested assistance post the sample data in the forum window.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 26, 2011 at 8:20 pm

    #1358917

    thanks for the code tip.

    Unfortunately isnull() forces you to declare a replacement of the same data type of the column.

    Invoice_Id(int, not null)

    Code: ISNULL(invoice_id, 0) returns a zero

    Code: ISNULL(invoice_ID,) is not accepted

    IssueDate(datetime, null)

    Code: ISNULL(issuedate, '') returns 1900:01:01 00:00:00

    Maybe I have to convert them all to strings?

  • July 27, 2011 at 9:40 am

    #1359405

    ringovski (7/26/2011)

    Maybe I have to convert them all to strings?

    NULL is the universal marker for "undefined" for all data types. Empty string is only valid for character types so the answer to your question is yes. If you want to deliver an empty string instead of NULL then you have to start with a character type and cast all columns in your select list before applying ISNULL.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply