No Column Name was specified for Column 10

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

No Column Name was specified for Column 10

Author

Topic

In2Minds
Starting Member

17 Posts

Posted - 2014-02-10 : 15:27:04

Hi all

Can anyone help please?

KH (a very kind member on here), helped write a script that gave me a running total.

I think it is almost there but am getting an error message:

No Column Name was specified for Column 10.

I think it is to do with alias naming but I am not sure.

Does anyone have the answer?

Here is the code:

; with cte as
(
SELECT NLAccountPeriodValue.SYSAccountingPeriodID,
SYSAccountingPeriod.PeriodNumber,
SYSFinancialYear.SYSFinancialYearID,
SYSAccountingPeriod.StartDate,
SYSAccountingPeriod.EndDate,
NLNominalAccount.AccountNumber,
NLAccountPeriodValue.BudgetValue,
NLAccountPeriodValue.ActualValue,
NLAccountPeriodValue.AdjustmentAfterYearEndClose,
Sum(NLAccountPeriodValue.AdjustmentAfterYearEndClose+NLAccountPeriodValue.ActualValue) AS 'Total'
FROM Sage200SDBIS.dbo.NLAccountPeriodValue NLAccountPeriodValue
INNER JOIN Sage200SDBIS.dbo.NLNominalAccount NLNominalAccount
ON NLNominalAccount.NLNominalAccountID = NLAccountPeriodValue.NLNominalAccountID
INNER JOIN Sage200SDBIS.dbo.SYSAccountingPeriod SYSAccountingPeriod
ON SYSAccountingPeriod.SYSAccountingPeriodID = NLAccountPeriodValue.SYSAccountingPeriodID
INNER JOIN Sage200SDBIS.dbo.SYSFinancialYear SYSFinancialYear
ON SYSFinancialYear.SYSFinancialYearID = SYSAccountingPeriod.SYSFinancialYearID
WHERE NLNominalAccount.AccountNumber = '106018'
GROUP BY NLAccountPeriodValue.SYSAccountingPeriodID,
SYSAccountingPeriod.PeriodNumber,
SYSFinancialYear.SYSFinancialYearID,
SYSAccountingPeriod.StartDate,
SYSAccountingPeriod.EndDate,
NLNominalAccount.AccountNumber,
NLAccountPeriodValue.BudgetValue,
NLAccountPeriodValue.ActualValue,
NLAccountPeriodValue.AdjustmentAfterYearEndClose
)
SELECT c.SYSAccountingPeriodID,
c.PeriodNumber,
c.SYSFinancialYearID,
c.StartDate,
c.EndDate,
c.AccountNumber,
c.BudgetValue,
c.ActualValue,
c.AdjustmentAfterYearEndClose
c.Total,
r.RunningTotal
FROM cte c
CROSS APPLY
(
SELECT SUM(Total) AS RunningTotal
FROM cte x
WHERE x.AccountNumber = c.AccountNumber
AND x.SYSAccountingPeriodID <= c.SYSAccountingPeriodID
) r

Would really appreciate some help.

Cheers
Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 15:49:28

You appear to be missing a comma:
c.AdjustmentAfterYearEndClose <--right here
c.Total,

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

In2Minds
Starting Member

17 Posts

Posted - 2014-02-10 : 18:30:55

Hi tara

Thanks for replying.

Yes good spot, however still get the same error message when I add it in.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 00:52:04

I dont think that error is caused in posed code. I can see you've already given an alias for 10 column. Are you running this as a part of batch? if yes, error might be from some other query which is part of the batch.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

In2Minds
Starting Member

17 Posts

Posted - 2014-02-11 : 01:22:38

Hi V

Not part of any batch, just a single query being run.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 01:23:35

quote:
Originally posted by In2Minds

Hi V

Not part of any batch, just a single query being run.

Thanks

And can i confirm above posted code was the EXACT code you were trying to execute?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

In2Minds
Starting Member

17 Posts

Posted - 2014-02-11 : 01:32:26

Hi V

Yes this is the exact code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 02:56:36

Its working fine for me. Whats your sql version?
try


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your databasename here'
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2014-02-11 : 03:31:09

We don't used quoted identifiers, so I don't know the answer, but is this style of quoting OK?


Sum(NLAccountPeriodValue.AdjustmentAfterYearEndClose+NLAccountPeriodValue.ActualValue) AS 'Total'

We use square brackets, so as a quick test perhaps try


AS [Total]

In2Minds
Starting Member

17 Posts

Posted - 2014-02-11 : 05:48:24

Hi Kristen / visakh16.

Thanks so much for taking the time out to help me out, I really appreciate it.

It works now, as Kristen suggested it related to the brackets, changing to [Total] rather than 'Total' actually works

cheers both
Have a good day,
Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 23:34:56

quote:
Originally posted by In2Minds

Hi Kristen / visakh16.

Thanks so much for taking the time out to help me out, I really appreciate it.

It works now, as Kristen suggested it related to the brackets, changing to [Total] rather than 'Total' actually works

cheers both
Have a good day,
Mark

Ok good
I also dont use ' for aliases . I use []
but i've seen that working didnt understand why it caused issues in your case.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources