{$page}

In Control 04.40.1003.2501, the Description field was added to the TransPart table. But the field was not initialized to the looked up values previously used. This query can be used to update the data, until the April 2010 release, which will include this SQL.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

code_formatsql

DECLARE @TransPartUpdate TABLE(ID INT PRIMARY KEY, TransDetailID INT, PartID INT,

                             PartLinkID INT, PartLinkClassTypeID INT,

                             AttachedBillDetailID INT, AttachedPODetailID INT, AttachedRDDetailID INT,

                             Description VARCHAR(255));

INSERT INTO @TransPartUpdate (ID, TransDetailID, PartID, PartLinkID, PartLinkClassTypeID,

                            AttachedBillDetailID, AttachedPODetailID, AttachedRDDetailID)

SELECT ID, TransDetailID, PartID, PartLinkID, PartLinkClassTypeID,

     AttachedBillDetailID, AttachedPODetailID, AttachedRDDetailID

FROM TransPart

WHERE ID > 0

    AND Description IS NULL

    AND PartID IS NOT NULL

    AND TransDetailID IS NOT NULL

;

UPDATE @TransPartUpdate

SET Description = CAST( P.Description AS VARCHAR(255) )

FROM @TransPartUpdate T

   LEFT JOIN Part P ON (P.ID = T.PartID)

WHERE P.Description IS NOT NULL

;

UPDATE @TransPartUpdate

SET Description = CAST( PL.Description AS VARCHAR(255) )

FROM @TransPartUpdate T

   LEFT JOIN GoodsItemPartLink PL ON (PL.ID = T.PartLinkID AND PL.ClassTypeID = T.PartLinkClassTypeID)

WHERE PL.Description IS NOT NULL

;

UPDATE @TransPartUpdate

SET Description = CAST¹⁾) AS VARCHAR(255))

FROM @TransPartUpdate T

   LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedRDDetailID)

WHERE TD.Description IS NOT NULL

;

UPDATE @TransPartUpdate

SET Description = CAST²⁾) AS VARCHAR(255))

FROM @TransPartUpdate T

   LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedPODetailID)

WHERE TD.Description IS NOT NULL

;

UPDATE @TransPartUpdate

SET Description = CAST³⁾) AS VARCHAR(255))

FROM @TransPartUpdate T

   LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedBillDetailID)

WHERE TD.Description IS NOT NULL

;

DELETE FROM @TransPartUpdate

WHERE Description IS NULL

;

UPDATE TransPart

SET Description = T.Description

FROM TransPart TP

   LEFT JOIN @TransPartUpdate T ON (T.ID = TP.ID)

WHERE TP.ID IN (SELECT ID FROM @TransPartUpdate)

;

UPDATE TransDetail

SET PartStr = NULL

WHERE ID IN (SELECT TransDetailID FROM @TransPartUpdate)

code

• Entered : 04/05/2010
• Version :

• Backlinks include_pagepage_componentbacklinks