Solved

SQL Query

Posted on 2010-08-30
6
297 Views
Last Modified: 2012-05-10
Please see the query below. It runs perfectly fine in MS SQL Server Management Studio, but when I try to create a report out of it, it gives me a bunch of errors when I try to execute it in SQL Server Business Intelligence Development Studio, such as:

Incorrect syntax near 'InvSum'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'AS'.

When I go into Query editor to see what the problem might be, I notice it converts all of my fields from the FS and PD tables into: (See examples below)
FS. 'InvSum'
PD. 'CountFirms' AS 'InvCount'
PD. 'MaxPostDate'
PD. 'CountFirms'

Notice how there is a space after the Table Alias and the Field name, and notice how it wraps the Field name in single quotes.

Not sure what's going on, any help would be appreciated.
SELECT F.FirmNo, F.FirmName, S.StateAbbr, Co.CodeName as FirmStatus, FS.InvSum, 

CASE 

WHEN DateDiff(dd, convert(varchar, PD.MaxPostDate, 101),getdate()) BETWEEN 25 AND 54 THEN '30'		 

WHEN DateDiff(dd, convert(varchar, PD.MaxPostDate, 101),getdate()) BETWEEN 55 AND 75 THEN '60'

WHEN DateDiff(dd, convert(varchar, PD.MaxPostDate, 101),getdate()) BETWEEN 76 AND 105 THEN '90'

WHEN DateDiff(dd, convert(varchar, PD.MaxPostDate, 101),getdate()) > 105 THEN '120'

END AS 'Letter', 

PD.CountFirms as 'InvCount', 

CASE WHEN PD.CountFirms >= 32 THEN 'MANUAL' ELSE 'AUTO' END AS 'InvCount'



FROM Firms F LEFT OUTER JOIN Contacts C ON C.FirmNo = F.FirmNo

LEFT OUTER JOIN Invoices I ON I.BillContactNo = C.ContactNo

LEFT OUTER JOIN Code Co ON Co.CodeNo = F.AcctStatus

LEFT OUTER JOIN [State] S ON S.StateNo = F.StateNo

LEFT OUTER JOIN (

  SELECT F.FirmNo, SUM(I.Balance) as 'InvSum'

  FROM Invoices I LEFT OUTER JOIN Contacts C ON C.ContactNo = I.BillContactNo

  LEFT OUTER JOIN Firms F ON F.FirmNo = C.FirmNo

  WHERE I.VoidDate IS NULL

  AND I.PostDate IS NOT NULL

  AND I.Balance > 0

  AND I.InvType = 1120

  GROUP BY F.FirmNo) FS ON FS.FirmNo = F.FirmNo



LEFT OUTER JOIN (

  SELECT F.FirmNo, MIN(I.PostDate) as 'MaxPostDate', COUNT(*) as 'CountFirms'

  FROM Invoices I LEFT OUTER JOIN Contacts C ON C.ContactNo = I.BillContactNo

  LEFT OUTER JOIN Firms F ON F.FirmNo = C.FirmNo

  WHERE I.VoidDate IS NULL

  AND I.PostDate IS NOT NULL

  AND I.Balance > 0

  AND I.InvType = 1120

  GROUP BY F.FirmNo) PD ON PD.FirmNo = F.FirmNo			

WHERE I.VoidDate IS NULL

  AND I.PostDate IS NOT NULL

  AND I.Balance > 0

  AND I.InvType = 1120



GROUP BY PD.CountFirms, F.FirmNo, F.FirmName, Co.CodeName, S.StateAbbr, FS.InvSum, PD.MaxPostDate



HAVING DateDiff(dd, convert(varchar, PD.MaxPostDate, 101),getdate()) BETWEEN 25 AND 105

  AND FS.InvSum >= 100



ORDER BY Co.CodeName, S.StateAbbr, F.FirmName

Open in new window

0
Comment
Question by:InfoTechEE
6 Comments
 
LVL 1

Expert Comment

by:d_j_twilight
ID: 33563325
You have unnecessary single quotes around your AS targets.

e.g. Select FOO as 'BAR'
should be Select FOO as BAR

Remove the single quotes and see if it likes that better.
0
 

Author Comment

by:InfoTechEE
ID: 33563346
That didn't help. One of the other error messages that I have been receiving is:

The query contains mroe than one unnamed or duplicate field name.
Please specify unique column aliases.

I think it doesn't like the FS & PD tables. They are virtual tables afterall. Not virtual, but Aliases for a table created from a SELECT clause. Don't know if that makes any diference.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33563359
You have two 'InvCount'

Change one of them

PD.CountFirms as 'InvCount',
CASE WHEN PD.CountFirms >= 32 THEN 'MANUAL' ELSE 'AUTO' END AS 'InvCount2'
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33563375
And do remove the single quotes.

This is valid

select col1 as 'Alias1' from tbl

so is this

select col1 as Alias1 from tbl  -- no quotes

But this is not

select x.'Alias1'
from (
   select col1 as 'Alias1' from tbl ) AS x

The query tool is probably confused by the quotes and including them when generating SQL.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33568004
Hi

Just a comment based on what I recently read in Microsoft book (MCTS 2008 Database Development).... You can use single quotes for table/column aliases (SQL-92) but you shouldn't and you should stick to [] when you need them to avoid issues (I think something related to SET QUOTED_IDENTIFIER ON / SET QUOTED_IDENTIFIER OFF in SQL Server)

Not sure if this comment is relevant in this case but I thought I will share it.

Regards
Emil
0
 

Author Comment

by:InfoTechEE
ID: 33568663
Thanks to all you guys for the great advice, however, at the end of the day it was my own stupidity that killed me.

cyberkiwi was right. He had pointed out that I had two fiels with the same alias. After correcting that, everything started working OK.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

15 Experts available now in Live!

Get 1:1 Help Now