IIF with Multiple Case Statements for Computed Column
I'm trying to add this as a Formula (Computed Column) but I'm getting an error message saying it is not valid.
Can anyone see what is wrong with the below formula?
IIF ( select * from Config where Property = 'AutomaticExpiry' and Value = 1, case when [ExpiryDate] IS NULL OR sysdatetimeoffset()<[ExpiryDate] then 1 else 0 end, case when [ExpiryDate] IS NULL then 1 else 0 end )
From BOL: ALTER TABLE computed_column_definition
computed_column_expression Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or include an alias data type.
Overview of SQL IIF Statement, We use the following format to write Case statement logic in SQL queries. SELECT CASE IIF statement. We can use Nested SQL IIF statement as well to specify multiple conditions Example 5: SQL IIF statement with a table column In the actual execution plan, look at the properties of Compute Scalar. Example 5: SQL IIF statement with a table column. In the previous examples, we either specify values directly in the IIF statement or specified variables for it. Usually, we want to use it with the existing data in a SQL table. We can use the IIF statement with a table column as well.
Wrap the login in function. Something like this:
CREATE FUNCTION [dbo].[fn_CustomFunction] ( @ExpireDate DATETIME2 ) RETURNS BIT AS BEGIN; DECLARE @Value BIT = 0; IF EXISTS(select * from Config where Property = 'AutomaticExpiry' and Value = 1) BEGIN; SET @Value = IIF (sysdatetimeoffset()< @ExpireDate, 1, 0) RETURN @value; END; RETURN IIF(@ExpireDate IS NULL, 1, 0); END; GO --DROP TABLE IF EXISTS dbo.TEST; CREATE TABLE dbo.TEST ( [ID] INT IDENTITY(1,1) ,[ExpireDate] DATETIME2 ,ComputeColumn AS [dbo].[fn_CustomFunction] ([ExpireDate]) ) GO INSERT INTO dbo.TEst (ExpireDate) VALUES ('2019-01-01') ,('2018-01-01') ,(NULL); SELECT * FROM dbo.Test;
SQL SERVER, With the help of CASE statement, now I can write conditional logic. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. -- Add Computed Column with Case Statement. From BOL: ALTER TABLE computed_column_definition. computed_column_expression Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the
Youre trying to do something, what we're not quite sure - you've made a classic XY problem mistake.. You have some task, like "implement auto login expiry if it's on in the prefs table" and you've devised this broken solution (use a computed column/IIF) and have sought help to know why it's broken.. It's not solving the actual core problem.
In transitioning from your current state to one where you're solving the problem, you can consider:
As a view:
CREATE VIEW yourtable_withexpiry AS SELECT *, CASE WHEN [ExpiryDate] IS NULL OR config.[Value] = 1 AND SysDateTimeOffset() < [ExpiryDate] THEN 1 ELSE 0 END AS IsValid FROM yourtable LEFT JOIN config ON config.property = 'AutomaticExpiry'
As a trigger:
CREATE TRIGGER trg_withexpiry ON yourtable AFTER INSERT OR UPDATE AS IF NOT EXISTS(select * from Config where Property = 'AutomaticExpiry' and Value = 1) RETURN; UPDATE yourtable SET [ExpiryDate] = DATE_ADD(..some current time and suitable offset here..) FROM yourtable y INNER JOIN inserted i ON y.pk = i.pk; END;
But honestly, you should be doing this in your front end app. It should be responsible for reading/writing session data and keeping things up to date and kicking users out if they're over time etc.. Using the database for this is, to a large extent, putting business logic/decision processing into a system that shouldn't be concerned with it..
Have your front end language implement a code that looks up user info upon some regular event (like page navigation or other activity) and refreshes the expiry date as a consequence of the activity, only if the expiry date isn't passed. For sure too keep the thing valid if the expiry is set to null if you want a way to have people active forever (or whatever)
SQL Server IIF Function By Practical Examples, In fact, the IIF() function is shorthand of a CASE expression: CASE WHEN B) Using SQL Server IIF() function with table column example. The following A) Using SQL Server IIF() function with a simple example. This example uses the IIF() function to check if 10 < 20 and returns the True string: SELECT IIF (10 < 20, 'True', 'False') Result; Here is the result: Result ----- True (1 row affected) B) Using SQL Server IIF() function with table column example
CASE Statements vs. IF Statements in Tableau, There is the classic IF THEN statement with the ability to add multiple ELSEIF And, I want a calculated field to identify these instances. I could use a simple IF THEN statement (Figure 1) or an IIF statement (Figure 2). Hi All, I have two calculated Columns which I created in the Data view from other Columns. The Calculated Columns are [Number of Days] and [Action] Number of Days Action Result 4 2 Green 6 2 Yellow 8 2 Red 15 3 Blue 16 4 Orange I want to Have another Calculated Column , [Result]in the Data based
IIf Function - Access, If the expression is true, IIf returns one value; if it is false, IIf returns another. You specify the values The IIf function is frequently used to create calculated fields in queries. The syntax is the In this case, "Language:" is the field alias. For more which will have Increase, Decrease and None values in the new column (from the CASE statement). 2) and then a calculated 'Measure'. Calculated 'Measure' should show just the record count of "Increase and Decrease" which I then show it using a 'Card' visualization.
How to Use IFTHEN Logic in SQL Server, In this example below, we want to return an additional locale column that specifies whether our To begin, we of initialize the CASE statement then specify under which conditions Restructuring our above example to use IIF is quite simple. Case statement is one of a widely used program flow statements. It allows us to control the program flow and it’s outputs when we have more than two flows/conditions to set. In essence, case statement is an optimized version of nested If statement. In SSRS, case statement is a decision function that can be reached through expressions.