Works in the QueryDesigner but not in real life

24 Dec 2005 2:30 AM

Daniel Manes

I've been working on a fairly complex query and testing it in the
QueryDesigner in Visual Studio. I was going under the premise that,
"Gee, if it works in the QueryDesigner, it should work when I run it on
my asp.net page." Stupid me.

When I run the query below, I get:
Incorrect syntax near the keyword 'WHERE'

And I have no idea why.

It would be nice if they told me *which* WHERE, but I can tell you that
the embedded query works (i.e., it works if you get rid of the first
two lines, the last four lines, and the outer parenthases). With the
whole query, however, it only runs in the Query Designer.

What am I doing wrong?

Thanks in advance,

-Dan

---------------
        SelectCommand=
                    "SELECT HypothesisID, COUNT(*) AS iPostCount
                    FROM
                        (SELECT iPostTable.HypothesisID,
iPostTable.TimeStamp AS iPostTimeStamp, LatestVisit.TimeStamp AS
VisitTimeStamp
                        FROM
                            (SELECT iPost.ParticipantID,
iPost.HypothesisID, iPost.TimeStamp, PermissionTable.PermissionFlag
                            FROM iPost
                            LEFT OUTER JOIN
                                (SELECT HypothesisID, PermissionFlag
                                FROM Permission
                                WHERE ParticipantID = @ParticipantID)
                                AS PermissionTable
                            ON iPost.HypothesisID =
PermissionTable.HypothesisID
                            WHERE
                                iPost.IncludeFlag = 1
                                AND
                                    (iPost.ParticipantID =
@ParticipantID
                                    OR iPost.HypothesisID =
@OwnedHypothesisID))
                            AS iPostTable
                        LEFT OUTER JOIN
                            (SELECT HypothesisID, MAX(TimeStamp) AS
TimeStamp
                            FROM Visit
                            WHERE ParticipantID = @ParticipantID
                            GROUP BY HypothesisID)
                            AS LatestVisit
                        ON iPostTable.HypothesisID =
LatestVisit.HypothesisID)
                    WHERE
                        VisitTimeStamp IS NULL
                        OR iPostTimeStamp >= VisitTimeStamp
                    GROUP BY HypothesisID"
---------------

24 Dec 2005 2:40 AM

Greg Linwood

You had an un-named derived table just before the final WHERE clause.

Try:

        SelectCommand=
                    "SELECT HypothesisID, COUNT(*) AS iPostCount
                    FROM
                        (SELECT iPostTable.HypothesisID,
iPostTable.TimeStamp AS iPostTimeStamp, LatestVisit.TimeStamp AS
VisitTimeStamp
                        FROM
                            (SELECT iPost.ParticipantID,
iPost.HypothesisID, iPost.TimeStamp, PermissionTable.PermissionFlag
                            FROM iPost
                            LEFT OUTER JOIN
                                (SELECT HypothesisID, PermissionFlag
                                FROM Permission
                                WHERE ParticipantID = @ParticipantID)
                                AS PermissionTable
                            ON iPost.HypothesisID =
PermissionTable.HypothesisID
                            WHERE
                                iPost.IncludeFlag = 1
                                AND
                                    (iPost.ParticipantID =
@ParticipantID
                                    OR iPost.HypothesisID =
@OwnedHypothesisID))
                            AS iPostTable
                        LEFT OUTER JOIN
                            (SELECT HypothesisID, MAX(TimeStamp) AS
TimeStamp
                            FROM Visit
                            WHERE ParticipantID = @ParticipantID
                            GROUP BY HypothesisID)
                            AS LatestVisit
                        ON iPostTable.HypothesisID =
LatestVisit.HypothesisID) as dv
                    WHERE
                        VisitTimeStamp IS NULL
                        OR iPostTimeStamp >= VisitTimeStamp
                    GROUP BY HypothesisID"

Regards,
Greg Linwood
SQL Server MVP
Show quote

