Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save xavierzwirtz/70ba177f9ff9990f52b17dd22d8b4533 to your computer and use it in GitHub Desktop.
Save xavierzwirtz/70ba177f9ff9990f52b17dd22d8b4533 to your computer and use it in GitHub Desktop.
DECLARE @p0 NVARCHAR(20) = 'ItemAndItemLocations'
DECLARE @p1 NVARCHAR(2) = 't1'
DECLARE @p2 UNIQUEIDENTIFIER = '8251d2e8-f988-49fe-933a-af54f2e53532'
DECLARE @p3 NVARCHAR(20) = 'ItemAndItemLocations'
DECLARE @p4 NVARCHAR(3) = 'ct0'
DECLARE @p5 UNIQUEIDENTIFIER = '8251d2e8-f988-49fe-933a-af54f2e53532'
DECLARE @p6 NVARCHAR(20) = 'ItemAndItemLocations'
DECLARE @p7 NVARCHAR(3) = 'ct1'
DECLARE @p8 UNIQUEIDENTIFIER = '8251d2e8-f988-49fe-933a-af54f2e53532'
SELECT [t1].[data_item_no],
CASE
WHEN [t1].[audit_bd_version] IS NULL THEN 0x0000000000000000
ELSE [t1].[audit_bd_version]
END AS [t1.audit_BD_Version],
[t1].[audit_bd_deleted] AS [t1.audit_BD_Deleted],
[t1].[matched] AS [t1.matched],
[t1].[data_id] AS [t1.data_ID],
CASE
WHEN [ct0].[audit_bd_version] IS NULL THEN 0x0000000000000000
ELSE [ct0].[audit_bd_version]
END AS [ct0.audit_BD_Version],
[ct0].[audit_bd_deleted] AS [ct0.audit_BD_Deleted],
[ct0].[matched] AS [ct0.matched],
[ct0].[data_id] AS [ct0.data_ID],
CASE
WHEN [ct1].[audit_bd_version] IS NULL THEN 0x0000000000000000
ELSE [ct1].[audit_bd_version]
END AS [ct1.audit_BD_Version],
[ct1].[audit_bd_deleted] AS [ct1.audit_BD_Deleted],
[ct1].[matched] AS [ct1.matched],
[ct1].[data_id] AS [ct1.data_ID]
FROM (SELECT CASE
WHEN [t1_sub].matched = 'true' THEN [t1_sub].data_id
ELSE [t1_audit].[id]
END AS [data_ID],
CASE
WHEN [t1_sub].matched = 'true' THEN [t1_sub].data_item_no
ELSE [t1_audit].[item_no]
END AS [data_item_no],
[t1_audit].[bd_version] AS [audit_BD_Version],
[t1_audit].[bd_deleted] AS [audit_BD_Deleted],
'true' AS [matched]
FROM (SELECT [id] AS [data_ID],
[item_no] AS [data_item_no],
'true' AS [matched]
FROM [dbo].[imitmidx_sql]) AS [t1_sub]
FULL OUTER JOIN (SELECT *
FROM [dbo].[bd_audit_imitmidx_sql]) AS
[t1_audit]
ON ( [t1_sub].[data_id] = [t1_audit].[id] )) AS [t1]
LEFT JOIN (SELECT CASE
WHEN [ct0_sub].matched = 'true' THEN
[ct0_sub].data_id
ELSE [ct0_audit].[id]
END AS [data_ID],
CASE
WHEN [ct0_sub].matched = 'true' THEN
[ct0_sub].data_item_no
ELSE [ct0_audit].[item_no]
END AS [data_item_no],
CASE
WHEN [ct0_sub].matched = 'true' THEN
[ct0_sub].data_loc
ELSE [ct0_audit].[loc]
END AS [data_loc],
[ct0_audit].[bd_version] AS [audit_BD_Version],
[ct0_audit].[bd_deleted] AS [audit_BD_Deleted],
'true' AS [matched]
FROM (SELECT [id] AS [data_ID],
[item_no] AS [data_item_no],
[loc] AS [data_loc],
'true' AS [matched]
FROM [dbo].[iminvloc_sql]) AS [ct0_sub]
FULL OUTER JOIN (SELECT *
FROM [dbo].[bd_audit_iminvloc_sql])
AS
[ct0_audit]
ON ( [ct0_sub].[data_id] =
[ct0_audit].[id] )) AS
[ct0]
ON ( [t1].[data_item_no] = [ct0].[data_item_no] )
LEFT JOIN (SELECT CASE
WHEN [ct1_sub].matched = 'true' THEN
[ct1_sub].data_id
ELSE [ct1_audit].[id]
END AS [data_ID],
CASE
WHEN [ct1_sub].matched = 'true' THEN
[ct1_sub].data_item_no
ELSE [ct1_audit].[item_no]
END AS [data_item_no],
CASE
WHEN [ct1_sub].matched = 'true' THEN
[ct1_sub].data_loc
ELSE [ct1_audit].[loc]
END AS [data_loc],
[ct1_audit].[bd_version] AS [audit_BD_Version],
[ct1_audit].[bd_deleted] AS [audit_BD_Deleted],
'true' AS [matched]
FROM (SELECT [id] AS [data_ID],
[item_no] AS [data_item_no],
[loc] AS [data_loc],
'true' AS [matched]
FROM [dbo].[iminvloc_sql]) AS [ct1_sub]
FULL OUTER JOIN (SELECT *
FROM [dbo].[bd_audit_iminvloc_sql])
AS
[ct1_audit]
ON ( [ct1_sub].[data_id] =
[ct1_audit].[id] )) AS
[ct1]
ON ( [t1].[data_item_no] = [ct1].[data_item_no] )
LEFT JOIN (SELECT *
FROM [dbo].[bd_lastversion_imitmidx_sql]
WHERE [group] = @p0
AND [tablealias] = @p1
AND [targetid] = @p2) AS [t1_last]
ON ( [t1].[data_id] = [t1_last].[key_id] )
LEFT JOIN (SELECT *
FROM [dbo].[bd_lastversion_iminvloc_sql]
WHERE [group] = @p3
AND [tablealias] = @p4
AND [targetid] = @p5) AS [ct0_last]
ON ( [ct0].[data_id] = [ct0_last].[key_id] )
LEFT JOIN (SELECT *
FROM [dbo].[bd_lastversion_iminvloc_sql]
WHERE [group] = @p6
AND [tablealias] = @p7
AND [targetid] = @p8) AS [ct1_last]
ON ( [ct1].[data_id] = [ct1_last].[key_id] )
WHERE ( ( ( [t1_last].[last] IS NULL
AND [t1].[data_id] IS NOT NULL )
OR (( t1.audit_bd_version > t1_last.last
AND ( t1_last.runid IS NULL
OR NOT t1_last.runid =
'948072a2-6c25-43a8-8c12-8a93d86dd537'
)
)) )
OR ( ( [ct0_last].[last] IS NULL
AND [ct0].[data_id] IS NOT NULL )
OR (( ct0.audit_bd_version > ct0_last.last
AND ( t1_last.runid IS NULL
OR NOT ct0_last.runid =
'948072a2-6c25-43a8-8c12-8a93d86dd537' )
)
) )
OR ( ( [ct1_last].[last] IS NULL
AND [ct1].[data_id] IS NOT NULL )
OR (( ct1.audit_bd_version > ct1_last.last
AND ( t1_last.runid IS NULL
OR NOT ct1_last.runid =
'948072a2-6c25-43a8-8c12-8a93d86dd537' )
)
) ) )
AND ( [t1].[data_item_no] IN ((SELECT DISTINCT [item_no]
FROM [iminvloc_sql]
WHERE [item_no] IN ((SELECT [sku]
FROM [sf_sku]))
AND [loc] IN ( 'SF' )))
OR [t1].[audit_bd_deleted] = 'true' )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment