→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Matt8
Engaged Sweeper III
Simple query, unexpected result. I am using the second Custom2 field to save Cost, since Lansweeper is about the only inventory system in the world without a field for it

Trying to see something over the cost of $500

SELECT a.AssetName, at.AssetTypename, ac.PurchaseDate, ac.Department, ac.Custom2 FROM tblAssets a
INNER JOIN tblAssetCustom ac ON a.AssetID = ac.AssetID
INNER JOIN tsysAssetTypes at ON a.Assettype = at.AssetType
WHERE ac.Custom2 > '499'


And the result set I get is wrong. I get items well under the amount.

The column appears to be formatted as nvarchar which would explain why this doesn't work.

If I were to change this column type to numeric, am I going to blow up Lansweeper?
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
You will need to convert the values in your query, as suggested by RC62N as well. Custom fields always remain text fields within the Lansweeper database, regardless of what you set their display type to. Changing the field type directly in the database is not supported and may very well break certain parts of your installation.

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
You will need to convert the values in your query, as suggested by RC62N as well. Custom fields always remain text fields within the Lansweeper database, regardless of what you set their display type to. Changing the field type directly in the database is not supported and may very well break certain parts of your installation.
Matt8
Engaged Sweeper III
That would absolutely work, but will have to do that on any query that uses the custom fields. Seems like when you change the type of data in the column, Lansweeper should be formatting that column as such instead of leaving as nvarchar
RCorbeil
Honored Sweeper II
Rather than risk breaking your LANSweeper, could you not Convert() your custom value to an Int when evaluating it?