asked on

insert statement problem

I have an insert statement below and am trying to add the Comments field but am getting an error of fewer items on the insert list. I want to insert this part into comments: p.Productname + ' - ' + g.GrowthAbbreviation AS OrderComment

Where am I going wrong?

INSERT INTO WW.ClientOrderDetails
(DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Instructions, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount,OrderID,SpecialDiscount, 
UnitPrice,TotalPrice, Comments)
SELECT     DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Comments, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount, OrderID, 
CASE @AccountType WHEN 1 THEN 0 ELSE VolumeDiscount END,
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2) AS UnitPrice, 
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2)* Quantity AS TotalPrice
FROM (SELECT     i.DeliveryDateRequest, p.ProductID, g.GrowthStageID, i.StandingOrder, i.Comments, i.Quantity, i.ClientSpecialDiscount, i.ResellerSpecialDiscount, 
              t.OrderID, po.PaymentDiscount, 
	CASE g.GrowthStageID 
WHEN 1 THEN p.PriceMG 
WHEN 2 THEN p.PriceHG 
WHEN 3 THEN p.PriceBG 	ELSE p.ProductPrice END AS ProductPrice, 
CASE WHEN i.Quantity < 5 THEN 0 WHEN i.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount,
p.Productname + ' - ' + g.GrowthAbbreviation AS OrderComment
FROM          WW.ClientOrderImport AS i INNER JOIN
              WW.ClientOrders AS o ON i.ClientOrderNumber = o.ClientOrderNumber INNER JOIN
              @TmpTable AS t ON o.OrderID = t.OrderID INNER JOIN
              WW.GrowthStage AS g ON i.GrowthStage = g.GrowthAbbreviation INNER JOIN
              WW.Products AS p ON i.Product = p.Productname INNER JOIN
              WW.PaymentOptions AS po ON o.PaymentOptionID = po.PaymentOptionID
WHERE      (i.SelectItem = 1)) AS Z

Open in new window

edtechdba

Does this work? Adding blank value into OrderComment field

INSERT INTO WW.ClientOrderDetails
(DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Instructions, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount,OrderID,SpecialDiscount, 
UnitPrice,TotalPrice, Comments)
SELECT     DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Comments, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount, OrderID, 
CASE @AccountType WHEN 1 THEN 0 ELSE VolumeDiscount END,
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2) AS UnitPrice, 
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2)* Quantity AS TotalPrice
FROM (SELECT     i.DeliveryDateRequest, p.ProductID, g.GrowthStageID, i.StandingOrder, i.Comments, i.Quantity, i.ClientSpecialDiscount, i.ResellerSpecialDiscount, 
              t.OrderID, po.PaymentDiscount, 
	CASE g.GrowthStageID 
WHEN 1 THEN p.PriceMG 
WHEN 2 THEN p.PriceHG 
WHEN 3 THEN p.PriceBG 	ELSE p.ProductPrice END AS ProductPrice, 
CASE WHEN i.Quantity < 5 THEN 0 WHEN i.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount,
'' AS OrderComment
FROM          WW.ClientOrderImport AS i INNER JOIN
              WW.ClientOrders AS o ON i.ClientOrderNumber = o.ClientOrderNumber INNER JOIN
              @TmpTable AS t ON o.OrderID = t.OrderID INNER JOIN
              WW.GrowthStage AS g ON i.GrowthStage = g.GrowthAbbreviation INNER JOIN
              WW.Products AS p ON i.Product = p.Productname INNER JOIN
              WW.PaymentOptions AS po ON o.PaymentOptionID = po.PaymentOptionID
WHERE      (i.SelectItem = 1)) AS Z

Open in new window

If so, then this should do the trick. Wrapping your order comment string in parentheses.

INSERT INTO WW.ClientOrderDetails
(DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Instructions, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount,OrderID,SpecialDiscount, 
UnitPrice,TotalPrice, Comments)
SELECT     DeliveryDateRequest, ProductID, GrowthStageID, StandingOrder, Comments, 
Quantity, ClientSpecialDiscount, ResellerSpecialDiscount, OrderID, 
CASE @AccountType WHEN 1 THEN 0 ELSE VolumeDiscount END,
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2) AS UnitPrice, 
ROUND(((ProductPrice - ProductPrice * VolumeDiscount) - (ProductPrice - ProductPrice * VolumeDiscount) * PaymentDiscount), 2)* Quantity AS TotalPrice
FROM (SELECT     i.DeliveryDateRequest, p.ProductID, g.GrowthStageID, i.StandingOrder, i.Comments, i.Quantity, i.ClientSpecialDiscount, i.ResellerSpecialDiscount, 
              t.OrderID, po.PaymentDiscount, 
	CASE g.GrowthStageID 
WHEN 1 THEN p.PriceMG 
WHEN 2 THEN p.PriceHG 
WHEN 3 THEN p.PriceBG 	ELSE p.ProductPrice END AS ProductPrice, 
CASE WHEN i.Quantity < 5 THEN 0 WHEN i.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount,
(p.Productname + ' - ' + g.GrowthAbbreviation) AS OrderComment
FROM          WW.ClientOrderImport AS i INNER JOIN
              WW.ClientOrders AS o ON i.ClientOrderNumber = o.ClientOrderNumber INNER JOIN
              @TmpTable AS t ON o.OrderID = t.OrderID INNER JOIN
              WW.GrowthStage AS g ON i.GrowthStage = g.GrowthAbbreviation INNER JOIN
              WW.Products AS p ON i.Product = p.Productname INNER JOIN
              WW.PaymentOptions AS po ON o.PaymentOptionID = po.PaymentOptionID
WHERE      (i.SelectItem = 1)) AS Z

Open in new window

Shawn

ASKER

no, this doesn't even work. That is the strange thing. maybe I have a syntax error somewhere but can't see it

ASKER CERTIFIED SOLUTION

edtechdba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION