Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select Query

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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…
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

885 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