Solved

Help!  Novice working on SQL Report.

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now