Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL - View Error

Posted on 2013-02-05
4
Medium Priority
?
290 Views
Last Modified: 2013-02-05
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, 
                      dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID

Open in new window


Getting the Error
Msg 156, Level 15, State 1, Line 19 - Bottom Join
Incorrect syntax near the keyword 'ON'.
What am i missing
0
Comment
Question by:PtboGiser
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 total points
ID: 38856493
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

Inner Join before dbo.street you had a comma so the on wasn't allowed ...(the condition would go in a where clause)
0
 

Author Closing Comment

by:PtboGiser
ID: 38856509
thx, sometimes syntax is so frustrating!!!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856543
wouldn't this be simpler although i think the ward join is then incorrect
since you end up with the same value for both the left and right name parts...

SELECT     seg.ROADJUR, seg.L_F_ADD, seg.L_T_ADD, seg.R_F_ADD, seg.R_T_ADD, 
                      seg.Geometry,
                      LTRIM(RTRIM(UPPER(ISNULL(st.Prefix_Full, '')))) + ' ' + 
                          RTRIM(UPPER(ISNULL(st.Street_Name, '')))   + ' ' + 
                         RTRIM (UPPER(ISNULL(st.Suffix_Cnty, ''))) + '' +
	  RTRIM (UPPER(ISNULL(st.Suffix_Bell, ''))) + ' ' +
	  RTRIM (UPPER(ISNULL(st.Suffix_Direction, ''))) AS Name,						  
                       w.Ward_Name
                            AS L_MUNAME,
                       w.Ward_Name
                             AS R_MUNAME, 
	CASE seg.ROADJUR 
                               WHEN 'KINGS' THEN 9 
                                WHEN  'CNTY' THEN 15
                                WHEN  'TWP' THEN 16 
                                 WHEN  'PRIV' THEN 17 
                                END AS LEVEL
               FROM dbo.Segment as seg 
              INNER JOIN   dbo.Ward as w 
                               ON Seg.Left_Mun_ID = w.Ward_ID
                            AND Seg.Right_Mun_ID = W.Ward_ID
                   inner join dbo.Street  as 
                              ON seg.Street_ID = st.Street_ID

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38856572
Top one is working well from the results i see. I'm sure there may be another way of writing it. I'm hoping mine is good from a overall execution standpoint. Results are good.
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question