incorrect syntax

[tonyvee1973]

Hi All

I have a n error message when running the script below, it states "Msg 102, Level 15, State 1, Line 27 Incorrect syntax near ','."
Can anyone point me in the right direction please as this is my first time grouping?
Many thanks


SELECT dbo.SL_ACCOUNTS.CUCODE, dbo.SL_ACCOUNTS.CUCODE AS HeadOfficeAccount, dbo.SL_ACCOUNTS.CUNAME, dbo.ps_addresses.AddressLine1,
dbo.ps_addresses.AddressLine2, dbo.ps_addresses.AddressLine3, dbo.ps_addresses.AddressLine4, dbo.ps_addresses.AddressLine5,
dbo.SL_ACCOUNTS.CUPHONE, dbo.SL_ACCOUNTS.CUFAX, dbo.SL_ACCOUNTS.CU_TOT_DISC, dbo.SL_ACCOUNTS.CU_SETT_DISC_1,
dbo.SL_ACCOUNTS.CU_CREDIT_LIMIT, '' AS DaysForNextVisit, '' AS CompanyRegNumber, '' AS BankName, '' AS BankAddress1, '' AS BankAddress2,
'' AS BankTown, '' AS BankCounty, '' AS BankPostCode, '' AS BankTelephone, '' AS BankAccount, '' AS BankSortCode, '' AS ProprietorsName,
'' AS ProprietorsAddress1, '' AS ProprietorsAddress2, '' AS ProprietorsTown, '' AS ProprietorsCounty, '' AS ProprietorsPostCode,
'' AS ProprietorsTelephone, dbo.SL_ACCOUNTS.CUCONTACT, REPLACE(REPLACE(dbo.SL_ACCOUNTS.CU_ON_STOP, '0', 'N'), '1', 'Y') AS CU_ON_STOP,
dbo.SL_ACCOUNTS.CU_PRICE_KEY AS PricelistNumber, dbo.SL_ACCOUNTS.CUUSER1, dbo.SL_ACCOUNTS.CUTURNOVERPTD,
dbo.SL_ACCOUNTS.CUTURNOVERYTD, '' AS LastYearSales, dbo.SL_ACCOUNTS.CUBALANCE, '' AS Notes1, '' AS Notes2,
dbo.SL_ACCOUNTS.CUCURRENCYCODE, '' AS Tax, '' AS ConfirmOrders, '' AS GenericCode3, '' AS GenericCode4, REPLACE(CONVERT(varchar(255),
dbo.SL_ACCOUNTS.CU_NOTES), CHAR(13), ' ') AS Freetype1, '' AS Freetype2, dbo.SL_ACCOUNTS.CU_EMAIL,
dbo.SL_ACCOUNTS.CUUSER2 AS PriceCode, '' AS DiscountCode, dbo.SL_ACCOUNTS.CUAGED_1 AS CurrentMonthBalance,
dbo.SL_ACCOUNTS.CUAGED_2 AS [Month+1], dbo.SL_ACCOUNTS.CUAGED_3 AS [Month+2], dbo.SL_ACCOUNTS.CUAGED_4 AS [Month+3],
dbo.SL_ACCOUNTS.CU_DATE_INV, '' AS AmountLastInvoice, dbo.SL_ACCOUNTS.CU_DATE_PAY, '' AS AmountLastPayment, '' AS MinOrderValue,
dbo.SL_ACCOUNTS2.CU_FLAG1 AS PORefRequired, '' AS NoteDateRequired, '' AS PostOrderDiscount, 'N' AS UsrBreakDisc, '' AS JournalGroupCode,
'' AS TaxRegion, '' AS DefaultLocationCode, '' AS DefaultGeneric18, '' AS DefaultGeneric19, '' AS DefaultGeneric20, '' AS DefaultGeneric21,
'' AS DefaultGeneric22, '' AS DefaultGeneric23, dbo.SL_ACCOUNTS2.CU_USRFLAG2
FROM dbo.SL_ACCOUNTS INNER JOIN
dbo.ps_addresses ON dbo.SL_ACCOUNTS.CUCODE = dbo.ps_addresses.AccountNumber INNER JOIN
dbo.SL_ACCOUNTS2 ON dbo.SL_ACCOUNTS.CUCODE = dbo.SL_ACCOUNTS2.CUCODE2 INNER JOIN
dbRD_HEADER ON dbo.SL_ACCOUNTS.CUCODE = dbRD_HEADER.OH_ACCOUNT

