SQL Query against select value that is nvarchar and needs to be converted to int

Queries and Resources
1

Trying to limit sql query based upon selected values. The issue is that values are strings in an array.

One selection works fine but more than one gives error
Conversion failed when converting the nvarchar value '0,1' to data type int.

select Photo.PhotoId, PropertyId, Tags, IsPrimaryThumbnail, CreatedDate, UpdatedDate, ImageBytes, Notes, FileName, Url, ThumbnailImageUrl, DisplayImageUrl, OriginalImageUrl, Latitude, Longitude, TimeStamp, DistanceInMeters, IsActive, ArchivedBy, ArchivedDate, CRC, COALESCE(Tag.Name, 'Blank') as Tag, ('https://rrsav2photos.blob.core.windows.net/prod/Properties' + OriginalImageUrl) As 'FullUrl', ISNULL(CONVERT(INT, COALESCE(Tag.TagId, 0)),0)
From Photo
Left Join PhotoTag on Photo.PhotoId = PhotoTag.PhotoId
Left Join Tag on PhotoTag.TagId = Tag.TagId
Where PropertyId = {{tableProperties.selectedRow.data.PropertyId}}
AND ISNULL(CONVERT(INT, COALESCE(Tag.TagId, 0)),0) IN ({{multiselectPhotoTags.value}})

2

Hey @nroeder!

Have you already checked out the SQL Cheatsheet? Dealing with arrays in Retool largely depends on the type of SQL resource you're using. If you don't find the answer there could you let us know what that is?

3

I read that but I get this error Conversion failed when converting the nvarchar value '0,1,2,' to data type int.

the only way I can get it to work is by selecting each value manually.
AND (ISNULL(CONVERT(INT, COALESCE(Tag.TagId, 0)),0) IN ({{multiselectPhotoTags.value['0']}},{{multiselectPhotoTags.value['1']}}, {{multiselectPhotoTags.value['2']}}, {{multiselectPhotoTags.value['3']}}, {{multiselectPhotoTags.value['4']}}, {{multiselectPhotoTags.value['5']}}, {{multiselectPhotoTags.value['6']}}, {{multiselectPhotoTags.value['7']}}, {{multiselectPhotoTags.value['6']}}, {{multiselectPhotoTags.value['13']}}, {{multiselectPhotoTags.value['14']}}, {{multiselectPhotoTags.value['15']}} ))

4

so i think the problem is actually on the multiselect side. On the sql side I have to use .

(ISNULL(CONVERT(INT, COALESCE(Tag.TagId, 0)),0)

because undefined comes back as a value.

I need a similar query for JS side.

5

I see, JavaScript has it's own nullish coalescing operator - ??. You might try mapping over your array with that or the logical OR operator - || in order to have your undefined values default to 0. Something like {{ multiselectPhotoTags.value.map(value => value || 0) }}. Does that work?