May 5, 2011 at 3:11 am
Hi,
Is it possible to do as follows:
If Column1 = 0 then instead select the value from column2?
Like this:
SELECT CASE [Unit cost]
WHEN 0 THEN [Logistic Unit price]
END
FROM blablabla
or like this perhaps:
SELECT 'UnitCost' = CASE
WHEN [Unit cost] = 0 THEN [Logistic Unit price]
END
FROM blablabla
May 5, 2011 at 3:13 am
Yes, you can do it balalalalala
May 5, 2011 at 3:29 am
My problem is, that in this specific query the value is Null when it has to choose column2... (and the value is not null ;-))
May 5, 2011 at 3:33 am
Ahh... Got it working.. The problem was of course that I did not have an ELSE ... When the value <> 0 then Colounm1. 🙂
May 5, 2011 at 3:55 am
An alternative to CASE: -
--Build some test data since you didn't supply any
DECLARE @blablabla AS TABLE (UnitCost MONEY, UnitPrice MONEY)
INSERT INTO @blablabla
SELECT 0, 1.50
UNION ALL SELECT 1.12, 1.45
UNION ALL SELECT 1.53, 0
UNION ALL SELECT 0, 0.15
--Now the query
SELECT ISNULL(NULLIF(UnitCost,0),UnitPrice)
FROM @blablabla
May 5, 2011 at 4:59 am
Well that was even better...
May 5, 2011 at 9:58 am
agh100 (5/5/2011)
Well that was even better...
Well, that depends 😉
Best bet is to run some tests on your own data, or knock-up some test data to play with.
Try this to get you started -
DECLARE @blablabla AS TABLE (UnitCost MONEY, UnitPrice MONEY)
INSERT INTO @blablabla
SELECT TOP 1000000
CASE WHEN RAND(CHECKSUM(NEWID())) * 10 < 5
THEN 0
ELSE ROUND(RAND(CHECKSUM(NEWID())) * 100.00,2) END,
ROUND(RAND(CHECKSUM(NEWID())) * 100.00,2)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
--Check Data
SELECT * FROM @blablabla
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply