November 24, 2015 at 11:17 am
I'm trying to return the number of pieces from a table where the piece count can be NULL, 0, or any whole number 1-999.
I need my query results to:
- change all NULL values to a '1'
- change all '0' values to a '1'
- make NO other changes, essentially letting all other values pass through untouched
The query below produces the desired results with the NULL and '0' values, but everything else then turns into a NULL. I can't seem to get the good values (1-999) to pass through the statement and keep their original value.
Here's the section of the query:
...
case when o.Pieces = '0' then '1' -- Change all ZERO piece orders to 1 piece orders
when o.Pieces IS NULL then '1' -- Change all NULL piece counts to 1
end as 'Pieces',
...
November 24, 2015 at 11:23 am
rhaberkorn (11/24/2015)
I'm trying to return the number of pieces from a table where the piece count can be NULL, 0, or any whole number 1-999.I need my query results to:
- change all NULL values to a '1'
- change all '0' values to a '1'
- make NO other changes, essentially letting all other values pass through untouched
The query below produces the desired results with the NULL and '0' values, but everything else then turns into a NULL. I can't seem to get the good values (1-999) to pass through the statement and keep their original value.
Here's the section of the query:
...
case when o.Pieces = '0' then '1' -- Change all ZERO piece orders to 1 piece orders
when o.Pieces IS NULL then '1' -- Change all NULL piece counts to 1
end as 'Pieces',
...
Quick suggestion, post the relevant DDL (create table), sample data as an insert statement and the desired results!
😎
November 24, 2015 at 11:26 am
You've only specified values in the CASE for the conditions where the value is NULL or '0'. You would need to add an ELSE to grab the rest of the possibilities. Right now those possibilities don't have a matching condition in the CASE statement, so they will return NULL.
Something like this:
CASE
WHEN o.pieces = '0' THEN '1' -- Change all ZERO piece orders to 1 piece orders
WHEN o.pieces IS NULL THEN '1' -- Change all NULL piece counts to 1
ELSE o.pieces
END AS 'Pieces'
On a side-note, are numeric values being stored as strings (wondering because of the single quotes)?
Cheers!
November 24, 2015 at 12:02 pm
A shorter version with sample data:
USE tempdb
GO
CREATE TABLE #Test( pieces int);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
INSERT INTO #Test
SELECT n - 500
FROM cteTally
UNION ALL
SELECT NULL;
SELECT o.pieces, --To show value
ISNULL( NULLIF( o.pieces, 0), 1) newpieces
FROM #Test o
WHERE (o.pieces BETWEEN 0 AND 999 OR o.pieces IS NULL)
ORDER BY pieces;
GO
DROP TABLE #Test;
November 24, 2015 at 12:23 pm
Thank you very much for your response!! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply