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
Solved

TSQL Question - View

Posted on 2013-01-29
9
264 Views
Last Modified: 2013-02-01
 Select
 LTRIM(RTRIM(UPPER(ISNULL(dbo.Street.Prefix_Full, ''))) 
+  ' ' +     RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, ''))) 
+ ' ' +      RTRIM(UPPER(ISNULL(dbo.Street.Suffix_Cnty, '')))) AS Name
	FROM dbo.Segment INNER JOIN    dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID

Open in new window



Hey guys
I'm close, I'm using the following code to pull out column and create street names. I am struggling with the part where i need to select the [Suffix_Full] column when [Suffix_Cnty] is not null.
Can someone help?
Thanks
0
Comment
Question by:PtboGiser
  • 6
  • 2
9 Comments
 

Author Comment

by:PtboGiser
ID: 38832641
The Suffix_Full Column would read (Alley) instead of the Suffix_Cnty column would have a value of Al. But only Suffix_Cnty is not null.
Thanks
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 38833130
Not sure I fully understand what your q is.

Maybe something like this?:


SELECT

 LTRIM(RTRIM(UPPER(ISNULL(str.Prefix_Full, '')))
+       RTRIM(UPPER(ISNULL(' ' + str.Street_Name, '')))
+       RTRIM(UPPER(COALESCE(' ' + Suffix_Full, ' ' + str.Suffix_Cnty, '')))) AS Name

FROM dbo.Segment seg
INNER JOIN dbo.Street str ON
    seg.Street_ID = str.Street_ID
0
 
LVL 10

Assisted Solution

by:deviprasadg
deviprasadg earned 300 total points
ID: 38834306
Try This:
 Select
 LTRIM(RTRIM(UPPER(ISNULL(CASE WHEN dbo.Street.Suffix_Cnty IS NOT NULL THEN Suffix_Full 
                                 ELSE dbo.Street.Prefix_Full
                          END, ''))) 
+  ' ' +     RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, ''))) 
+ ' ' +      RTRIM(UPPER(ISNULL(dbo.Street.Suffix_Cnty, '')))) AS Name
	FROM dbo.Segment INNER JOIN    dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:PtboGiser
ID: 38835294
Select
 LTRIM(RTRIM(UPPER(ISNULL(dbo.Street.Prefix_Full, '')))
+  ' ' +     RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, '')))
+ ' ' +      RTRIM(UPPER(CASE WHEN dbo.Street.Suffix_Cnty IS NOT NULL THEN Suffix_Full
                                 ELSE dbo.Street.Suffix_Bell
                          END, ''))) AS Name
      FROM dbo.Segment INNER JOIN dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
  Order By Name

Error message
Msg 174, Level 15, State 1, Line 4
The upper function requires 1 argument(s).
0
 

Author Comment

by:PtboGiser
ID: 38835317
Do i require the is ISNULL part anymore?
0
 

Author Comment

by:PtboGiser
ID: 38835328
Select
 LTRIM(RTRIM(UPPER(ISNULL(dbo.Street.Prefix_Full, ''))) 
+  ' ' +     RTRIM(UPPER(ISNULL(dbo.Street.Street_Name, ''))) 
+ ' ' +      RTRIM(UPPER((COALESCE(CASE WHEN dbo.Street.Suffix_Cnty IS NOT NULL THEN Suffix_Full 
                                 ELSE dbo.Street.Suffix_Bell
                          END, ''))))) AS Name
	FROM dbo.Segment INNER JOIN dbo.Street ON dbo.Segment.Street_ID = dbo.Street.Street_ID
  Order By Name

Open in new window


This is giviing me a the Suffix_Full column for all my records. I only want it where Suffix_Cnty IS NOT NULL.
Thanks for the help
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38835557
The "CASE WHEN Suffix_Cntry IS NOT NULL THEN" will add Suffix_Full ONLY when the condition is true.

What is "Suffix_Bell"?
0
 

Author Comment

by:PtboGiser
ID: 38835579
Hey Scott
Suffix_Bell is the abbrviation of Suffix_Full.  
Suffix_Cnty is also the abbrviation of Suffix_Full.
When Suffix_Bell is populated (Rd(in Suffix_Bell) = Road(in Suffix_Full) Suffix_Cnty will be Null. and Vise Versa.

Suffix_Bell & Suffix_Cnty connot both be populated, its one or the other or niether.

The wierd part of this request is.

When Suffix_Cnty is not null i need to populate the Name field with
Prefix_Full + Street_Name + Suffix_Full (When Suffix_Cnty IS NOT NULL)

When Suffix_Bell is not null i need to populate the Name field with only
Prefix_Full + Street_Name. The Suffix_Bell Column gets put in the Type Field.
Which reading back i think i explained this part wrong to start. Sorry about that
0
 

Author Comment

by:PtboGiser
ID: 38839704
Anyone have a chance to work this one? I'm stuck
Thx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query question 12 32
SQL Server 2012 Database Restore 4 38
Negative isnull? 3 14
Location of Dynamics AX Service accounts in SQL 3 15
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.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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 information from SQL Server on Database, Connection and Server properties

791 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