Solved

SQL - View Error

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

821 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