Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - View Error

Posted on 2013-02-05
4
Medium Priority
?
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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