SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Syntax errors on my complex query - please help		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/17/2010 :  10:30:39  Show Profile  Reply with Quote
Hello,

I have one complex query in MS Access. It works fine. However, when I run this in Sql server, it shows syntax errors. So, I change the Iff to Case, but it still shows errors.

This query calculates the number of reports on another query.

Can anyone please look at my modified version to see why it shows syntax errors when running on sql server?

Thanks.



In MS Access:

SELECT DISTINCT qry_reports.rep_Id, 

        qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate, 

        qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals, 

        (SELECT Iff(SUM(qry_reports_2.NumPeopleGo) IS NOT NULL, SUM(qry_reports_2.NumPeopleGo))  

                FROM qry_reports AS qry_reports_2

                WHERE qry_reports_2.rep_Id = qry_reports.rep_Id 

                AND qry_reports_2.Dept <> qry_reports.Dept

                GROUP BY qry_reports_2.rep_Id) AS SUB_TOTAL, 

        CInt(NumPeopleGo*NumPeopleGo)+Iff(Len(SUB_TOTAL)>0,SUB_TOTAL,0) AS NoRequired

FROM qry_reports 

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id;






In SQL Server 2000:

SELECT DISTINCT qry_reports.rep_Id, 

        qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate, 

        qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals, 

        (SELECT Case(SUM(qry_reports_2.NumPeopleGo) IS NOT NULL Then SUM(qry_reports_2.NumPeopleGo))  

                FROM qry_reports AS qry_reports_2

                WHERE qry_reports_2.rep_Id = qry_reports.rep_Id 

                AND qry_reports_2.Dept <> qry_reports.Dept

                GROUP BY qry_reports_2.rep_Id) AS SUB_TOTAL, 

        CInt(NumPeopleGo*NumPeopleGo)+Case(Len(SUB_TOTAL)>0 Then SUB_TOTAL Else 0) AS NoRequired

