Sql-server – Combining nullif while checking if field is an integer with a case statement

azure-sql-databasecasenullsql server

As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some issues when trying to combine the following two business requirements:

If the value of the source varchar field is blank, then it will be set as NULL in the target tinyint field. For that I would normally use NULLIF.
If the field contains anything that could not be cast as a tinyint value (e.g. punctuation, letters, or a value higher than 255) then the value will be set as 255 in the target tinyint field. The rationale behind this is that the tinyint field would never have a 255 value unless there is an error — and it wouldn't be appropriate to have errors set as NULL or 0 because these are perfectly valid values and we wouldn't be able to differentiate between a valid entry and a mistake.

The problem I have is trying to combine NULLIF with some sort of case statement. I think that perhaps this is not the right approach. Any thoughts on this?

Best Answer

I believe Azure supports TRY_CAST.

(Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.)

Here are three examples that seem to demonstrate what you're after:

--Column is blank, then NULL
DECLARE @YourCol1 VARCHAR(10) = ' '

SELECT CASE 
        WHEN @YourCol1 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol1 AS TINYINT), 255)
        END AS YourCol1

--Column won't successfully CAST to TinyInt, then 255
DECLARE @YourCol2 VARCHAR(10) = 'abc'

SELECT CASE 
        WHEN @YourCol2 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol2 AS TINYINT), 255)
        END AS YourCol2

--Column successfully CASTS to TinyInt, then actual value
DECLARE @YourCol3 VARCHAR(10) = '21'

SELECT CASE 
        WHEN @YourCol3 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol3 AS TINYINT), 255)
        END AS YourCol3

Related Solutions

How to select data with a case statement and group by

The problem is that you can't use the alias Stops in the GROUP BY. In your query when you are using the GROUP BY it is using the individual values for each row. You'd need to use a subquery to get the result:

select stops, count(*) Total
from  
(
  select 
    CASE 
      when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
      ELSE Stops
    END as Stops
  from yourtable
) d
group by stops;

See SQL Fiddle with Demo.

Or if you don't want to use a subquery, then you could repeat the CASE expression in the GROUP BY:

select 
  CASE 
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
    ELSE Stops
  END as Stops,
  count(*)  as Total
from yourtable
group by 
  CASE 
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop' 
    ELSE Stops
  END

See SQL Fiddle with Demo

Display non-null and null count using case statement

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:

Sample Data:

CREATE TABLE #Example
(
    Eid integer NULL
);

INSERT #Example
    (Eid)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (NULL),
    (NULL),
    (NULL),
    (NULL),
    (NULL);

My idea revolves around the idea that COUNT(column) does not count NULLs, whereas COUNT(*) does:

SELECT 
    [Number] = COUNT(E.Eid), 
    [Null] = COUNT(*) - COUNT(E.Eid)
FROM #Example AS E;

Output 1:

+--------+------+
| Number | Null |
+--------+------+
|      5 |    5 |
+--------+------+

If the specific arrangement of rows and columns is important, I would UNPIVOT:

WITH Previous AS
(
    -- Same query as above
    SELECT 
        [Number] = COUNT(E.Eid), 
        [Null] = COUNT(*) - COUNT(E.Eid)
    FROM #Example AS E
)
SELECT
    U.Eid,
    U.Counts
FROM Previous
UNPIVOT
(
    Counts FOR Eid IN ([Number], [Null])
) AS U;

Output 2:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

Using `CASE`

SELECT 
    C.Name, 
    Counts = COUNT(*) 
FROM 
(
    SELECT 
        Name =
            CASE 
                WHEN E.Eid IS NULL 
                THEN 'Null' 
                ELSE 'Number' 
            END 
    FROM #Example AS E
) AS C
GROUP BY
    C.Name;

Output 3:

+--------+--------+
|  Name  | Counts |
+--------+--------+
| Null   |      5 |
| Number |      5 |
+--------+--------+

Edit:

This is the query form Babu ended up using:

select 
    count(*) as cnt, 
    case 
        when smpth is null 
        then 'NULL' 
        else 'NUMBER' 
    end numbertest 
from employees 
group by 
    case 
        when smpth is null 
        then 'NULL' 
        else 'NUMBER' 
    end numbertest