Solved

Help!  Novice working on SQL Report.

Posted on 2006-07-08
5
272 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT INTO from XML 6 46
Database Availability Group Distribution 9 45
SQL Store Procedure - Slow Performance 13 54
Server 2012 r2 and SQL 2014 6 30
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 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