I have this code in my select statement
ISNULL(a.PolicySignedDateTime,aq.Amount) AS 'Signed Premium',
But I want to see if "a.PolicySignedDateTime" is not null. Is there a easy function to do this which does not involve using a "if" statement?
Cheers guys
You have to use CASE
SELECT CASE WHEN Field IS NOT NULL
THEN 'something'
ELSE 'something else'
END
I know is late but just in case someone else viewing this and using MSSQL 2012 or above you could use 'IIF' statement.
I guess OP don't want to use 'IF' clausule cause is "too much code syntax" to acomplish simple stuff.
An alternative also cleaner than 'IF' statement is 'IIF'. Is just an inline 'IF' simplification.
SELECT IIF(X IS NULL, 'Is null', 'Not null') 'Column Name'
Regarding OP
SELECT IIF(a.PolicySignedDateTime IS NULL, NULL, aq.Amount) AS 'Signed Premium'
It seems to me this would be an easy way to get the opposite.
SELECT (1-ISNULL(field));
Will give you 1 if NOT NULL and 0 if NULL instead of the 1 for NULL and 0 for NOT NULL
There is the COALESCE expression (although not function https://msdn.microsoft.com/en-us/library/ms190349.aspx) test the arguments in order and keep doing it until finds the value not NULL and returns it.
example usage:SELECT COALESCE(NULL, NULL, 5)--returns 5
In your case :
COALESCE(a.PolicySignedDateTime,aq.Amount) AS 'Signed Premium',
Try this:
SELECT
CASE
WHEN a.PolicySignedDateTime IS NOT NULL THEN a.PolicySignedDateTime
ELSE aq.Amount
FROM your joined table
But.... ISNULL(a.PolicySignedDateTime, aq.Amount) check if your field is null, so is not null you obtain its value.
So I don't really understand because you want to use another way.
There is no opposite function but you can do it without CASE.
Use the fact that a string + 'something' will be NULL if string is NULL, and if that is null then use ISNULL to return 'somethingelse', get end of the returned value with RIGHT() and check that against 'something' using NULLIF, and then use COALESCE to do what you'd like to do if that is NULL (meaning original value is not null).
Example:
declare @text varchar(20) = 'some text or value'
select COALESCE(NULLIF(RIGHT(ISNULL(@text + 'NOT', 'IS ') + 'NULL', 7), 'NOTNULL'), 'NOT NULL')
Try this code and also try it with no value for @text.
Since we are in DBMS environment, it is possible to use set-based approach. So, assuming that table has an identifier field (id) - primary key or unique and not null, the job can be done this way:
SELECT numeric_field * count(flag_field) AS not_null_flag_numeric_value_or_zero
FROM my_table
GROUP BY id, numeric_field
If value field is of character data type some char function should be used. So, practical implementation of the approach might be as below:
SELECT * INTO #temporary_table
FROM
(VALUES
(1, 1, 111, 'string_1'),
(2, NULL, 222, 'string_2')
) data_table(id, flag_field, numeric_field, character_field)
ALTER TABLE #temporary_table ADD CONSTRAINT tab_pk PRIMARY KEY (id)
SELECT
count(flag_field) AS is_not_null,
numeric_field * count(flag_field) AS numeric_value_or_zero,
numeric_field * nullif(count(flag_field), 0) AS numeric_value_or_null,
left(character_field, len(character_field) * count(flag_field)) AS character_value_or_empty,
stuff(character_field, nullif(count(flag_field), 0), 0, '') AS character_value_or_null
FROM #temporary_table
GROUP BY id, numeric_field, character_field
--DROP TABLE #temporary_table
Another option to get NOT NULL sign in form of 1 is to use conversion of flag_field value to bit:
... cast(flag_field as bit) ...
This works when conversion to bit is available for your flag_field data type and is usefull when you can consider 0-values and NULLs as same NO VALUE. Represented by NULL or 0 - as you choose:
SELECT
nullif(cast(flag_field as bit), 0) AS is_not_null_or_null,
isnull(cast(flag_field as bit), 0) AS is_not_null_or_zero,
numeric_field * nullif(cast(flag_field as bit), 0) AS value_or_null,
numeric_field * isnull(cast(flag_field as bit), 0) AS value_or_zero
FROM #temporary_table
CREATE FUNCTION ISNUL (@DATA sql_variant) RETURNS BIT AS
BEGIN
IF (@DATA IS NULL) RETURN 1;
RETURN 0
END
SELECT dbo.ISNUL(NULL) -- 1
SELECT dbo.ISNUL('123') -- 0
SELECT dbo.ISNUL(123) -- 0
SELECT dbo.ISNUL(123) ^ dbo.ISNUL(NULL), dbo.ISNUL('123') ^ dbo.ISNUL(NULL), dbo.ISNUL('123') ^ dbo.ISNUL(123), dbo.ISNUL(NULL) ^ dbo.ISNUL(NULL) -- 1,1,0,0
Or when XOR is in focus:
CREATE FUNCTION XOR (@D1 sql_variant, @D2 sql_variant) RETURNS BIT AS
BEGIN
IF (@D1 IS NULL AND @D2 IS NULL) RETURN 0;
IF (@D1 IS NOT NULL AND @D2 IS NOT NULL) RETURN 0;
RETURN 1
END
SELECT XOR(NULL,123), XOR('123',NULL), XOR(NULL,NULL), XOR('123',123)
SELECT 'TRUE' where XOR(NULL,123) = 'true'
SELECT 'FALSE' where XOR('123',123) = 'false'
isnull(nullif(isnull(@Num,0),@Num),1)
0 for null, 1 for not null
