Link to home
Start Free TrialLog in
Avatar of PtboGiser
PtboGiserFlag for Canada

asked on

Unable To Parse Query

I get the results i'm looking for but still recive this error before it completes
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

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

in the GUI / query designer?
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]
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
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PtboGiser

ASKER

Does not like the word Level
Thanks again
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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  :)
thanks i will be sure to continue to use the joins that way,