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
Co-author responds to email by mistake cc'ing the EiC
How would one country purchase another?
Science fiction short story where aliens contact a drunk about Earth's impending destruction
How do I make distance between concentric circles equal?
When translating the law, who ensures that the wording does not change the meaning of the law?
Why didn’t Doctor Strange stay in the original winning timeline?
How should I face my manager if I make a mistake because a senior coworker explained me something wrong?
Check in to 2 hotels at same location
Why is 日本 read as "nihon" but not "nitsuhon"?
Why did this happen to Thanos's ships at the end of "Avengers: Endgame"?
Is it safe to remove the bottom chords of a series of garage roof trusses?
Is it appropriate for a prospective landlord to ask me for my credit report?
Can a character spend multiple hit dice at level 1?
How to compare two different formulations of a problem?
Potential new partner angry about first collaboration - how to answer email to close up this encounter in a graceful manner
Why aren't RCS openings an issue for spacecraft heat shields?
Why doesn't mathematics collapse even though humans quite often make mistakes in their proofs?
Get info from plist file
confused about grep and the * wildcard
Efficiently pathfinding many flocking enemies around obstacles
Create Tmux pane with sudo from sudoed pane?
Why does The Ancient One think differently about Doctor Strange in Endgame than the film Doctor Strange?
Why did MS-DOS applications built using Turbo Pascal fail to start with a division by zero error on faster systems?
Was Tuvok bluffing when he said that Voyager's transporters rendered the Kazon weapons useless?
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
