Compare column values between 2 tables with conditional default valuesISNULL not working in TSQL SelectMongoDb query condition on comparing 2 fieldsAdd a column to a table, if it does not already existWhat is the safest practical way to deal with non-required MS Access text fields in queries?Oracle SQL - Compare Columns - SELECT records for values that do not have a matchfind missing values between two tablesTSQL Trigger unknown ColumnAssign -1 if column has null value or blank valueSQL 2017 - Comparing values between two tables where certain values can be NULLHow to conditionally parse a default value in a string when missing or not
Why we don't have vaccination against all diseases which are caused by microbes?
System to validate run time complexity requirements
Why did MS-DOS applications built using Turbo Pascal fail to start with a division by zero error on faster systems?
Is there a known non-euclidean geometry where two concentric circles of different radii can intersect? (as in the novel "The Universe Between")
Is refusing to concede in the face of an unstoppable Nexus combo punishable?
How is "sein" conjugated in this sub-sentence?
How do I find the fastest route from Heathrow to an address in London using all forms of transport?
In what ways can a Non-paladin access Paladin spells?
Have only girls been born for a long time in this village?
Create Tmux pane with sudo from sudoed pane?
Where is the "conservation" in the first law of thermodynamics?
What professions would a medieval village with a population of 100 need?
Why does my house heat up, even when it's cool outside?
Justifying the use of directed energy weapons
How should I face my manager if I make a mistake because a senior coworker explained me something wrong?
The economy of trapping
Check in to 2 hotels at same location
Shouldn't the "credit score" prevent Americans from going deeper and deeper into personal debt?
Why is Boris Johnson visiting only Paris & Berlin if every member of the EU needs to agree on a withdrawal deal?
How to create a summation symbol with a vertical bar?
Was Tuvok bluffing when he said that Voyager's transporters rendered the Kazon weapons useless?
In an emergency, how do I find and share my position?
Are there nouns that change meaning based on gender?
Is "stainless" a bulk or a surface property of stainless steel?
Compare column values between 2 tables with conditional default values
ISNULL not working in TSQL SelectMongoDb query condition on comparing 2 fieldsAdd a column to a table, if it does not already existWhat is the safest practical way to deal with non-required MS Access text fields in queries?Oracle SQL - Compare Columns - SELECT records for values that do not have a matchfind missing values between two tablesTSQL Trigger unknown ColumnAssign -1 if column has null value or blank valueSQL 2017 - Comparing values between two tables where certain values can be NULLHow to conditionally parse a default value in a string when missing or not
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
0
I have 2 tables in which I need to compare their column values. The comparison works well. However, I need to add an extra condition in order to fill in a default value if either of the table columns from the source or the target table are NULL. However, only if I have values in my Default column. Essentially, the Default column values would serve as overrides for NULL table values.
I have provided the CREATE TABLE script as well as I already have the full code prepared for by column comparisons between my source and target tables.
/****** Object: Table [dbo].[SourceTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].SourceTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
GO
/****** Object: Table [dbo].[TargetTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].TargetTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
-- Populate Source Table
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(10 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(15 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(5 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
GO
-- Populate Target Table
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
GO
;with
compare_source ([Provider], ProductID, [Default],
/*** Source columns to compare ***/
Col1Source, Col2Source
)
as (
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.SourceTable
),
compare_target ([Provider], ProductID, [Default],
/*** Target columns to compare ***/
Col1Target, Col2Target
)
as
(
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.TargetTable
)
SELECT ProductID
, Col1Source, Col1Target
, Col2Source, Col2Target
, [Provider]
FROM
(
SELECT
s.ProductID
, s.[Provider]
, s.Col1Source, s.Col2Source
, t.Col1Target, t.Col2Target
FROM compare_source s
left join compare_target t on t.ProductID = s.ProductID
WHERE not exists
(
SELECT 1 FROM compare_target t WHERE
s.ProductID = t.ProductID AND (( Col1Source = Col1Target ) OR ( ISNULL ( Col1Source, Col1Target ) IS NULL )) AND
s.ProductID = t.ProductID AND (( Col2Source = Col2Target ) OR ( ISNULL ( Col2Source, Col2Target ) IS NULL ))
)
) diff
Here are the current results:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
2 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
3 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
4 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
5 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
6 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
7 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
8 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
9 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
10 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
Since I have values in my TargetTable Default field (zeroes), I would like to insert these default values (zeroes) in my NULL values in my Target table. In essence, these Default values server as an "override" to replace all of my NULL values in my tables. The rule is, if there are values in my Default column, then use these values to replace all of my NULL fields. This would have the effect of eliminating some of the column value differences between tables. Thus, the expected results after the replacement of the NULLs by the Default field values should give the following result:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
2 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
3 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
4 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
5 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
6 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
7 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
8 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
9 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
10 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
In theory, I no longer want to see my fields which are identical. Thus, at the end, here is my result of the comparison after the Default values have replaced my NULLs:
ProductID | Col2Source | Col2Target | Provider
1 | NULL | 0.0000000000 | 28
2 | NULL | 0.0000000000 | 28
3 | NULL | 0.0000000000 | 28
4 | NULL | 0.0000000000 | 28
5 | NULL | 0.0000000000 | 28
6 | NULL | 0.0000000000 | 28
7 | NULL | 0.0000000000 | 28
8 | NULL | 0.0000000000 | 28
9 | NULL | 0.0000000000 | 28
10 | NULL | 0.0000000000 | 28
I'm hoping it's mostly clear.
compare
sql-server-2017 isnull not-exists isnullorempty asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
add a comment |
0
I have 2 tables in which I need to compare their column values. The comparison works well. However, I need to add an extra condition in order to fill in a default value if either of the table columns from the source or the target table are NULL. However, only if I have values in my Default column. Essentially, the Default column values would serve as overrides for NULL table values.
I have provided the CREATE TABLE script as well as I already have the full code prepared for by column comparisons between my source and target tables.
/****** Object: Table [dbo].[SourceTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].SourceTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
GO
/****** Object: Table [dbo].[TargetTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].TargetTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
-- Populate Source Table
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(10 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(15 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(5 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
GO
-- Populate Target Table
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
GO
;with
compare_source ([Provider], ProductID, [Default],
/*** Source columns to compare ***/
Col1Source, Col2Source
)
as (
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.SourceTable
),
compare_target ([Provider], ProductID, [Default],
/*** Target columns to compare ***/
Col1Target, Col2Target
)
as
(
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.TargetTable
)
SELECT ProductID
, Col1Source, Col1Target
, Col2Source, Col2Target
, [Provider]
FROM
(
SELECT
s.ProductID
, s.[Provider]
, s.Col1Source, s.Col2Source
, t.Col1Target, t.Col2Target
FROM compare_source s
left join compare_target t on t.ProductID = s.ProductID
WHERE not exists
(
SELECT 1 FROM compare_target t WHERE
s.ProductID = t.ProductID AND (( Col1Source = Col1Target ) OR ( ISNULL ( Col1Source, Col1Target ) IS NULL )) AND
s.ProductID = t.ProductID AND (( Col2Source = Col2Target ) OR ( ISNULL ( Col2Source, Col2Target ) IS NULL ))
)
) diff
Here are the current results:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
2 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
3 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
4 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
5 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
6 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
7 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
8 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
9 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
10 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
Since I have values in my TargetTable Default field (zeroes), I would like to insert these default values (zeroes) in my NULL values in my Target table. In essence, these Default values server as an "override" to replace all of my NULL values in my tables. The rule is, if there are values in my Default column, then use these values to replace all of my NULL fields. This would have the effect of eliminating some of the column value differences between tables. Thus, the expected results after the replacement of the NULLs by the Default field values should give the following result:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
2 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
3 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
4 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
5 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
6 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
7 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
8 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
9 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
10 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
In theory, I no longer want to see my fields which are identical. Thus, at the end, here is my result of the comparison after the Default values have replaced my NULLs:
ProductID | Col2Source | Col2Target | Provider
1 | NULL | 0.0000000000 | 28
2 | NULL | 0.0000000000 | 28
3 | NULL | 0.0000000000 | 28
4 | NULL | 0.0000000000 | 28
5 | NULL | 0.0000000000 | 28
6 | NULL | 0.0000000000 | 28
7 | NULL | 0.0000000000 | 28
8 | NULL | 0.0000000000 | 28
9 | NULL | 0.0000000000 | 28
10 | NULL | 0.0000000000 | 28
I'm hoping it's mostly clear.
compare
sql-server-2017 isnull not-exists isnullorempty asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
add a comment |
0
0
0
I have 2 tables in which I need to compare their column values. The comparison works well. However, I need to add an extra condition in order to fill in a default value if either of the table columns from the source or the target table are NULL. However, only if I have values in my Default column. Essentially, the Default column values would serve as overrides for NULL table values.
I have provided the CREATE TABLE script as well as I already have the full code prepared for by column comparisons between my source and target tables.
/****** Object: Table [dbo].[SourceTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].SourceTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
GO
/****** Object: Table [dbo].[TargetTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].TargetTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
-- Populate Source Table
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(10 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(15 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(5 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
GO
-- Populate Target Table
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
GO
;with
compare_source ([Provider], ProductID, [Default],
/*** Source columns to compare ***/
Col1Source, Col2Source
)
as (
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.SourceTable
),
compare_target ([Provider], ProductID, [Default],
/*** Target columns to compare ***/
Col1Target, Col2Target
)
as
(
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.TargetTable
)
SELECT ProductID
, Col1Source, Col1Target
, Col2Source, Col2Target
, [Provider]
FROM
(
SELECT
s.ProductID
, s.[Provider]
, s.Col1Source, s.Col2Source
, t.Col1Target, t.Col2Target
FROM compare_source s
left join compare_target t on t.ProductID = s.ProductID
WHERE not exists
(
SELECT 1 FROM compare_target t WHERE
s.ProductID = t.ProductID AND (( Col1Source = Col1Target ) OR ( ISNULL ( Col1Source, Col1Target ) IS NULL )) AND
s.ProductID = t.ProductID AND (( Col2Source = Col2Target ) OR ( ISNULL ( Col2Source, Col2Target ) IS NULL ))
)
) diff
Here are the current results:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
2 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
3 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
4 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
5 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
6 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
7 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
8 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
9 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
10 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
Since I have values in my TargetTable Default field (zeroes), I would like to insert these default values (zeroes) in my NULL values in my Target table. In essence, these Default values server as an "override" to replace all of my NULL values in my tables. The rule is, if there are values in my Default column, then use these values to replace all of my NULL fields. This would have the effect of eliminating some of the column value differences between tables. Thus, the expected results after the replacement of the NULLs by the Default field values should give the following result:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
2 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
3 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
4 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
5 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
6 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
7 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
8 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
9 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
10 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
In theory, I no longer want to see my fields which are identical. Thus, at the end, here is my result of the comparison after the Default values have replaced my NULLs:
ProductID | Col2Source | Col2Target | Provider
1 | NULL | 0.0000000000 | 28
2 | NULL | 0.0000000000 | 28
3 | NULL | 0.0000000000 | 28
4 | NULL | 0.0000000000 | 28
5 | NULL | 0.0000000000 | 28
6 | NULL | 0.0000000000 | 28
7 | NULL | 0.0000000000 | 28
8 | NULL | 0.0000000000 | 28
9 | NULL | 0.0000000000 | 28
10 | NULL | 0.0000000000 | 28
I'm hoping it's mostly clear.
compare
sql-server-2017 isnull not-exists isnullorempty asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
I have 2 tables in which I need to compare their column values. The comparison works well. However, I need to add an extra condition in order to fill in a default value if either of the table columns from the source or the target table are NULL. However, only if I have values in my Default column. Essentially, the Default column values would serve as overrides for NULL table values.
I have provided the CREATE TABLE script as well as I already have the full code prepared for by column comparisons between my source and target tables.
/****** Object: Table [dbo].[SourceTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].SourceTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
GO
/****** Object: Table [dbo].[TargetTable] Script Date: 9/14/2017 7:57:37 PM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].TargetTable(
ProductID [int] NOT NULL,
[Provider] [int] NOT NULL,
MaturityDate [datetime] NULL,
Price [numeric](28, 10) NULL,
Price2 [numeric](28, 10) NULL,
[Default] [numeric](28, 10) NULL
)
-- Populate Source Table
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(10 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(15 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(5 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(0 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
INSERT [dbo].SourceTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(1 AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)), CAST(NULL AS Numeric(28, 10)))
GO
-- Populate Target Table
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (1, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (2, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (3, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (4, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (5, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (6, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (7, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (8, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (9, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
INSERT [dbo].TargetTable (ProductID, [Provider], MaturityDate, Price, Price2, [Default]) VALUES (10, 28, N'2019-01-01', CAST(NULL AS Numeric(28,10)), CAST(NULL AS Numeric(28, 10)), CAST(0 AS Numeric(28, 10)))
GO
;with
compare_source ([Provider], ProductID, [Default],
/*** Source columns to compare ***/
Col1Source, Col2Source
)
as (
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.SourceTable
),
compare_target ([Provider], ProductID, [Default],
/*** Target columns to compare ***/
Col1Target, Col2Target
)
as
(
SELECT
[Provider]
,ProductID
,Price
,Price2
,[Default]
FROM dbo.TargetTable
)
SELECT ProductID
, Col1Source, Col1Target
, Col2Source, Col2Target
, [Provider]
FROM
(
SELECT
s.ProductID
, s.[Provider]
, s.Col1Source, s.Col2Source
, t.Col1Target, t.Col2Target
FROM compare_source s
left join compare_target t on t.ProductID = s.ProductID
WHERE not exists
(
SELECT 1 FROM compare_target t WHERE
s.ProductID = t.ProductID AND (( Col1Source = Col1Target ) OR ( ISNULL ( Col1Source, Col1Target ) IS NULL )) AND
s.ProductID = t.ProductID AND (( Col2Source = Col2Target ) OR ( ISNULL ( Col2Source, Col2Target ) IS NULL ))
)
) diff
Here are the current results:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
2 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
3 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
4 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
5 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
6 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
7 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
8 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
9 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
10 | 0.0000000000 | NULL | NULL | 0.0000000000 | 28
Since I have values in my TargetTable Default field (zeroes), I would like to insert these default values (zeroes) in my NULL values in my Target table. In essence, these Default values server as an "override" to replace all of my NULL values in my tables. The rule is, if there are values in my Default column, then use these values to replace all of my NULL fields. This would have the effect of eliminating some of the column value differences between tables. Thus, the expected results after the replacement of the NULLs by the Default field values should give the following result:
ProductID | Col1Source | Col1Target | Col2Source | Col2Target | Provider
1 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
2 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
3 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
4 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
5 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
6 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
7 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
8 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
9 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
10 | 0.0000000000 | 0.0000000000 | NULL | 0.0000000000 | 28
In theory, I no longer want to see my fields which are identical. Thus, at the end, here is my result of the comparison after the Default values have replaced my NULLs:
ProductID | Col2Source | Col2Target | Provider
1 | NULL | 0.0000000000 | 28
2 | NULL | 0.0000000000 | 28
3 | NULL | 0.0000000000 | 28
4 | NULL | 0.0000000000 | 28
5 | NULL | 0.0000000000 | 28
6 | NULL | 0.0000000000 | 28
7 | NULL | 0.0000000000 | 28
8 | NULL | 0.0000000000 | 28
9 | NULL | 0.0000000000 | 28
10 | NULL | 0.0000000000 | 28
I'm hoping it's mostly clear.
compare
sql-server-2017 isnull not-exists isnullorempty compare
sql-server-2017 isnull not-exists isnullorempty asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
asked Mar 27 at 15:47
Shawn_MShawn_M
155 bronze badges
155 bronze badges
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55381333%2fcompare-column-values-between-2-tables-with-conditional-default-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55381333%2fcompare-column-values-between-2-tables-with-conditional-default-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
