Hi Vikas,

You'd use a LIKE clause in your where, something like {Device}.[Name] LIKE '%' + @DeviceSearchText + '%'

Not knowing the purpose of your conditions in the WHERE, I would say that you should wrap it with parenthesis to isolate it:

WHERE (/* YOUR EXISTING WHERE */)
AND (@DeviceSearchText = '' OR {Device}.[Name] LIKE '%' + @DeviceSearchText + '%')

still at error

Can you show us what you wrote, and what error you're getting?

"simdevicekey" is text data type

The problem is this bit, I removed it and the query executes:

AND {GroupMember}.[DeviceId] IN {GroupMember}.[Id]

Can't figure out what it's for. What are you trying to do with this?

SELECT {Device}.*, {Customer}.*, 0
FROM {Device}
INNER JOIN {Customer} on {Customer}.[Id] = {Device}.[CustomerId]
WHERE {Device}.[Id] NOT IN
(SELECT {GroupMember}.[DeviceId]
FROM {GroupMember}
WHERE {GroupMember}.[DeviceGroupId] = @DeviceGroupId AND {GroupMember}.[IsActive] = 1)
OR (@DeviceSearchText = '' OR {Device}.[SimDeviceKey] LIKE '%' + @DeviceSearchText + '%')

the query I posted in beginning is the working correcting. it's for fetching devices which are not in "groupmembers " and displayed in a table record.

now I just want to add a search button on the table. so in aggregate, I can't directly filter but here don't know where to put or I have to edit the whole query

device     group     groupmember

id             id            id  

name      name       deviceid

                               groupid     

It executes for me only if I turn "IN {GroupMember}.[DeviceId]" into "IN ({GroupMember}.[DeviceId])".

Try this and see if it returns what you're expecting:

SELECT {Device}.*, {Customer}.*, 0
FROM {Device}
INNER JOIN {Customer} on {Customer}.[Id] = {Device}.[CustomerId]
WHERE {Device}.[Id] NOT IN
(SELECT {GroupMember}.[DeviceId]
FROM {GroupMember}
WHERE {GroupMember}.[DeviceGroupId] = @DeviceGroupId AND {GroupMember}.[DeviceId] IN ({GroupMember}.[DeviceId]) AND {GroupMember}.[IsActive] = 1)
OR (@DeviceSearchText = '' OR {Device}.[SimDeviceKey] LIKE '%' + @DeviceSearchText + '%')

"query cannot execute properly" when I am opening it in browser

Can you access ServiceCenter and show us the detailed error? Do you have both inputs set to Expand Inline No?

"add device" has the sql query. nothing in service center

Hello

if this works 

Select {Device}.*, {Customer}.*, 0
from {Device}
inner join {Customer} on {Customer}.[Id] = {Device}.[CustomerId]
where {Device}.[Id] not IN
(select {GroupMember}.[DeviceId] 
from {GroupMember}   
where {GroupMember}.[DeviceGroupId] = @DeviceGroupId and {GroupMember}.[DeviceId] IN {GroupMember}.[DeviceId] and {GroupMember}.[IsActive]=1)

This shoudl work too

Select {Device}.*, {Customer}.*, 0
from {Device}
inner join {Customer} on {Customer}.[Id] = {Device}.[CustomerId]
where {Device}.[Id] not IN
(select {GroupMember}.[DeviceId] 
from {GroupMember}   
where {GroupMember}.[DeviceGroupId] = @DeviceGroupId and {GroupMember}.[DeviceId] IN {GroupMember}.[DeviceId] and {GroupMember}.[IsActive]=1)
AND {Device}.[SimDeviceKey] LIKE '%' + @DeviceSearchText + '%'

Note: that if you making a filter with like you do not need to do a validation if is "" because like will allways try to find if your value in any of the records since is empty, it will be in all of them.

If you use or it will allways return all values values in the filter ignoring the "not in" condition before.

Other question why do you have this "{GroupMember}.[DeviceId] IN {GroupMember}.[DeviceId]" like isn't this condition allways true?

BR

What about your parameters -  are they Expand Inline No?

It's very odd that you're seeing an error in browser but not Service Center.

look the query works fine but not with the keyword

I don't know why I am getting an error  " getcustomerwithdevice" there is a foreign key  customerid in device but i am also fetching that so i don't feel it should get an error fetching it

Your SELECT clauses are different in both screenshots. Try removing the zero after {Device}.* and see if you can execute it then.

SyntaxEditor Code Snippet

this one is working fine and also you have to pass the search keyword into the parameter with "%searchkeyword%" percentage sign on both side for null values (for showing all the values without any search word)

Select {Device}.*, {Customer}.*, 0
from {Device}
inner join {Customer} on {Customer}.[Id] = {Device}.[CustomerId]
where {Device}.[Id] not IN
(select {GroupMember}.[DeviceId] 
from {GroupMember}   
where {GroupMember}.[DeviceGroupId] = @DeviceGroupId and  {GroupMember}.[IsActive]=1) and
(({Device}.[SimDeviceID] LIKE @DeviceSearchText) or
({Device}.[SimDeviceKey] like @DeviceSearchText) or 
({Device}.[SimNumber] like @DeviceSearchText) or
({Customer}.[FirstName] like @DeviceSearchText) or
({Customer}.[LastName] like @DeviceSearchText))