Solved

Select Query

Posted on 2013-02-06
5
288 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 65

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 65

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now