locked
Null Valie RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below Case Condition i want SPACE to be displayed in place of null. First 2 fields are numeric , Third one is date

    	CASE WHEN RR = 1 THEN DocEntry ELSE null END Doc,
    	CASE WHEN RR = 1 THEN DocNum ELSE null END 'Inv.',
    	CASE WHEN RR = 1 THEN DocDate ELSE '' END 'Date',

    Thanks

    Friday, May 15, 2020 9:14 AM

Answers

  • User-719153870 posted

    Hi jsshivalik,

    Assume this question is from Query.

    I am doing like this but it is displaying 0 in else part . I want SPACE.

    It shows 0 instead of blank space because the DocEntry field is int type which does not support ‘’ as its value, so it automatically convert '' to 0 which is the default value for null ''.

    To achieve the requirement, as @mgebhard mentioned, please use the CONVERT() function to convert your fileds type to string(varchar, nvarchar etc).

    Please check below demo:

    create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
    insert into AAA values(1,30,'2019-05-04','C',7,'2019-05-04','ABC',200,27400),
    (2,31,'2019-05-04','C',12,'2019-05-04','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
    (3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)
    
    select * from AAA
    SELECT
    	CASE WHEN RR = 1 THEN convert(varchar(10),id) ELSE '' END id,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END age,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE '' END itype,
    	CASE WHEN RR = 1 THEN convert(varchar(10),anum) ELSE '' END anum,
    	CASE WHEN RR = 1 THEN convert(varchar(10),edate) ELSE '' END edate,
    	CASE WHEN RR = 1 THEN name ELSE '' END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM AAA) SUBQUERY1
    drop table AAA

    result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 18, 2020 2:34 AM

All replies

  • User475983607 posted

    Use ISNULL() or COALESCE()

    CASE WHEN RR = 1 THEN ISNULL(DocEntry, '') ELSE null END Doc,
    CASE WHEN RR = 1 THEN ISNULL(DocNum, '') ELSE null END 'Inv.',
    CASE WHEN RR = 1 THEN ISNULL(DocDate, '') ELSE '' END 'Date',

    Friday, May 15, 2020 10:09 AM
  • User-797751191 posted

    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

    Friday, May 15, 2020 11:42 AM
  • User452040443 posted

    Hi,

    Space is an alphanumeric character, therefore incompatible with numeric or date columns.

    The closest in the query for your case is null, and if necessary you should replace it with space in your application / presentation.

    Friday, May 15, 2020 12:08 PM
  • User475983607 posted

    jsshivalik

    I am doing like this but it is displaying 0 in else part . I want SPACE.

    Anyway, the fix is trivial at best.   Use the CONVERT() function to convert the integers and the date to a string.  Please read the reference documentation.

    If you do not want to convert the type to strings then fix your presentation! 

    Friday, May 15, 2020 12:36 PM
  • User-719153870 posted

    Hi jsshivalik,

    Assume this question is from Query.

    I am doing like this but it is displaying 0 in else part . I want SPACE.

    It shows 0 instead of blank space because the DocEntry field is int type which does not support ‘’ as its value, so it automatically convert '' to 0 which is the default value for null ''.

    To achieve the requirement, as @mgebhard mentioned, please use the CONVERT() function to convert your fileds type to string(varchar, nvarchar etc).

    Please check below demo:

    create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
    insert into AAA values(1,30,'2019-05-04','C',7,'2019-05-04','ABC',200,27400),
    (2,31,'2019-05-04','C',12,'2019-05-04','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
    (3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)
    
    select * from AAA
    SELECT
    	CASE WHEN RR = 1 THEN convert(varchar(10),id) ELSE '' END id,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END age,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE '' END itype,
    	CASE WHEN RR = 1 THEN convert(varchar(10),anum) ELSE '' END anum,
    	CASE WHEN RR = 1 THEN convert(varchar(10),edate) ELSE '' END edate,
    	CASE WHEN RR = 1 THEN name ELSE '' END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM AAA) SUBQUERY1
    drop table AAA

    result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 18, 2020 2:34 AM