Problem converting Access SQL query to T-SQL 2005

15 pts.
Tags:
Microsoft Access
SQL
SQL Server 2005
T-SQL
I have a SQL Server 2005 database. One of my co-workers has put a MS Access front-end on it, because of the ease of designing queries in Access. I need to convert an existing query from Access SQL to T-SQL to put it into production. So far, I have removed all the obvious differences between the 2, such as replacing IIF with CASE and UCASE$ with UPPER, but still have errors. According to the errors generated, apparently my alias fields are not being created correctly, as I get an error on line 5 that my Department_Essential field does not exist. Does anyone have any suggestions? SELECT dbo.vwDisplayUserListAllFields.DEPT_DESC, SUM(CASE WHEN UPPER(Essential_Code)='D' THEN 1 ELSE 0 END) AS 'Department_Essential', SUM(CASE WHEN UPPER(Essential_Code)='E' THEN 1 ELSE 0 END) AS 'EOC_Essential', COUNT(dbo.vwDisplayUserListAllFields.UserID) AS 'Total_Employees', ([Department_Essential]/[Total_Employees]*100) AS [%Department_Essential], ([Department_Essential]/[Total_Employees]*100) AS [%EOC_Essential] FROM dbo.vwDisplayUserListAllFields GROUP BY dbo.vwDisplayUserListAllFields.DEPT_DESC ORDER BY dbo.vwDisplayUserListAllFields.DEPT_DESC

Answer Wiki

Thanks. We'll let you know when a new response is added.

Department_Essential is a <b>column alias</b>, and column aliases are not allowed to be referenced in the same SELECT clause

(well, they are allowed in Access, and there’s the source of your problem)

so just “push down” the calculations into a subquery:
<pre>SELECT DEPT_DESC
, Department_Essential
, EOC_Essential
, Total_Employees
, 100.0 * Department_Essential / Total_Employees
AS [%Department_Essential]
, 100.0 * EOC_Essential / Total_Employees
AS [%EOC_Essential]
FROM (
SELECT DEPT_DESC
, SUM(CASE WHEN UPPER(Essential_Code) = ‘D’
THEN 1
ELSE 0 END) AS Department_Essential
, SUM(CASE WHEN UPPER(Essential_Code) = ‘E’
THEN 1
ELSE 0 END) AS EOC_Essential
, COUNT(UserID) AS Total_Employees
FROM dbo.vwDisplayUserListAllFields
GROUP
BY DEPT_DESC
) AS d
ORDER
BY DEPT_DESC </pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chunkydrew
    That works great! Thank you for your help. The only thing I need to work on now is converting the percentages to whole numbers, i.e. 88, rather than 88.03..... I removed the .0 after the multiplication by 100, and ended up with 11% and 88% - not quite 100%. I'll work on that. Again, thanks for your help.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following