how to convert this calculated column ACCESS in my sql server column?
([VratePctg] < 0.05) Or (Abs([Amount1]) < 1) Or (Abs([Amount2]) < 1)
Thank you
Asked
Active
Viewed 39 times
-1
-
that should still work in sql server – John Woo Feb 21 '18 at 13:51
-
1T-SQL does not have a boolean type; as such you cannot calculate `OR` into a value. You can, however, use `CASE WHEN
THEN 1 ELSE 0 END` to achieve the same. The rest of the syntax is already valid T-SQL, by happy coincidence. – Jeroen Mostert Feb 21 '18 at 13:54 -
@JeroenMostert better change the 1 and 0 into `convert(1, bit)` for type safety – Cleptus Feb 21 '18 at 13:57
-
ok and how would you write it? – sebyrock Feb 21 '18 at 13:58
-
please help me.. – sebyrock Feb 21 '18 at 14:03
-
1`ColumnName as CAST(CASE WHEN [VratePctg] < 0.05 Or Abs([Amount1]) < 1 Or Abs([Amount2]) < 1 THEN 1 ELSE 0 END As Bit)` – Zohar Peled Feb 21 '18 at 14:14
-
I tried but it does not work – sebyrock Feb 21 '18 at 14:17
-
"it does not work" is an empty phrase. You have to be more specific. – Zohar Peled Feb 21 '18 at 14:24
-
I tried do as you handed me: (CASE WHEN [VratePctg] < 0.05 Or Abs([Amount1]) < 1 Or Abs([Amount2]) < 1 THEN 1 ELSE 0 END ) , but accidentally with parentheses , sql server says that the syntax is incorrect – sebyrock Feb 21 '18 at 14:27
-
I'm trying a correction... – sebyrock Feb 21 '18 at 14:29
-
why -1 :( when I find the solution I write it – sebyrock Feb 21 '18 at 15:06
1 Answers
0
Your question lacks context, but here is a demo of a computed column with these conditions:
CREATE TABLE Demo
(
VratePctg decimal(5,2),
Amount1 int,
Amount2 int,
ComputedColumn AS CAST(CASE WHEN [VratePctg] < 0.05 Or Abs([Amount1]) < 1 Or Abs([Amount2]) < 1 THEN 1 ELSE 0 END As Bit)
)
Test:
INSERT INTO Demo(VratePctg, Amount1, Amount2) VALUES
(0.03, 2, 5),
(0.6, 7, 4),
(0.9, 0, 8),
(4.2, 9, 0)
SELECT *
FROM Demo
Results:
VratePctg Amount1 Amount2 ComputedColumn
0,03 2 5 True
0,60 7 4 False
0,90 0 8 True
4,20 9 0 True
Zohar Peled
- 79,642
- 10
- 69
- 121
-
the problem is that I had to calculate on two calculated columns, I had to replace the expression instead of the column name. Thank you – sebyrock Feb 22 '18 at 10:48