Coding Issue changes required 1

[Cpreston]

Hi

Sorry I am not up on large programming like this but need to try and change a large view. The sql code is below and apologies for the size it is.

This is used by a job to update a table called 148-salesanalysisreportdata

I need the code to include somehow if the OrderLine.ManualDescription is empty then to update it with the Product.Description instead. Could someone please help me changing the code. Sorry it is such an ask but I really have no idea how to change the code to do this. Thanks in advance



SELECT Branch.BranchCode, OrderHeader.OrderNumber, Branch.Name, Customer.CustomerID, LEFT(dbo.CustomerGroup.Name, 3)
AS udfBuyingGroupCode, Customer.Name AS Expr1, SalesRep.Name AS Expr2, Users.Name AS Expr3, OrderHeader.BranchID, OrderHeader.SalesRepID,
OrderHeader.UserID, OrderHeader.DateRequiredOption, OrderHeader.DateRequired, OrderLine.SellingCalcType, OrderLine.TotalSellPrice, OrderLine.TotalCostPrice,
OrderLine.Quantity, OrderLine.LineType, Product.Description, OrderLine.ManualDescription, OrderLine.UnitSellPrice, Per_1.PerCode, SalesRep.SalesRepCode,
Customer.CustomerCode, OrderHeader.CustomerRef, OrderLine.ManualCode, Product.ProductCode, OrderLine.LineNumber, OrderLineOption.OptionID,
OrderLineOption.AVOName, OrderLineOption.AVOOptionName, OrderLineOption.PackRef, OrderLineOption.PackQuantityTally, OrderLine.Notes,
OrderLine.InternalNotes, OrderLine.UnitCostPrice, Per_2.PerCode AS Expr4, Per.PerCode AS Expr5, SaleType.Name AS Expr6, OrderHeader.TotalVolume,
OrderHeader.SpecialInstructions, Product.ProductGroupID, VehicleType.Name AS Expr7, OrderHeader.OrderType, OrderHeader.Deleted,
OrderHeader.DateTimeCreated, OrderLine.TotalMargin, OrderHeader.DeliveryAddress, CASE WHEN orderheader.BackOrderedFromID IS NULL
THEN '' ELSE '(Back Order)' END AS BackOrder
FROM dbo.CustomerGroup INNER JOIN
dbo.CustomerGroupMember ON dbo.CustomerGroup.CustomerGroupID = dbo.CustomerGroupMember.CustomerGroupID RIGHT OUTER JOIN
dbo.[148-vwProductGroupSubgroup] WITH (NOLOCK) INNER JOIN
dbo.ProductGroup WITH (NOLOCK) ON dbo.[148-vwProductGroupSubgroup].[Group] = dbo.ProductGroup.Name RIGHT OUTER JOIN
dbrderHeader AS OrderHeader WITH (NOLOCK) INNER JOIN
dbo.Customer AS Customer WITH (NOLOCK) ON OrderHeader.CustomerID = Customer.CustomerID INNER JOIN
dbo.Branch AS Branch WITH (NOLOCK) ON OrderHeader.BranchID = Branch.BranchID INNER JOIN
dbo.SalesRep AS SalesRep WITH (NOLOCK) ON OrderHeader.SalesRepID = SalesRep.SalesRepID INNER JOIN
dbo.Users AS Users ON OrderHeader.UserID = Users.UserID INNER JOIN
dbrderLine AS OrderLine WITH (NOLOCK) ON OrderHeader.OrderID = OrderLine.OrderID INNER JOIN
dbo.SaleType AS SaleType WITH (NOLOCK) ON OrderHeader.SaleType = SaleType.SaleTypeID ON
dbo.[148-vwProductGroupSubgroup].ProductID = OrderLine.ProductID ON dbo.CustomerGroupMember.CustomerID = Customer.CustomerID LEFT OUTER JOIN
dbo.Product AS Product WITH (NOLOCK) ON OrderLine.ProductID = Product.ProductID LEFT OUTER JOIN
dbo.Per AS Per WITH (NOLOCK) ON OrderLine.PerID = Per.PerID LEFT OUTER JOIN
dbo.Per AS Per_1 WITH (NOLOCK) ON OrderLine.InputPerID = Per_1.PerID LEFT OUTER JOIN
dbrderLineOption AS OrderLineOption WITH (NOLOCK) ON OrderLine.OrderLineID = OrderLineOption.OrderLineID LEFT OUTER JOIN
dbo.Per AS Per_2 WITH (NOLOCK) ON OrderLine.CostPerID = Per_2.PerID LEFT OUTER JOIN
dbo.VehicleType AS VehicleType WITH (NOLOCK) ON OrderHeader.VehicleTypeID = VehicleType.VehicleTypeID
WHERE (OrderHeader.OrderType = 1) AND (OrderHeader.Deleted = 0) AND (OrderHeader.Deleted = 0) AND (OrderHeader.DateTimeCreated > DATEADD(day, - 1, GETDATE()))
AND (OrderHeader.DateTimeCreated < DATEADD(day, 1, GETDATE()))
ORDER BY OrderHeader.BranchID, OrderHeader.SalesRepID, OrderHeader.UserID, Customer.CustomerCode, OrderHeader.OrderNumber, OrderLine.LineNumber,
OrderLineOption.OptionID

 

[dhookom]

You should be able to use

IsNull(OrderLine.ManualDescription,Product.Description) As ManualDescription

Duane
Hook'D on Access
MS Access MVP

 

[Cpreston]

Hi

where in the code would this go

many thanks

 

[Cpreston]

Hi

Its ok I think we have solved it, it runs from a job and we have added the code into the script

UPDATE 148-salesanalysisreportdata SET ManualDescription = ProductDescription where ManualDescription is null
go


This appears to work

Thanks for the reply

 

[dhookom]

I answered your question based on your "SELECT" statement and assumed you only wanted to display ProductDescription where there was no ManualDescription. I'm not sure you SQL will run as you provided.

SQL Books Online suggests this syntax for updates involving more than one table:

UPDATE dbo.Table2 
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2 
    INNER JOIN dbo.Table1 
    ON (dbo.Table2.ColA = dbo.Table1.ColA);

If this is your actual table name 148-salesanalysisreportdata, then you will need to wrap the name in []s.

Duane
Hook'D on Access
MS Access MVP