Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

SQL Query

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
InfoTechEE
Asked:
InfoTechEE
1 Solution
 
d_j_twilightCommented:
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
 
InfoTechEEAuthor Commented:
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
 
cyberkiwiCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
cyberkiwiCommented:
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
 
itcoupleCommented:
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
 
InfoTechEEAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now