PtboGiser
asked on
Unable To Parse Query
I get the results i'm looking for but still recive this error before it completes
Why?
Why?
SELECT dbo.Segment.ROADJUR, dbo.Segment.L_F_ADD, dbo.Segment.L_T_ADD, dbo.Segment.R_F_ADD, dbo.Segment.R_T_ADD,
dbo.Segment.Geometry,
(SELECT LTRIM(RTRIM(UPPER(ISNULL(dbo.Street.Prefix_Full, '')))) + ' ' +
(RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, '')))) + ' ' +
(RTRIM (UPPER(ISNULL(dbo.Street.Suffix_Cnty, '')))) + '' +
(RTRIM (UPPER(ISNULL(dbo.Street.Suffix_Bell, '')))) + ' ' +
(RTRIM (UPPER(ISNULL(dbo.Street.Suffix_Direction, ''))))) AS Name,
(SELECT Ward_Name
FROM dbo.Ward AS W
WHERE (Ward_ID = dbo.Segment.Left_Mun_ID)) AS L_MUNAME,
(SELECT Ward_Name
FROM dbo.Ward AS W
WHERE (Ward_ID = dbo.Segment.Right_Mun_ID)) AS R_MUNAME,
CASE WHEN dbo.Segment.ROADJUR = 'KINGS' THEN 9 WHEN dbo.Segment.ROADJUR = 'CNTY' THEN 15
WHEN dbo.Segment.ROADJUR = 'TWP' THEN 16 WHEN dbo.Segment.ROADJUR = 'PRIV' THEN 17 END AS LEVEL
FROM dbo.Segment INNER JOIN
dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID
INNER JOIN dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
you might also start using aliases to make your query easier to read:
https://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
if the above does not help, try to use a normal query window to run the query.
if that works, it's definitively a limitation of the query designer window.
if there it still does not work, comment out parts of the query until it works, and then you can concentrate on the part that is indeed raising the issue
https://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
if the above does not help, try to use a normal query window to run the query.
if that works, it's definitively a limitation of the query designer window.
if there it still does not work, comment out parts of the query until it works, and then you can concentrate on the part that is indeed raising the issue
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Does not like the word Level
Thanks again
Thanks again
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A Wealth of knowledge as usual
Thanks guys
Is there a Huge advantage in using the
LEFT JOIN dbo.Ward w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID
LEFT JOIN dbo.ward w1 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID
Compared to
Inner Join dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID
Thanks
Thanks guys
Is there a Huge advantage in using the
LEFT JOIN dbo.Ward w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID
LEFT JOIN dbo.ward w1 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID
Compared to
Inner Join dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID
Thanks
the 2 joins will make sure it will work, with 1 single join to "ward" you won't get any results UNLESS left_Mun_ID = Right_Mun_ID
thanks for the points :)
this one hence pushes me across the 38M points barrier :)
this one hence pushes me across the 38M points barrier :)
ASKER
thanks i will be sure to continue to use the joins that way,
if yes, this comes, I think, from the subqueries and/or CASE statements.
it might also be reserved keywords, for example "level" or "Geometry" might require to be written like [LEVEL] and [Geometry]