Solved

Help!  Novice working on SQL Report.

Posted on 2006-07-08
5
274 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:cyndylove46
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17064813
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
 

Author Comment

by:cyndylove46
ID: 17066612
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
 

Author Comment

by:cyndylove46
ID: 17066705
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
 
LVL 8

Accepted Solution

by:
raj_ earned 500 total points
ID: 17067601
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
 

Author Comment

by:cyndylove46
ID: 17067724
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

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question