SQL tricks by Oscar

delete-product.sql

--Deleting a fielddata from product

Begin transaction

select * from Products.VariantFieldData p
where p.OwnerSystemId = 'BBC232C1-ED8D-4381-853B-23DBB96907CA'
and FieldDefinitionId = '_images'

delete from Products.VariantFieldData
where OwnerSystemId = 'BBC232C1-ED8D-4381-853B-23DBB96907CA'
and GuidValue = 'aadd4d4b-2026-44b4-9015-c950ee5123b1';

update Products.VariantFieldData
set [Index] = [Index] - 1
where OwnerSystemId = 'BBC232C1-ED8D-4381-853B-23DBB96907CA'
and FieldDefinitionId = '_images'

select * from Products.VariantFieldData p
where p.OwnerSystemId = 'BBC232C1-ED8D-4381-853B-23DBB96907CA'
and FieldDefinitionId = '_images'

Rollback;

--Deleting fielddata from a page, remember to always delete from draft too

Begin transaction

select * from Sites.DraftPageFieldData p
where FieldDefinitionId = 'CompanyLogo'
  and GuidValue = '44F99C90-F589-4F6C-A46E-90CAD480B362' 

delete from Sites.PageFieldData
where FieldDefinitionId = 'CompanyLogo' 
  and GuidValue = '44F99C90-F589-4F6C-A46E-90CAD480B362' 

delete from Sites.DraftPageFieldData
where FieldDefinitionId = 'CompanyLogo' 
  and GuidValue = '44F99C90-F589-4F6C-A46E-90CAD480B362' 

select * from Sites.PageFieldData p
where p.OwnerSystemId = '3505fb6b-7ed7-4411-a745-75c5f9aeec22'

Rollback;

find-categories-with-true-boolean-field.sql

SELECT * FROM Products.Category c
    JOIN Products.CategoryFieldData f ON  f.OwnerSystemId = c.SystemId 	
WHERE c.AssortmentSystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34'
 AND f.FieldDefinitionId = 'IsMainCategory'
 AND f.BooleanValue = 1

find-order-comment-to-fix-payment.sql

select comments
from dbo.ECommerce_Order
WHERE ExternalOrderID = 'RR5767558'

find-orders-with-long-payment-update-delay.sql

begin transaction
select top 10000 o.ExternalOrderID, o.OrderDate, p.LastUpdatedDate as 'PaymentDate', ao.LastUpdatedDate, CAST(p.LastUpdatedDate - o.OrderDate as TIME) as 'Difference'
from dbo.ECommerce_Order o WITH (NOLOCK)
join dbo.ECommerce_AdditionalOrderInfo ao WITH (NOLOCK) 
	on ao.OrderID = o.OrderID and ao.AdditionalOrderInfoKey = 'SapOrderId'
join dbo.ECommerce_PaymentInfo p WITH (NOLOCK)
	ON p.OrderID = o.OrderID
where o.OrderDate > '2024-06-23 00:00:00'
and o.OrderDate < '2024-06-24 00:00:00'
and ao.LastUpdatedDate > '2024-06-24 00:00:00'
order by [Difference] DESC
rollback;

begin transaction
select top 1000 o.OrderDate, p.LastUpdatedDate as 'PaymentDate', ao.LastUpdatedDate, CAST(p.LastUpdatedDate - o.OrderDate as TIME) as 'Difference'
from dbo.ECommerce_Order o WITH (NOLOCK)
join dbo.ECommerce_AdditionalOrderInfo ao WITH (NOLOCK) 
	on ao.OrderID = o.OrderID and ao.AdditionalOrderInfoKey = 'SapOrderId'
join dbo.ECommerce_PaymentInfo p WITH (NOLOCK)
	ON p.OrderID = o.OrderID
where o.OrderDate > '2024-06-22 00:00:00'
and o.OrderDate < '2024-06-23 00:00:00'
and ao.LastUpdatedDate > '2024-06-23 00:00:00'
order by [Difference] DESC
rollback;

find-orders-without-email-and-list-by-month.sql

select 
	MONTH(o.OrderDate) as 'Month', 
	p.PaymentProvider, 
	Count(*) as [Nr. of orders without e-mail],
   (select count(*) from dbo.ECommerce_Order oo WITH (NOLOCK)
		join dbo.ECommerce_PaymentInfo pp WITH (NOLOCK) on pp.OrderID = oo.OrderID
	where pp.PaymentProvider = p.PaymentProvider AND MONTH(oo.OrderDate) = MONTH(o.OrderDate)
	  and oo.OrderDate > '2022-01-01') as [Total orders]
