Solved

SQL - View Error

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CAST issue with SQL 2000 15 44
mssql 7 32
SQL Job Failed 6 31
*** Windows Server 2012 Websites Set Up *** 17 32
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

751 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