Solved

Select Query

Posted on 2013-02-06
5
297 Views
Last Modified: 2013-02-06
I'm playing with this and cannot seem to get the proper results. I am looking to only select the main 4 "RoadJur" values withing that column. Currently i am selecting all of them. Whats the best way in the select statement at the top to NOT  select the "WAO" Value? which leaves me with the "KINGS", "TWP", "CNTY","PRIV" values.


SELECT     TOP (100) PERCENT 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 Expr1) AS Name, w1.Ward_Name AS L_MUNAME, w2.Ward_Name 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 LEFT OUTER JOIN
                      dbo.Ward AS w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID LEFT OUTER JOIN
                      dbo.Ward AS w2 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID INNER JOIN
                      dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID

Open in new window

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
5 Comments
 

Author Comment

by:PtboGiser
ID: 38859714
I'm doing this and its wrong

SELECT     TOP (100) PERCENT
                          (SELECT     ROADJUR
                            FROM          dbo.Segment
                            WHERE      (ROADJUR <> 'WAO')) AS Expr1,
Segment_1.L_F_ADD, Segment_1.L_T_ADD, Segment_1.R_F_ADD, Segment_1.R_T_ADD, Segment_1.Geometry,
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 38859732
>Whats the best way in the select statement at the top to NOT  select the "WAO" Value? which leaves me with the "KINGS", "TWP", "CNTY","PRIV" values.
To pull off conditions like this add them to the WHERE clause..

{your query above}
WHERE RoadJur IN ('KINGS', 'TWP', 'CNTY', PRIV')

<or>

{your query above}
WHERE RoadJur <> 'WAO'
0
 

Author Closing Comment

by:PtboGiser
ID: 38859818
Man sometimes i overthink things. Sorry
Thx
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38859827
Thanks for the grade.  Good luck with your project.  -Jim
0
 
LVL 25

Expert Comment

by:jogos
ID: 38859830
I guess that peace of code completed must be this (seeing the query in your first question)
SELECT     TOP (100) PERCENT
                          (SELECT     ROADJUR
                            FROM          dbo.Segment
                            WHERE      (ROADJUR <> 'WAO')) AS Expr1, 
Segment_1.L_F_ADD, Segment_1.L_T_ADD, Segment_1.R_F_ADD, Segment_1.R_T_ADD, Segment_1.Geometry,
from dbo.segment as Segment_1

Open in new window

Then you don't have a condition to join the subselect with the  rest of
the query, predictably giving error 'multiple rows .... where one row expected'.


Why you need a subselect to select that value when the table is already in the query?

 select .......
dbo.Segment 
LEFT OUTER JOIN  dbo.Ward AS w1 ON dbo.Segment.Left_Mun_ID = w1.Ward_ID
 LEFT OUTER JOIN  dbo.Ward AS w2 ON dbo.Segment.Right_Mun_ID = w2.Ward_ID 
INNER JOIN
                      dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
where RaodJur <> 'WAO'

Open in new window

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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