Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Syntax errors on my complex query - please help

Author  Topic 

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-02-17 : 10:30:39
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

52326 Posts
Posted - 2010-02-17 : 10:33:29
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

57 Posts
Posted - 2010-02-18 : 08:08:44
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 08:49:36
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/
Go to Top of Page

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-02-18 : 09:07:50
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

52326 Posts
Posted - 2010-02-18 : 09:34:28
[code]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;
[/code]

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

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-02-18 : 10:06:53
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

52326 Posts
Posted - 2010-02-18 : 10:17:46
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

57 Posts
Posted - 2010-02-18 : 11:14:17
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
Master Smack Fu Yak Hacker

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

prettyjenny
Yak Posting Veteran

57 Posts
Posted - 2010-02-18 : 12:15:40
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

52326 Posts
Posted - 2010-02-18 : 12:24:39
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

22859 Posts
Posted - 2010-02-18 : 12:32:19
"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

57 Posts
Posted - 2010-02-18 : 12:52:51
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

52326 Posts
Posted - 2010-02-18 : 12:59:07
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

57 Posts
Posted - 2010-02-18 : 13:22:47
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

52326 Posts
Posted - 2010-02-18 : 13:24:16
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
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-02-18 : 13:29:50
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

57 Posts
Posted - 2010-02-18 : 13:42:41
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
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-02-18 : 14:02:41
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

57 Posts
Posted - 2010-02-18 : 14:11:38
It worked perfectly.

Big thanks.

Have a great day!

(Spam Removed)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site