cast issue

[tonyvee1973]

Hi
I am trying to sort a grouping out but cant get the syntax right for the CAST function.
Below is what i'm trying to convert but keep getting the error "Column 'dbo.SL_ACCOUNTS.CU_NOTES' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"


group by 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, Cast (dbo.SL_ACCOUNTS.CU_NOTES as varchar)

 

[IanWaterman]

You must either change your select to

Max(Cast (dbo.SL_ACCOUNTS.CU_NOTES as varchar))

Or add , Cast (dbo.SL_ACCOUNTS.CU_NOTES as varchar) to your group by list.

Ian

 

[tonyvee1973]

Hi Ian
I already have this in my select as below:

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,
sum(cuturnovr_L1 + cuturnovr_l2) 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

and tried what you said in the group by but that doesnt work either, just get the error:

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.SL_ACCOUNTS.CU_NOTES' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[gmmastros]

Please be careful about casting to varchar. When you do, you should ALWAYS include a size. When casting to varchar (without a size), the default size of 30 characters is used. In your followup post, you appear to be converting to varchar(255). The equivalent cast syntax would be:

Cast (dbo.SL_ACCOUNTS.CU_NOTES as varchar[!](255)[/!])

Take a look at this example.... There are 30 numbers and then the text "Where is the rest of this". When you cast to varchar without a size, the data is silently truncated.

Select Cast('123456789012345678901234567890[!] Where is the rest of this?[/!]' as VarChar)

-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]

Hi
Ok, managed to get to the bottom of it.
In sql changed from text to varchar then in the script i changed:
REPLACE(CONVERT(varchar(255), dbo.SL_ACCOUNTS.CU_NOTES), CHAR(13), '') AS Freetype1
to simply:
dbo.SL_ACCOUNTS.CU_NOTES AS Freetype1

No conversion needed then. ALl good