asked on

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

ASKER CERTIFIED SOLUTION

Ares Kurklu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

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

SOLUTION

csePixelated

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ares Kurklu

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.

csePixelated

ASKER

yes it is, im still on good old MS SQL server 2008 r2, once again thanks