query not working for one column.

why does my query not return data for [Zones.Description]
so i know know why its not working, it seems that the Zones.Number field is always 10 characters so if it is 1 its '         1' and if its 30a its '       30a' unfortunately this does not match the contents its cross referenced with on SignalHistory.AlarmZones. how can i get the query to ignore the spaces?

SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate

Open in new window

LVL 1
csePixelatedAsked:
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.

 
Ares KurkluSoftware EngineerCommented:
You can simply use Trim function on both sides to have consistency.
Trim( SignalHistory.AlarmZones) =Trim( Zones.Number)

SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND TRIM(SignalHistory.AlarmZones) = TRIM (Zones.Number)
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

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
 
csePixelatedAuthor Commented:
when i parse i get, msg 195, level 15, state 10, line 4
'TRIM' is not a recognised function name.
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, Zones.Description, Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND TRIM(SignalHistory.AlarmZones) = TRIM (Zones.Number)
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY AlarmDate

Open in new window

0
 
csePixelatedAuthor Commented:
im using microsoft sql, I substituted it with 'LTRIM' it works now,thanks
0
 
Ares KurkluSoftware EngineerCommented:
Yea sorry should have checked the version, they actually added trim() to ms sql but it is very new (2017), funny how quickly you get used to it.
0
 
csePixelatedAuthor Commented:
yes it is, im still on good old MS SQL server 2008 r2, once again thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.