CODEWITHSUNDEEP
sql
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11055

Sql query not returning desired result

ID  Dst Channel                             destination
1                                           123
2                                           123
3   SIP/5raghami-00043236                   91026
5   SIP/5raghami-00043232                   91132
6   SIP/107-00043230                        123
7   Local/123@from-queue-00006326;1         802

The following query just prints 6,7. My desired output is 1,2,6,7

    SQL = "SELECT id,duration FROM result where destination='123' or (destination='802' and [Dst Channel] like '%Local/123%')"
    RS.Open SQL, con
    while not rs.eof 
        response.write rs("id")
    rs.movenext
    wend
    rs.close

I think rows 1 and 2 are ignored because [Dst Channel] is null. Is there any problem with query I wrote? (perhaps problem with prantheses)

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

This is your query (with some cosmetic changes):

SELECT r.duration
FROM result r 
WHERE r.destination = '123' OR
      (r.destination = '802' AND r.[Dst Channel] LIKE '%Local/123%');

Your query is doing exactly what you intend. If ids 1 and 2 are not being returned, it is because destination contains something other than '123'.

The most likely assumption is that destination is a string. If so, it might contain "hidden" characters. You can check with this version:

SELECT r.duration
FROM result r 
WHERE r.destination LIKE '%123%' OR
      (r.destination = '802' AND r.[Dst Channel] LIKE '%Local/123%');

Often hidden characters pose as spaces at the beginning or end of strings. Note that the hidden characters could be in the LIKE pattern.

There is a very unlikely scenario where destination is a floating or fixed point number that you (or the application) has rounded for the question. Both floating and fixed point numbers will accurately represent the integer 123. So, the assumption is that a number close to 123 is stored in the column, but not exactly 123.

Upvotes: 1

Related Questions