from dbo.ECommerce_Order o WITH (NOLOCK)
join dbo.ECommerce_CustomerInfo c WITH (NOLOCK) on c.CustomerInfoID = o.CustomerInfoID
join dbo.ECommerce_Address a WITH (NOLOCK) on a.AddressID = c.CustomerAddressID
join dbo.ECommerce_PaymentInfo p WITH (NOLOCK) on p.OrderID = o.OrderID
where o.OrderDate > '2022-01-01'
and a.Email is NULL
group BY MONTH(o.OrderDate), p.PaymentProvider
order by MONTH(o.OrderDate)

select o.ExternalOrderID, o.OrderDate
from dbo.ECommerce_Order o WITH (NOLOCK)
join dbo.ECommerce_CustomerInfo c WITH (NOLOCK) on c.CustomerInfoID = o.CustomerInfoID
join dbo.ECommerce_Address a WITH (NOLOCK) on a.AddressID = c.CustomerAddressID
where o.OrderDate > '2022-07-01'
and a.Email is NULL
order by o.OrderDate DESC

find-page-where-block-exist.sql

select top 100 * from Blocks.Block b
	join Blocks.BlockItemLink l ON l.BlockSystemId = b.SystemId
	join Sites.Page p ON p.SystemId = l.PageSystemId
where b.FieldTemplateSystemId = '24be3983-51d4-404d-a5ee-20b76118280f'
  and p.Status = 1

find-product-variant-field.sql

  Begin transaction
  Select *
  FROM [Products].[VariantFieldData]
  Where FieldDefinitionId = 'Packaging' And Culture != '*'
  Rollback;

find-products-without-main-category.sql

SELECT * FROM Products.BaseProduct b
	INNER JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	INNER JOIN Products.Category c on c.SystemId = l.CategorySystemId
	INNER JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
	and b.SystemId not in (
		select BaseProductSystemId
		from Products.BaseProductCategoryLink
		where MainCategory = 1
	)

replace-order-careOf.sql

update a
set CareOf = 'Lac Thu Fung'
from dbo.ECommerce_Address a
join dbo.ECommerce_Delivery d on a.AddressID = d.DeliveryAddressID
join dbo.ECommerce_Order o on d.OrderID = o.OrderID
where o.ExternalOrderID = 'LS101074'

replace-order-delivery-option.sql

update dbo.ECommerce_AdditionalDeliveryInfo
set AdditionalDeliveryInfoValue = replace(AdditionalDeliveryInfoValue, 'Tyresö Trollbäcken Alfa Filmkedjan och Mobilservice', 'Tyresö Trollbäcken Alfa Filmkedjan och Mobilservic')
from dbo.ECommerce_AdditionalDeliveryInfo dm
join dbo.ECommerce_Delivery d on dm.DeliveryID = d.DeliveryID
join dbo.ECommerce_Order o on d.OrderID = o.OrderID
where o.ExternalOrderID = 'LS883723'
and dm.AdditionalDeliveryInfoKey = 'UnifaunDeliveryOptions'

search-audit-logs.sql

SELECT
   t.TransactionDateTimeUtc,
   r.ArgsJson,
   r.State,
   i.EntitySystemId,
   t.IdentityName,
   t.IdentityJson
FROM Auditing.AuditTransactionItem i WITH (NOLOCK)
  JOIN Auditing.AuditTransactionRecord r WITH (NOLOCK) ON r.AuditTransactionItemSystemId = i.SystemId 
  JOIN Auditing.AuditTransaction t WITH (NOLOCK) ON t.SystemId = i.AuditTransactionSystemId
WHERE i.EntityType = 'Litium.Products.BaseProduct, Litium.Abstractions'
  AND i.EntitySystemId = '3E564510-0B52-48F8-B2E1-2EC8D779E273'
  --AND r.ArgsJson LIKE '%505074-397%'
  --AND t.TransactionDateTimeUtc > '2023-11-20'
  --AND r.State = 4 (Added = 1, Modified = 2, Removed = 4, Read = 8)
ORDER BY t.TransactionDateTimeUtc DESC