GROUP BY dbo.SL_ACCOUNTS.CUCODE, dbo.SL_ACCOUNTS.CUCODE, dbo.SL_ACCOUNTS.CUNAME, dbo.ps_addresses.AddressLine1, dbo.ps_addresses.AddressLine2, dbo.ps_addresses.AddressLine3,
dbo.ps_addresses.AddressLine4, dbo.ps_addresses.AddressLine5, dbo.SL_ACCOUNTS.CUPHONE, dbo.SL_ACCOUNTS.CUFAX, dbo.SL_ACCOUNTS.CU_TOT_DISC, dbo.SL_ACCOUNTS.CU_SETT_DISC_1,
dbo.SL_ACCOUNTS.CU_CREDIT_LIMIT, dbo.SL_ACCOUNTS.CUCONTACT, dbo.SL_ACCOUNTS.CU_ON_STOP, dbo.SL_ACCOUNTS.CU_PRICE_KEY, dbo.SL_ACCOUNTS.CUUSER1,
dbo.SL_ACCOUNTS.CUTURNOVERPTD, dbo.SL_ACCOUNTS.CUTURNOVERYTD, dbo.SL_ACCOUNTS.CUBALANCE, dbo.SL_ACCOUNTS.CUCURRENCYCODE, REPLACE(CONVERT(varchar(255), dbo.SL_ACCOUNTS.CU_NOTES,
dbo.SL_ACCOUNTS.CU_EMAIL, dbo.SL_ACCOUNTS.CUUSER2, dbo.SL_ACCOUNTS.CUAGED_1, dbo.SL_ACCOUNTS.CUAGED_2, dbo.SL_ACCOUNTS.CUAGED_3, dbo.SL_ACCOUNTS.CUAGED_4,
dbo.SL_ACCOUNTS.CU_DATE_INV, dbo.SL_ACCOUNTS.CU_DATE_PAY, dbo.SL_ACCOUNTS2.CU_FLAG1, dbo.SL_ACCOUNTS2.CU_USRFLAG2

 

[djj55]

To investigate, first try reducing the query to one table then add the other tables (joins) one at a time.

What is your aggregate? If you do not have one a GROUP BY is not needed. If you are going to add an aggregate, you might try to split the query up.

I might suggest to use alias for tables.



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[tonyvee1973]

Apologies but that went right over my head

 

[MarkSweetland]

Missing closing parenthesis on the REPLACE on line 26:

REPLACE(CONVERT(varchar(255), dbo.SL_ACCOUNTS.CU_NOTES[red]))[/red],

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[tonyvee1973]

Thanks Mark but now another error. Now states "Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'CU_USRFLAG2'

 

[gmmastros]

Mark's advice was spot on regarding the unclosed functions but the replace function requires 3 arguments and you only have the first one. Based on the select clause, I recommend you replace Mark's suggestion with this:

REPLACE(CONVERT(varchar(255), dbo.SL_ACCOUNTS.CU_NOTES)[!], CHAR(13), ' ')[/!],

Like I said, this is an educated guess based on the replace you are using in the select clause.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkSweetland]

I do agree with djj55 that you don't need the GROUP BY clause unless you have an aggregate (SUM, Count, etc) in the SELECT. You can use this method to get a distinct list of all the columns in the GROUP BY, but I'd remove the whole GROUP BY and run the query again to see if the error is within it or not.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

What is your aggregate? If you do not have one a GROUP BY is not needed. If you are going to add an aggregate, you might try to split the query up.

Apologies but that went right over my head

Let's talk about grouping because it appears as though you don't really understand it. If you do understand grouping, then please feel free to ignore the rest of this post.

Grouping is useful when you want to perform calculations on groups of data. For example, suppose you sell different widgets. Also suppose you have a table that tracks the in-stock quantities of your widgets. When widgets are delivered, you have positive quantities added to your stock of widgets. When you sell widgets, they are removed from your stock. So... you could have a table with WidgetId (because you sell multiple different widgets), and another column for Quantity. A positive quantity indicates you received widgets from a wholesaler. Negative quantities indicate you sold widgets. You could have a table that looks like this...

WidgetId Quantity
-------- --------
1 1000
2 300
1 -10
1 -30
2 -7
2 -1

Now, suppose you wanted to know how many of each widget you have in stock. The query would look something like:

Select   WidgetId,
         Sum(Quantity) As InStockQuantity
From     WidgetQuantities
Group By WidgetId

The results of this query would be:

WidgetId InStockQuantity
-------- ---------------
1 960
2 292

In the query shown above, the SUM function is considered an aggregate. Other aggregate functions are Min, Max, Count, and Avg. There is a rule when working with group by queries. The rule is... Whatever is in your select clause MUST appear in the Group By clause unless it is an aggregate function. Taking another look at the query above, WidgetId is not part of the aggregate, so it must appear in the group by. Quantity is part of the aggregate, so it does not appear in the group by. You can include quantity in the group by, but that would not give you the results you expect.

Now, let's take a look at the output. There will be exactly 1 row in the output for each combination of data in the group by. In this case we are only grouping by 1 column (WidgetId) so there will only be 1 row in the output for each widget.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonyvee1973]

Thanks to all of you for your help, its been great! And thanks to gmmastros for taking the time to explain - i now get it!