Link to home
Start Free TrialLog in
Avatar of PtboGiser
PtboGiserFlag for Canada

asked on

TSQL Question - View

 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
Avatar of PtboGiser
PtboGiser
Flag of Canada image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).
Do i require the is ISNULL part anymore?
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
The "CASE WHEN Suffix_Cntry IS NOT NULL THEN" will add Suffix_Full ONLY when the condition is true.

What is "Suffix_Bell"?
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
Anyone have a chance to work this one? I'm stuck
Thx