"Daniel Manes" <danth***@cox.net> wrote in message
news:1135391440.934008.84960@g14g2000cwa.googlegroups.com...
> I've been working on a fairly complex query and testing it in the
> QueryDesigner in Visual Studio. I was going under the premise that,
> "Gee, if it works in the QueryDesigner, it should work when I run it on
> my asp.net page." Stupid me.
>
> When I run the query below, I get:
> Incorrect syntax near the keyword 'WHERE'
>
> And I have no idea why.
>
> It would be nice if they told me *which* WHERE, but I can tell you that
> the embedded query works (i.e., it works if you get rid of the first
> two lines, the last four lines, and the outer parenthases). With the
> whole query, however, it only runs in the Query Designer.
>
> What am I doing wrong?
>
> Thanks in advance,
>
> -Dan
>
> ---------------
>        SelectCommand=
>                    "SELECT HypothesisID, COUNT(*) AS iPostCount
>                    FROM
>                        (SELECT iPostTable.HypothesisID,
> iPostTable.TimeStamp AS iPostTimeStamp, LatestVisit.TimeStamp AS
> VisitTimeStamp
>                        FROM
>                            (SELECT iPost.ParticipantID,
> iPost.HypothesisID, iPost.TimeStamp, PermissionTable.PermissionFlag
>                            FROM iPost
>                            LEFT OUTER JOIN
>                                (SELECT HypothesisID, PermissionFlag
>                                FROM Permission
>                                WHERE ParticipantID = @ParticipantID)
>                                AS PermissionTable
>                            ON iPost.HypothesisID =
> PermissionTable.HypothesisID
>                            WHERE
>                                iPost.IncludeFlag = 1
>                                AND
>                                    (iPost.ParticipantID =
> @ParticipantID
>                                    OR iPost.HypothesisID =
> @OwnedHypothesisID))
>                            AS iPostTable
>                        LEFT OUTER JOIN
>                            (SELECT HypothesisID, MAX(TimeStamp) AS
> TimeStamp
>                            FROM Visit
>                            WHERE ParticipantID = @ParticipantID
>                            GROUP BY HypothesisID)
>                            AS LatestVisit
>                        ON iPostTable.HypothesisID =
> LatestVisit.HypothesisID)
>                    WHERE
>                        VisitTimeStamp IS NULL
>                        OR iPostTimeStamp >= VisitTimeStamp
>                    GROUP BY HypothesisID"
> ---------------
>

24 Dec 2005 2:38 PM

Erland Sommarskog

Daniel Manes (danth***@cox.net) writes:
Show quote

> I've been working on a fairly complex query and testing it in the
> QueryDesigner in Visual Studio. I was going under the premise that,
> "Gee, if it works in the QueryDesigner, it should work when I run it on
> my asp.net page." Stupid me.
>
> When I run the query below, I get:
> Incorrect syntax near the keyword 'WHERE'
>
> And I have no idea why.
>
> It would be nice if they told me *which* WHERE, but I can tell you that
> the embedded query works (i.e., it works if you get rid of the first
> two lines, the last four lines, and the outer parenthases). With the
> whole query, however, it only runs in the Query Designer.
>
> What am I doing wrong?

Using the Query Designer is your first mistake. There are an incredible
number of issues with it. The more complex the SQL gets, the more it
distorts the queries.

As for the lack of line numbers, you can always run the query from a
query window in Query Analyzer or Management Studio, and you will get
a correct line number. (For this type of error, I should hasten to add.
If you have misspelled a column name, SQL Server will only point you
to where the line where the SQL statement starts.

The error itself, is as Greg pointed out:

>         SelectCommand=
>                     "SELECT HypothesisID, COUNT(*) AS iPostCount
>                     FROM
>                         (SELECT iPostTable.HypothesisID,
>...
>                         ON iPostTable.HypothesisID =
> LatestVisit.HypothesisID)
>                     WHERE

You have a derived table, and it must have an alias. Just add "AS x"
after the parenthesis.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

24 Dec 2005 8:47 PM

Daniel Manes

Greg, Erland,

Sure enough, that was it. Thanks! I was creating aliases for derived
tables only if I needed some way to disambiguate column names--didn't
realize you had to do this no matter what.

As for the Erland's advice about Query Designer, yeah, I've definitely
learned my lesson about that. It seems to be more lenient about some
things (like not requiring that you alias derived tables), but, then it
will totally choke on other things (e.g., I tried doing a SELECT...INTO
statement to create a temporary table prior to a main select and it
kept saying "Unable to parse query text").

Using the query window in Microsoft SQL Server Management Studio
Express (which is one of the longest names for an application I've ever
seen) seems to work much better. First of all, it doesn't completely
try to reformat my queries (much less into the most unreadable format
possible), and things like multiple statements, SELECT...INTO, and
local variable declarations work fine.

And, most importantly, when I paste things into my ASP.NET page, they
actually work (except if they contain a "<" or ">" which have to be
converted to < and > to avoid confusing the html parser).

Thanks again,

-Dan