Ambiguous column error

SubDescription 1, 2 & 3 fields were added to the ItemClass Table in RMS V2.0

In your Query 3, you need to specify the table name in the 2 palces that you are referencing SubDescription1. To get the same result as the original query, use Item.SubDescription1. If you want to return the Matrix level sub description, use ItemClass.SubDescription1

Glenn Adams Tiber Creek C> We just updated to 2.0. We have a couple of SQL querries that we run weekly.

SubDescription 1, 2 & 3 fields were added to the ItemClass Table in RMS V2.0

In your Query 3, you need to specify the table name in the 2 palces that you are referencing SubDescription1. To get the same result as the original query, use Item.SubDescription1. If you want to return the Matrix level sub description, use ItemClass.SubDescription1

Glenn Adams Tiber Creek Consulting

snipped-for-privacy@tibercreek.com----------------------------------------------Please DO NOT respond to me directly but post all responses here in thenewsgroup so that all can share the information.

Glenn Adams Tiber Creek C> We just updated to 2.0. We have a couple of SQL querries that we run weekly.

Glenn,

Thanks for that information! So, just to clarify, can I just go into both of my Query's and find and replace in the following manner: SubDescription1 becomes Item.SubDescription1

I'm not clear on which items are being replaced with the new term: ItemClass.SubDescription1

Here's my other querry, it's much larger, but returned the same error:

-- 2.2. Move Matrix Detail1 to SubDescription1 (Only if SubDescription1 is blank, otherwise leave it as is - NOTE - if you are changing a size that's already existing, you need to physically change the size in the parent item and in the Subdescription1):

UPDATE Item SET Item.SubDescription1 = b.Detail1 FROM Item LEFT JOIN (SELECT Itemid, ItemClassID, Detail1, Detail2, Detail3 FROM ItemClassComponent LEFT JOIN ItemClass ON ItemClassComponent.ItemClassID = ItemClass.ID WHERE ClassType = 0) b ON Item.ID = b.ItemID LEFT JOIN ItemClass ON b.ItemClassID = ItemClass.ID WHERE b.Detail1 IS NOT NULL AND Item.SubDescription1 IS NULL

-- 3. Change ILC for Shoes/Apparel:

-- 3.1. Shoes:

UPDATE Item SET Item.ItemLookupCode = IC.ItemLookupCode + '-' + ICC.Detail1 FROM Item LEFT JOIN ItemClassComponent ICC ON Item.ID = ICC.ItemID LEFT JOIN ItemClass IC ON ICC.ItemClassID = IC.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID WHERE Department.Name = 'Shoes' AND ICC.Detail1 IS NOT NULL

-- 3.2. Apparel:

UPDATE Item SET Item.ItemLookupCode = IC.ItemLookupCode + '-' + CASE Detail1 WHEN 'XS' THEN '1' WHEN 'S' THEN '2' WHEN 'M' THEN '3' WHEN 'L' THEN '4' WHEN 'LG' THEN '4' WHEN 'XL' THEN '5' WHEN 'XXL' THEN '6' WHEN 'SM' THEN '1' WHEN 'ML' THEN '2' WHEN 'LX' THEN '3' ELSE '' END FROM Item LEFT JOIN ItemClassComponent ICC ON Item.ID = ICC.ItemID LEFT JOIN ItemClass IC ON ICC.ItemClassID = IC.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID WHERE Department.Name = 'Apparel' AND Detail1 IN ('XS','S','M','L','LG','XL','XXL','SM','ML','LX') AND ICC.Detail1 IS NOT NULL

-- 3.3. Update these Apparel items with non standard descriptions to include

--Parent ILC + '-' + Matrix Detail 1:

UPDATE Item SET Item.ItemLookupCode = IC.ItemLookupCode + '-' + Detail1 FROM Item LEFT JOIN ItemClassComponent ICC ON Item.ID = ICC.ItemID LEFT JOIN ItemClass IC ON ICC.ItemClassID = IC.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID WHERE Department.Name = 'Apparel' AND Detail1 NOT IN ('XS','S','M','L','LG','XL','XXL','SM','ML','LX') AND ICC.Detail1 IS NOT NULL AND LEN(IC.ItemLookupCode + '-' + Detail1) < 26

-- 3.4. Update the rest of the Matrix items in other Departments to have an

--ILC of Parent Matrix ILC + '-' + Matrix Detail 1 Description:

UPDATE Item SET Item.ItemLookupCode = IC.ItemLookupCode + '-' + Detail1 FROM Item LEFT JOIN ItemClassComponent ICC ON Item.ID = ICC.ItemID LEFT JOIN ItemClass IC ON ICC.ItemClassID = IC.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID WHERE Department.Name NOT IN ('Apparel', 'Shoes') AND ICC.Detail1 IS NOT NULL AND LEN(IC.ItemLookupCode + '-' + Detail1) < 26

--4. Remove any Alias's where it matches an existing ILC or another alias:

DELETE Alias WHERE Alias IN (SELECT ItemLookupCode FROM Item) DELETE Alias WHERE Alias IN (SELECT Alias FROM Alias GROUP BY Alias HAVING COUNT(Alias) > 1)

--5. Filling Reorder Number with ILC for Standard Items and Matrix Parent for Matrix Components

--5.1 Move ILC into Reorder Number for all items:

UPDATE SupplierList SET SupplierList.ReorderNumber = Item.ItemLookupCode From SupplierList INNER JOIN Item ON SupplierList.ItemID = Item.ID

--5.2 Move Matrix Parent Description into Reorder Number:

UPDATE SupplierList SET SupplierList.ReorderNumber = ItemClass.ItemLookupCode FROM SupplierList INNER JOIN ItemClassComponent ON SupplierList.ItemID ItemClassComponent.ItemID LEFT JOIN ItemClass ON ItemClassComponent.ItemClassID = ItemClass.ID

--6. Append size to end of Item Description

UPDATE Item SET Item.Description = LEFT(LTRIM(RTRIM(ItemClass.Description)) + ' - ' + LTRIM(RTRIM(SubDescription1)), 30) FROM Item INNER JOIN ItemClassComponent ON Item.ID = ItemClassComponent.ItemID INNER JOIN ItemClass ON ItemClassComponent.ItemClassID = ItemClass.ID WHERE ClassType = 0 AND ItemClass.Description IS NOT NULL

Thanks!

If you want to get the same data you were getting in 1.X, use item.SubDescription1.

ItemClass.Subdescription1 is also valid, but would be the same value for all matrix members. I didn't really try to decipher what you are trying to get, so I don't know which is appropriate; probably just use the original item.Subdescription1

The same thing occurs in query 6, again just add "item." and it should be fixed

Glenn Adams Tiber Creek C> Glenn,

Glenn,

It worked. Thank you very much!