troubleshooting-vinga.sql

SELECT DISTINCT b.SystemId FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
  and b.SystemId not in (
	 select BaseProductSystemId
	 from Products.BaseProductCategoryLink
	 where MainCategory = 1
 )

-- PRODUCTS WITHOUT VARIANTS
SELECT DISTINCT b.SystemId FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	LEFT JOIN Products.Variant v on v.BaseProductSystemId = b.SystemId
	--JOIN Products.VariantChannelLink vl on vl.VariantSystemId = v.SystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
and b.SystemId not in (
	select BaseProductSystemId
	from Products.BaseProductCategoryLink
	where MainCategory = 1
)
AND v.Id IS NULL

-- PUBLISHED PRODUCTS WITHOUT MAIN CATEGORY
SELECT DISTINCT b.SystemId FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	JOIN Products.Variant v on v.BaseProductSystemId = b.SystemId
	JOIN Products.VariantChannelLink vl on vl.VariantSystemId = v.SystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
and b.SystemId not in (
	select BaseProductSystemId
	from Products.BaseProductCategoryLink
	where MainCategory = 1
)

-- UNPUBLISHED PRODUCTS WITHOUT MAIN CATEGORY
SELECT DISTINCT b.SystemId FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	JOIN Products.Variant v on v.BaseProductSystemId = b.SystemId
	LEFT JOIN Products.VariantChannelLink vl on vl.VariantSystemId = v.SystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
and b.SystemId not in (
	select BaseProductSystemId
	from Products.BaseProductCategoryLink
	where MainCategory = 1
)
and vl.VariantSystemId IS NULL

-- PUBLISHED PRODUCTS WITHOUT MAIN CATEGORY DESPITE BEING TIED TO CATEGORY MARKED AS MAIN CATEGORY
SELECT DISTINCT b.SystemId FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	JOIN Products.Variant v on v.BaseProductSystemId = b.SystemId
	JOIN Products.VariantChannelLink vl on vl.VariantSystemId = v.SystemId
	JOIN Products.CategoryFieldData cf on cf.OwnerSystemId = c.SystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
and b.SystemId not in (
	select BaseProductSystemId
	from Products.BaseProductCategoryLink
	where MainCategory = 1
)
AND cf.FieldDefinitionId = 'IsMainCategory'
AND cf.BooleanValue = 1

-- PUBLISHED PRODUCTS WITHOUT MAIN CATEGORY AND NOT TIED TO CATEGORY MARKED AS MAIN CATEGORY
SELECT DISTINCT b.SystemId
FROM Products.BaseProduct b
	JOIN Products.BaseProductCategoryLink l on l.BaseProductSystemId = b.SystemId
	JOIN Products.Category c on c.SystemId = l.CategorySystemId
	JOIN Products.Assortment a on a.SystemId = c.AssortmentSystemId
	JOIN Products.Variant v on v.BaseProductSystemId = b.SystemId
	JOIN Products.VariantChannelLink vl on vl.VariantSystemId = v.SystemId
WHERE a.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34' 
and 0 = (
	select count(*) from Products.BaseProductCategoryLink
	where MainCategory = 1 AND BaseProductSystemId = b.SystemId
)
and not exists (
	SELECT * FROM Products.Category cc
		JOIN Products.Assortment aa on aa.SystemId = cc.AssortmentSystemId 
		JOIN Products.BaseProductCategoryLink ll on ll.CategorySystemId = cc.SystemId AND ll.BaseProductSystemId = b.SystemId
		JOIN Products.CategoryFieldData cf ON cf.OwnerSystemId = cc.SystemId AND cf.FieldDefinitionId = 'IsMainCategory'
	WHERE aa.SystemId = '1b1f9f31-5162-42ea-4964-08d6a39c6c34'
		AND cf.BooleanValue = 1)

update-product-field-type.sql

SELECT * FROM Products.BaseProductFieldData 
WHERE OwnerSystemId = '280a6c6e-e91b-40e0-84e0-0702a0bd8628'

SELECT * FROM FieldFramework.FieldDefinition 
WHERE AreaType LIKE '%Products%' ORDER BY Id AND Id = 'TestTestTest'

UPDATE FieldFramework.FieldDefinition
SET FieldType = 'MultirowText'
WHERE Id = 'Maintanence' AND AreaType LIKE '%Products%'