Help! Novice working on SQL Report.

I am working on creating an SQL report from one table which will list account information and create subtotals for each group of accounts sorted by the first two digits of the account number and, at the end of the report, provide a grand total.  I thought I was doing well but I have run into a wall on the compute and subtotal code and I'm not very comfortable with what I have fashioned for the 2-digit sort.  

Here is my table structure:
Account (float) 8
Description (nvarchar) 255
Short_Desc (nvarchar) 255
Balance (money) 8,2

Here is my SQL code for my view:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER   VIEW POS410_TEAMA.COA_REPORT
AS
SELECT CAST(ACCOUNT AS varchar(6)) AS 'Account Number',
DESCRIPTION AS Account, SHORT_DESCRIPTION AS 'Account Description',
CAST(BALANCE AS DECIMAL(8,2)) AS 'Account Balance'  
FROM CHART_OF_ACCOUNTS
GROUP BY DESCRIPTION, SHORT_DESCRIPTION, BALANCE, ACCOUNT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 

Here is the error I've gotten so far:
Server: Msg 170, Level 15, State 1, Procedure COA_REPORT, Line 8
Line 8: Incorrect syntax near '('.

Any steering in the right direction will be appreciated. I apologize in advance for clearly being a rough novice.
Thank you!
cyndylove46Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
raj_Connect With a Mentor Commented:
try this

SELECT CAST(cast(ACCOUNT as decimal(38,18)) AS varchar(56)) AS 'Account Number',
Descp AS Account, Short_Desc AS 'Account Description',
CAST(BALANCE AS DECIMAL(8,2)) AS 'Account Balance'  
FROM CHART_OF_ACCOUNTS
GROUP BY Descp, Short_Desc, BALANCE,  CAST(cast(ACCOUNT as decimal(38,18)) AS varchar(56))

why do u have account as a float???(i cast the float as a decimal and then into vsrchar)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Your query looks like ok, the only error i am getting is  invalid column name 'SHORT_DESCRIPTION', if you change it to 'SHORT_DESC' the error is gone ...
0
 
cyndylove46Author Commented:
This was my error.  I misrepresented the field design and it actually was SHORT_DESCRIPTION....so that kind of takes me back to my question, (I thank you for pointing out the additional error I supplied in my question - smile).

Unfortunately, I still have the syntax error and can't figure out what I'm doing wrong.

Here is the error again:
Server: Msg 170, Level 15, State 1, Procedure COA_REPORT, Line 8
Line 8: Incorrect syntax near '('.

0
 
cyndylove46Author Commented:
I've updated my code and (I think) gotten closer to where I need to be...but more errors.  Please look at my new code and comment (HELPE!!!!):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER   VIEW POS410_TEAMA.COA_REPORT
AS
SELECT CAST(LEFT(ACCOUNT,2) AS varchar(6)) AS 'Account Number', DESCRIPTION AS Account, SHORT_DESCRIPTION AS 'Account Description', CAST(BALANCE AS DECIMAL(8,2)) AS 'Account Balance'  
FROM CHART_OF_ACCOUNTS
GROUP BY Account, SHORT_DESCRIPTION, BALANCE, Description
BREAK ON REPORT ON ACCOUNT SKIP 1
SET FEEDBACK OFF
COMPUTE SUM OF BAL ON BALANCE
COMPUTE SUM OF BAL ON REPORT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 
cyndylove46Author Commented:
Thank you for this tip.  The "float" data field defaulted to that data type when I imported my data from an Excel doc to my SQL database.  Here is what really frustrates me, I read in my text that I should be able to group my sort data for my report using a break command, however, when I compile my data I get an error stating that a break must be part of a WHILE statement.  What am I dong wrong?

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.