16
Views
4
Comments
Solved
SQL Help
Question

Hi Community,

Need some help in this.

I am looking to add the following function in the "------??" section.

1) After WHERE {Notification}.[Seenticket] = 0 , I would like to check if 

{Notification}.[Message] = " "

2) If {Notification}.[Message] = " " exists, I would like to have {Notification}.[SMSSeen] =0


Rank: #156
Solution

Hi Alvin,


You can add OR ({Notification}.[Message] = ' ' AND {Notification}.[SMSSeen] = 0) instead of your comment.

In this way, you're filtering that if the message is a single space then the SMS Seen is 0.

If you want to see if Message is empty, you should then apply OR ({Notification}.[Message] = '' AND {Notification}.[SMSSeen] = 0).


Hope it helps.


Cheers,

João

mvp_badge
MVP
Rank: #96

Hi Alvin,

As per by understanding, I think the WHERE clause Filter conditions should be connected with AND, as shown below

AND - strictly validates both LHS & RHS conditional statements

- I guess all the mentioned conditions should be strictly considered as part of the data filter

SELECT 
    {Ticket}.[TicketNumber],
    T.[CodeValue] AS TicketType,
    {Ticket}.[CreatedDate],
    {UserSMSRespond}.[Message]
FROM {Notification}
JOIN {Ticket} WITH (NOLOCK) ON {Ticket}.[TicketNumber] = {Notification}.[TicketNumber]
LEFT JOIN {UserSMSRespond} WITH (NOLOCK) ON T.[Id] = {Ticket}.[TicketTypeId]
    AND T.[Module] = @ModuleName
    AND T.[CodeType] = @TicketType
WHERE {Notification}.[SeenTicket] = 0
    AND {Notification}.[Message] = ''
    AND {Notification}.[SMSSeen] = 0
ORDER BY {Notification}.[TicketNumber] DESC


Hope this helps you!


Regards,

Benjith Sam