Sql-server – How implement CASE statement in query

sql serversql-server-2008

I stuck in here.

How to implement:

CASE
WHEN m.MAXDATE is not null THEN '*'
WHEN m.MAXDATE IS NULL THEN ' '
END

In query:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE] 
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

I've checked few website in order to find the answer, but it's giving me errors in every variant.

More details:

Query is:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE] 
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

This is a result:

SMITH   1980-12-17 00:00:00.000 NULL
ALLEN   1981-02-20 00:00:00.000 NULL
WARD    1981-02-22 00:00:00.000 NULL
JONES   1981-04-02 00:00:00.000 NULL
MARTIN  1981-09-28 00:00:00.000 NULL
BLAKE   1981-05-01 00:00:00.000 NULL
CLARK   1981-06-09 00:00:00.000 NULL
SCOTT   1982-12-09 00:00:00.000 NULL
KING    1981-11-17 00:00:00.000 NULL
TURNER  1981-09-08 00:00:00.000 NULL
ADAMS   1983-01-12 00:00:00.000 1983-01-12 00:00:00.000
JAMES   1981-12-03 00:00:00.000 NULL
FORD    1981-12-03 00:00:00.000 NULL
MILLER  1982-01-23 00:00:00.000 NULL

I want to just put '*' instead of date in last column for ADAMS, and space ' ' where its NULL.

Best Answer

Your syntax for the CASE expression is correct (it's an expression, not a statement!) You can simplify it slightly by removing the second WHEN:

CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END

and the query:

SELECT Ename, Hiredate, 
       CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END AS MAXDATE
FROM EMP
  LEFT JOIN (SELECT max(Hiredate) AS MAXDATE 
             FROM EMP) m
  ON EMP.HIREDATE = m.MAXDATE ;

And you don't really need the self-join. You could have the same result with a window aggregate function:

SELECT Ename, Hiredate, 
       CASE WHEN HIREDATE = max(HIREDATE) OVER () 
           THEN '*' ELSE '' END AS MAXDATE
FROM EMP ;

Related Solutions

Sql-server – Check Constraint with Case Statement

Check constraints can only be used to verify if the data falls within a given set of parameters. The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client.

To test this, I created your table, along with your proposed check constraint, and inserted several rows of test data:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

enter image description here

Results:

enter image description here

When attempting to insert 2 and 3 (or in fact anything other than 1 or >4) the constraint will return false, indicating to SQL Server to not perform the insert, and return the error messages seen above.

Here is an example of how you could accomplish this using a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint 
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

enter image description here

Ensure you remove the constraint from the table before trying to insert data, since it may conflict with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;

SQL Server 2008 – Is ELSE 0 Implied in COUNT CASE WHEN Statement?

Simply enough in the first case you are counting 1s & NULLs. (NULL is returned if none of the conditions in the CASE statement match and there is no ELSE clause.) NULLs don't get counted. In the second case 1's and 0's. 0's can be counted.

Quick example:

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

Output:

Best Answer

Related Solutions

Sql-server – Check Constraint with Case Statement

SQL Server 2008 – Is ELSE 0 Implied in COUNT CASE WHEN Statement?

Related Question