where to write the SQL query to implement a search box which will search data according to the device.name. think like a search box, after pressing the search button it will filter data like aggregate. how to implement that

SyntaxEditor Code Snippet

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)

Hi Vikas,

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

Afonso Carvalho wrote:

Hi Vikas,

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


yes, but where to put this "{Device}.[Name] LIKE '%' + @DeviceSearchText + '%' '' in my code so it wont effect the existing result

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.

Afonso Carvalho wrote:

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.

yes both IP are "no"


vikas chauhan wrote:



Here your test query failed because the first input is an Identifier  and you have a string. Try with no ""

Gabriel Cardoso wrote:

vikas chauhan wrote:



Here your test query failed because the first input is an Identifier  and you have a string. Try with no ""

yes it is an identifier but i am passing it as a string .. the input variable is also a "text" data type.




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

vikas chauhan wrote:

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


In this case, you have 3 output structures and your SELECT has 4 outputs

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

Solution

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))
Solution