FROM qry_reports 

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id;

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/17/2010 :  10:33:29  Show Profile  Reply with Quote
Iff(SUM(qry_reports_2.NumPeopleGo), CInt(NumPeopleGo*NumPeopleGo) etc are not valid syntax in sql server. use case when and cast instead of iif and cint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  08:08:44  Show Profile  Reply with Quote
Here are what I changed:
SELECT DISTINCT qry_reports.rep_Id,

qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate,

qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals,

(SELECT Case(SUM(qry_reports_2.NumPeopleGo) IS NOT NULL Then SUM(qry_reports_2.NumPeopleGo))

FROM qry_reports AS qry_reports_2

WHERE qry_reports_2.rep_Id = qry_reports.rep_Id

AND qry_reports_2.Dept <> qry_reports.Dept

GROUP BY qry_reports_2.rep_Id) AS SUB_TOTAL,

CAST((NumPeopleGo*NumPeopleGo) as Integer) +Case(Len(SUB_TOTAL)>0 Then SUB_TOTAL Else 0) AS NoRequired

FROM qry_reports

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id;

---- I got incorrect syntax error near the keyword 'FROM'
this line: FROM qry_reports AS qry_reports_2

thanks.
Edited by - prettyjenny on 02/18/2010 08:09:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  08:49:36  Show Profile  Reply with Quote
Case(SUM(qry_reports_2.NumPeopleGo) IS NOT NULL Then SUM(qry_reports_2.NumPeopleGo))
should be

Case WHEN SUM(qry_reports_2.NumPeopleGo) IS NOT NULL Then SUM(qry_reports_2.NumPeopleGo)
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 02/18/2010 08:50:16
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  09:07:50  Show Profile  Reply with Quote
I fixed as you suggested but
I still got incorrect syntax error near the keyword 'FROM'
this line: FROM qry_reports AS qry_reports_2

Can I use the table alias in the FROm clause like above?

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  09:34:28  Show Profile  Reply with Quote 
SELECT DISTINCT qry_reports.rep_Id, 

qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate, 

qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals, 

COALESCE((SELECT SUM(qry_reports_2.NumPeopleGo) 

FROM qry_reports AS qry_reports_2

WHERE qry_reports_2.rep_Id = qry_reports.rep_Id 

AND qry_reports_2.Dept <> qry_reports.Dept

GROUP BY qry_reports_2.rep_Id),0) AS SUB_TOTAL, 

CAST((NumPeopleGo*NumPeopleGo) as Integer) +
COALESCE(SUB_TOTAL,0) AS NoRequired

FROM qry_reports 

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  10:06:53  Show Profile  Reply with Quote
I got different errors:
Invalud column name 'SUB_TOTAL'
and
Ambiguous column name ' NumPeopleGo'

thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  10:17:46  Show Profile  Reply with Quote
quote:
Originally posted by prettyjenny

I got different errors:
Invalud column name 'SUB_TOTAL'
and
Ambiguous column name ' NumPeopleGo'

thanks.

you've NumPeopleGo present in more than 1 table so use table alias to distinguish them.

Also SUB_TOTAL is created on same select so you cant use it directly inside coalesce. you need to either repeat whole subquery or do as follows

SELECT all other columns,
CAST((NumPeopleGo*NumPeopleGo) as Integer) +
COALESCE(SUB_TOTAL,0) AS NoRequired
FROM
(
SELECT DISTINCT qry_reports.rep_Id, 

qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate, 

qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals, 

COALESCE((SELECT SUM(qry_reports_2.NumPeopleGo) 

FROM qry_reports AS qry_reports_2

WHERE qry_reports_2.rep_Id = qry_reports.rep_Id 

AND qry_reports_2.Dept <> qry_reports.Dept

GROUP BY qry_reports_2.rep_Id),0) AS SUB_TOTAL 



FROM qry_reports 

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id;

)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  11:14:17  Show Profile  Reply with Quote
I got Incorrect syntax near ')'
the last line. I removed the 't' on the last line and the semicolon, but still shows that error.

thanks.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 02/18/2010 :  11:18:59  Show Profile  Reply with Quote
You need the 't'..its an alias...Just delete the ';' and try the query.
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  12:15:40  Show Profile  Reply with Quote
I don't see any 't' that applies to this alias so ...

Anyway, I added the 't' in and got this:
The column prefix 'qry_reports' does not match switha table name or alias name used in the query.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  12:24:39  Show Profile  Reply with Quote
quote:
Originally posted by prettyjenny

I don't see any 't' that applies to this alias so ...

Anyway, I added the 't' in and got this:
The column prefix 'qry_reports' does not match switha table name or alias name used in the query.

Thanks.


SELECT rep_Id,post,Dept,SDate,EDate,NumPeopleGo,NoEvals,
CAST((NumPeopleGo*NumPeopleGo) as Integer) +
COALESCE(SUB_TOTAL,0) AS NoRequired
FROM
(
SELECT DISTINCT qry_reports.rep_Id, 

qry_reports.post, qry_reports.Dept, qry_reports.SDate, qry_reports.EDate, 

qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals, 

COALESCE((SELECT SUM(qry_reports_2.NumPeopleGo) 

FROM qry_reports AS qry_reports_2

WHERE qry_reports_2.rep_Id = qry_reports.rep_Id 

AND qry_reports_2.Dept <> qry_reports.Dept

GROUP BY qry_reports_2.rep_Id),0) AS SUB_TOTAL 



FROM qry_reports 

LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id

)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 02/18/2010 :  12:32:19  Show Profile  Reply with Quote
"I don't see any 't' that applies to this alias so ..."

That's correct, "T" is not explicitly referenced, but you do have to have an alias name when you do:

SELECT Col1, Col2, ...
FROM
(
    SELECT ...
    FROM ...
) AS X

"The column prefix 'qry_reports' does not match switha table name or alias name used in the query."

Have you got "qry_reports" as a prefix for any columns in the main select list - that has "SELECT all other columns," in the example?

if not suggest you post your exact query so we can see what you are actually using.
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  12:52:51  Show Profile  Reply with Quote
qry_reports is the name of the query.

I am using this one to calculate from that query.

thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  12:59:07  Show Profile  Reply with Quote
quote:
Originally posted by prettyjenny

qry_reports is the name of the query.

I am using this one to calculate from that query.

thanks.

Did you see my last posted query? thats what Kristen meant

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  13:22:47  Show Profile  Reply with Quote
Yes. I read it.

If I removed the alias for all columns in the main select list, then I got the same errors as previous one.

Invalud column name 'SUB_TOTAL'
and
Ambiguous column name ' NumPeopleGo'

thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/18/2010 :  13:24:16  Show Profile  Reply with Quote
quote:
Originally posted by prettyjenny

Yes. I read it.

If I removed the alias for all columns in the main select list, then I got the same errors as previous one.

Invalud column name 'SUB_TOTAL'
and
Ambiguous column name ' NumPeopleGo'

thanks.

Did you mean you get above errors when you run my last posted query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 02/18/2010 :  13:29:50  Show Profile  Reply with Quote
quote:
Originally posted by prettyjenny

Yes. I read it.

If I removed the alias for all columns in the main select list, then I got the same errors as previous one.

Invalud column name 'SUB_TOTAL'
and
Ambiguous column name ' NumPeopleGo'

thanks.

Can you make this a li'l easy for us by posting the exact query you used.
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  13:42:41  Show Profile  Reply with Quote
Here is the whole script.

thanks.

SELECT rep_Id,
post, Dept, sDate, eDate,
NumPeopleGo, NoEvals,
CAST((NumPeopleGo*NumPeopleGo) AS Integer)+
COALESCE(SUB_TOTAL, 0) AS NoRequired
FROM
(
SELECT DISTINCT qry_reports.rep_Id,
qry_reports.post, qry_reports.Dept, qry_reports.sDate, qry_reports.eDate,
qry_reports.NumPeopleGo AS NumPeopleGo, qry_reports.NoEvals,
COALESCE((SELECT SUM(qry_reports_2.NumPeopleGo)
FROM qry_reports AS qry_reports_2
WHERE qry_reports_2.rep_Id = qry_reports.rep_Id
AND qry_reports_2.Dept <> qry_reports.Dept
GROUP BY qry_reports_2.rep_Id),0) AS SUB_TOTAL,
CAST((NumPeopleGo*NumPeopleGo) AS Integer)+
COALESCE(SUB_TOTAL, 0) AS NoRequired
FROM qry_reports
LEFT JOIN qry_reports AS qry_reports_1 ON qry_reports.rep_Id=qry_reports_1.rep_Id
)t
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 02/18/2010 :  14:02:41  Show Profile  Reply with Quote
Try this..
SELECT t.rep_Id, 
       t.post, 
       t.Dept, 
       t.sDate, 
       t.eDate, 
       t.NumPeopleGo, 
       t.NoEvals, 
       CAST((t.NumPeopleGo * t.NumPeopleGo) AS INTEGER) + COALESCE(t.SUB_TOTAL,0) AS NoRequired 
FROM   (SELECT DISTINCT qry_reports.rep_Id, 
                        qry_reports.post, 
                        qry_reports.Dept, 
                        qry_reports.sDate, 
                        qry_reports.eDate, 
                        qry_reports.NumPeopleGo                                              AS NumPeopleGo, 
                        qry_reports.NoEvals, 
                        COALESCE((SELECT   SUM(qry_reports_2.NumPeopleGo) 
                                  FROM     qry_reports AS qry_reports_2 
                                  WHERE    qry_reports_2.rep_Id = qry_reports.rep_Id 
                                           AND qry_reports_2.Dept <> qry_reports.Dept 
                                  GROUP BY qry_reports_2.rep_Id),0) AS SUB_TOTAL
        FROM   qry_reports 
               LEFT JOIN qry_reports AS qry_reports_1 
                 ON qry_reports.rep_Id = qry_reports_1.rep_Id) t
Go to Top of Page

prettyjenny
Yak Posting Veteran

USA
57 Posts
Posted - 02/18/2010 :  14:11:38  Show Profile  Reply with Quote
It worked perfectly.

Big thanks.

Have a great day!

(Spam Removed)
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000