Solved

Help!  Novice working on SQL Report.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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