0

Trying to amend a stored procedure and getting this error

Msg 156, Level 15, State 1, Procedure webopius_OrderExport, Line 14
Incorrect syntax near the keyword 'left’.

The code is

ALTER proc [dbo].[webopius_OrderExport]           
AS      
   SET NOCOUNT ON
BEGIN
   SELECT 
      convert(varchar, getdate(), 103) ExtractDate, 
      o.StoreID, o.OrderNumber OrderNumber,    
      o.CustomerID, o.FirstName, o.LastName, o.Email, o.AffiliateID
      left(cast(o.OrderNotes as varchar(100)), 100) OrderNotes,
      o.ShippingFirstName, o.ShippingLastName, o.ShippingMethod,
      left(left(case o.ShippingCompany when '' then '' else o.ShippingCompany+' ' end,80)+o.ShippingAddress1, 100) ShippingAddress1,
      left(o.ShippingAddress2, 100) ShippingAddress2, left(o.ShippingSuite, 30) ShippingSuite, 
      left(o.ShippingCity, 50) ShippingCity, 
      left(o.ShippingState, 50) ShippingState, 
      left(o.ShippingZip, 13) ShippingZip, 
      o.ShippingCountry ShippingCountry, o.ShippingPhone,
      cast(isnull(a.ExtensionData,'') as nvarchar(200)) ShippingTitle,
      o.ShippingMethodID, 
      convert(varchar(10), o.OrderDate, 103) as OrderDate, 
      left(os.OrderedProductSKU, 40) SKU, 
      os.Quantity, 
      isnull(os.OrderedProductRegularPrice, 0) OrderedProductRegularPrice, 
      ISNULL(os.OrderedProductPrice,0) OrderedProductPrice, 
      isnull(os.OrderedProductSalePrice,0) OrderedProductSalePrice , 
      isnull(o.LevelHasNoTax, 0) LevelHasNoTax, 
      TaxRate, os.IsTaxable, OrderShippingCosts, o.OrderSubtotal SubTotal,
      left(upper(o.BillingFirstName + ' ' + o.BillingLastName), 80) AS BillingName, 
      left(left(case o.BillingCompany when '' then '' else o.BillingCompany+' ' end,80)+o.BillingAddress1,100) BillingAddress1,
      left(o.BillingAddress2, 100) BillingAddress2, 
      left(o.BillingSuite, 30) BillingSuite, 
      left(o.BillingCity, 50) BillingCity, 
      left(o.BillingState, 50) BillingState, 
      left(o.BillingZip, 8) BillingZip, 
      o.BillingCountry BillingCountry, o.BillingPhone,  
      o.CardType, o.CardName, o.CardNumber, 
      o.CardExpirationMonth+'/'+o.CardExpirationYear CardExpiryDate,
      o.CardStartDate, o.CardIssueNumber,
      o.PaymentMethod, o.LevelID, os.ProductID, os.VariantID,
      c.CustomerID, 
      '"' + isnull(cast(c.ExtensionData as nvarchar(200)), '') + '"' ExtensionData,
      m.Name Supplier, p.Name ProductName,  
      '"' + isnull(cast(p.ExtensionData as nvarchar(200)), '') + '"' ProdExtensionData
   FROM 
      dbo.Orders o with (NOLOCK)
   left join 
      orders_ShoppingCart os WITH (NOLOCK) ON os.OrderNumber = o.OrderNumber   
   left join 
      Customer c WITH (NOLOCK) on c.CustomerID = o.CustomerID 
   left join 
      Product p with (NOLOCK) on p.ProductID = os.ProductID
   left join 
      ProductManufacturer pm with (NOLOCK) on pm.ProductID = os.ProductID
   left join 
      Manufacturer m with (NOLOCK) on m.ManufacturerID = pm.ManufacturerID 
   left join 
      Address a with (NOLOCK) on a.AddressID = c.ShippingAddressID 
   WHERE 
      o.OrderNumber = os.OrderNumber 
      AND o.ShippedOn IS NULL 
      AND (o.TransactionState IN ('AUTHORIZED', 'CAPTURED') 
           OR (o.TransactionState = 'PENDING' and o.PaymentMethod='PURCHASEORDER'))
      AND IsNew = 1
      AND o.StoreID <> 4
   ORDER BY 
      ordernumber
END

The column does exist in the SQL table where the left command is shown.

Any ideas where I'm going wrong?

2 Answers 2

2

Your code reads at about line 20

 o.CustomerID, o.FirstName, o.LastName, o.Email, o.AffiliateID

 left(cast(o.OrderNotes as varchar(100)),100) OrderNotes,

it should read

 o.CustomerID, o.FirstName, o.LastName, o.Email, o.AffiliateID, --comma added here

 left(cast(o.OrderNotes as varchar(100)),100) OrderNotes,

Easy, missing comma

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks! Need more coffee I think!
1

You're missing a comma on the previous line, after o.AffiliateID.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.