Coalesce Function Used on Derived Field

Im using SQL Server 2005 with Reporting Services;
The problem is that the report will display a blank block of test if the proper conditions are not met-
The problem fields are address1 and address2-and the concatenation of each;
I dont think Im building the string correctly.
Both address1 and address2 are not required so they could be blank
Address1 is a field directly from the database;
Address2 is a concatenation of the database fields: City, State and Zip;
And I wanted a new field, addressb, to be the concatenation of address1 and address2.
Attached below is the section of the sql query in question;
I need this data for the report.
And one of these fields must be returning a blank but I can pin down which one...
SELECT 
coalesce(b.address1,'') + ' ' + coalesce(b.city,'') + ' ' + 
coalesce(b.state,'') + ' ' + coalesce(b.zip,'') AS addressb, 
ISNULL(RTRIM(b.Address1),'NA') AS address1,
ISNULL(RTRIM(b.City),'') + ' ' + ISNULL(RTRIM(b.State),'') + ' ' + ISNULL(RTRIM(b.Zip),'') AS address2
a.[Meeting End] AS [meetingend]  
FROM         
Reservations A 
LEFT JOIN
Client B 
ON B.ClientID = A.[general/clientid] 
LEFT JOIN 
Rooms R  
ON R.Room_id = A.Room_id 
LEFT JOIN 
Locations D 
ON D.Location_ID = R.Location_ID   
LEFT JOIN 
vw_events E 
ON E.EventID = A.[general/eventid]   
WHERE R.[Location_ID] = 6

Open in new window

jtrapat1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chza727Commented:

Maybe you need an ISNULL around all of address2?

ISNULL(
               ISNULL(RTRIM(b.City),'') + ' ' + ISNULL(RTRIM(b.State),'') + ' ' + ISNULL(RTRIM(b.Zip),'')
            ,'NA') AS address2

Without knowing what error you are getting or what data you have... that is my guess.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
so what's the issue, can you paste the sample result
SELECT
coalesce(b.address1,'') + ' ' + ISNULL(RTRIM(b.City),'') + ' ' + ISNULL(RTRIM(b.State),'') + ' ' + ISNULL(RTRIM(b.Zip),'') AS addressb,
ISNULL(RTRIM(b.Address1),'NA') AS address1,
ISNULL(RTRIM(b.City),'') + ' ' + ISNULL(RTRIM(b.State),'') + ' ' + ISNULL(RTRIM(b.Zip),'') AS address2
,a.[Meeting End] AS [meetingend]  
FROM        
Reservations A
LEFT JOIN
Client B
ON B.ClientID = A.[general/clientid]
LEFT JOIN
Rooms R  
ON R.Room_id = A.Room_id
LEFT JOIN
Locations D
ON D.Location_ID = R.Location_ID  
LEFT JOIN
vw_events E
ON E.EventID = A.[general/eventid]  
WHERE R.[Location_ID] = 6

0
jtrapat1Author Commented:
chza727:
thanks for the response-
ill try this; in the meantime, address2 could be NULL or spaces.
image.gif
0
jtrapat1Author Commented:
aneeshattingal:
yes- as for the sample result-
there are blanks in addressb: and sometimes in all three fields:

attached is a sample
thanks


image2.gif
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.