Hi
I am doing like this but it is displaying 0 in else part . I want SPACE.
Both fields are of numeric type
CASE WHEN RR = 1 THEN DocEntry ELSE '' END Doc,
CASE WHEN RR = 1 THEN DocNum ELSE '' END 'Inv',
Thanks
You are returning a numeric value - and '' is not a numeric value so it will be converted to the corresponding numeric value of 0. You can either return a NULL value or convert the DocEntry to a string.
CASE WHEN RR = 1 THEN DocEntry END -- return a NULL
CASE WHEN RR = 1 THEN CAST(DocEntry AS varchar(20)) ELSE '' END --return a string
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 17, 2020 at 6:34 am
Hi Jeffrey
In case of Date field wht is to be done.
Thanks
May 17, 2020 at 2:48 pm
The same thing - either return a NULL or convert the date into a string. Depending on the downstream systems that will be using this - it generally is much better to return the actual data type and a NULL value for undefined.
For example - if sending the data to SSRS or SSIS then sending the data as a string eliminates the ability to format that data appropriately (as needed) in the report or export. If the column is defined as a date/time - then in the report you can format it as a date only, a time only, a date/time - and you can use regional settings. If the column is defined as a string then you can only display the data in that format.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply