Solved

SQL - View Error

Posted on 2013-02-05
4
277 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 SP4 29 33
Database Containment - Benefits 6 25
Azure SQL DB? 3 16
sql query Help 12 29
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now