PtboGiser
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Select
LTRIM(RTRIM(UPPER(ISNULL(d bo.Street. Prefix_Ful l, '')))
+ ' ' + RTRIM(UPPER(ISNULL(dbo.Str eet.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).
LTRIM(RTRIM(UPPER(ISNULL(d
+ ' ' + RTRIM(UPPER(ISNULL(dbo.Str
+ ' ' + 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).
ASKER
Do i require the is ISNULL part anymore?
ASKER
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
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"?
What is "Suffix_Bell"?
ASKER
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
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
ASKER
Anyone have a chance to work this one? I'm stuck
Thx
Thx
ASKER
Thanks