SQL Server: when Else is case it is showing 0 as default in CASE clause

user_10850062 0
Viewed 255

This query is run on AdventureWorks2014.Sales.SalesOrderDetail table.

My query: in the CASE clause I have clearly stated to show - when it is the ELSE case. This query removes the repeating SalesOrderID and after 1st time shown it should be replaced with "-" this character.

SELECT 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID ASC) = 1 THEN SalesOrderID ELSE '-' END  AS SalesOrderID,
    R.ProductID,
    R.OrderQty,
    R.UnitPrice,
    R.LineTotal

FROM AdventureWorks2014.Sales.SalesOrderDetail AS R

currently showing 0 even though i said in the ELSE to show me -

enter image description here

sql-server
3 Answers

Below query will solve your issue

 SELECT 
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID ASC) = 1 THEN CONVERT(varchar(10),SalesOrderID) ELSE '-' END  AS SalesOrderID,
        R.ProductID,
        R.OrderQty,
        R.UnitPrice,
        R.LineTotal

    FROM AdventureWorks2014.Sales.SalesOrderDetail AS R
user_19362310

I think your are getting 0 because data type of SalesOrderId is int. If it will be varchar then it will come -. Try this below query.

Create table Orders (SalesOrderId Varchar(5), ProductId int, OrderQty int, UnitPrice decimal,
LineTotal decimal)

insert into Orders
values
(43659, 776, 1, 2024.94, 2024.99),
(43659, 777, 3, 2024.94, 2024.99),
(43659, 778, 1, 2024.94, 2024.99)

SELECT 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID ASC) = '1' 
        THEN SalesOrderID 
    ELSE '-' END  AS SalesOrderID,
    R.ProductID,
    R.OrderQty,
    R.UnitPrice,
    R.LineTotal

FROM Orders AS R

You can find the live demo Live Demo Here in both format of datatype int and varchar.

Without changing datatype you can achieve it using query as shown below

SELECT 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID ASC) = '1'  
        THEN Convert(Varchar(5), SalesOrderID)
    ELSE '-' END  AS SalesOrderID,
    R.ProductID,
    R.OrderQty,
    R.UnitPrice,
    R.LineTotal
FROM OrdersInt AS R
user_105325000

Thanks for your input i made it work

SELECT CASE
            WHEN ROW_NUMBER() OVER(PARTITION BY R.SalesOrderID ORDER BY SalesOrderID ASC) = 1 THEN SalesOrderID 
        ELSE ' ' END AS SO_ID,
        R.ProductID,
        R.OrderQty,
        R.UnitPrice,
        R.LineTotal

FROM(SELECT 
        CONVERT(VARCHAR(15),S.SalesOrderID) AS SalesOrderID,
        S.ProductID,
        S.OrderQty,
        S.UnitPrice,
        S.LineTotal
    FROM AdventureWorks2014.Sales.SalesOrderDetail AS S) AS R

Sample data

enter image description here

user_108500620
Related