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

x
?
Solved

Select Query

Posted on 2013-02-06
5
Medium Priority
?
299 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 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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
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
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…

722 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