Getting an unexpected result

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Getting an unexpected result

jose isaias cabrera

Greetings!

I am trying to create a report with this query,

select id, ProjID, PClass, PSubClass, bdate, lang, wDir
From LSOpenJobs Where
cust = 'PIPA' AND
fromLang = 'EN-CA' AND
(lang = 'DE-DE' OR lang = 'PT-BR') AND
PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
(PClass = 'Language' OR PClass = 'Technical') AND
(bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

I am getting a bunch of records, where I should not get any.  Where is
failing is the bdate.  All records are before the bdate.  Any thoughts on
why?  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting an unexpected result

jose isaias cabrera

"jose isaias cabrera" wrote...

> Greetings!
>
> I am trying to create a report with this query,
>
> select id, ProjID, PClass, PSubClass, bdate, lang, wDir
> From LSOpenJobs Where
> cust = 'PIPA' AND
> fromLang = 'EN-CA' AND
> (lang = 'DE-DE' OR lang = 'PT-BR') AND
> PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
> (PClass = 'Language' OR PClass = 'Technical') AND
> (bdate BETWEEN '2014-10-01' AND '2014-10-01')
> GROUP BY id;
>
> I am getting a bunch of records, where I should not get any.  Where is
> failing is the bdate.  All records are before the bdate.  Any thoughts on
> why?  Thanks.

Never mind.  My apologies for the bandwidth waste.  I needed one more set of
().

(PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid') AND

Igor probably telepathically told me the answer.  He is that good. :-)

jic

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting an unexpected result

Keith Medcalf
In reply to this post by jose isaias cabrera

You said:

  select id, ProjID, PClass, PSubClass, bdate, lang, wDir
    From LSOpenJobs
   Where cust = 'PIPA'
     AND fromLang = 'EN-CA'
     AND (lang = 'DE-DE' OR lang = 'PT-BR')
     AND PSubClass LIKE '%-Trans'
      OR PSubClass Like '%-Valid'
     AND (PClass = 'Language' OR PClass = 'Technical')
     AND (bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

did you perchance mean:

  select id, ProjID, PClass, PSubClass, bdate, lang, wDir
    From LSOpenJobs
   Where cust = 'PIPA'
     AND fromLang = 'EN-CA'
     AND (lang = 'DE-DE' OR lang = 'PT-BR')
     AND (PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid')
     AND (PClass = 'Language' OR PClass = 'Technical')
     AND (bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

Although the parser does not care how you format your query, human readability is improved by using nice formatting.

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of jose isaias cabrera
>Sent: Thursday, 16 October, 2014 12:46
>To: General Discussion of SQLite Database
>Subject: [sqlite] Getting an unexpected result
>
>
>Greetings!
>
>I am trying to create a report with this query,
>
>select id, ProjID, PClass, PSubClass, bdate, lang, wDir
>From LSOpenJobs Where
>cust = 'PIPA' AND
>fromLang = 'EN-CA' AND
>(lang = 'DE-DE' OR lang = 'PT-BR') AND
>PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
>(PClass = 'Language' OR PClass = 'Technical') AND
>(bdate BETWEEN '2014-10-01' AND '2014-10-01')
>GROUP BY id;
>
>I am getting a bunch of records, where I should not get any.  Where is
>failing is the bdate.  All records are before the bdate.  Any thoughts on
>why?  Thanks.
>
>josé
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting an unexpected result

Richard Hipp-3
On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf <[hidden email]> wrote:

>
> Although the parser does not care how you format your query, human
> readability is improved by using nice formatting.
>

Indeed, the error would have likely been spotted much faster had the query
been reformatted for readability.

That should perhaps be the first rule of trouble-shooting a query:  First
reformat the SQL so that the spacing and indentation help to elucidate the
intent of the query, not obscure it.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting an unexpected result

jose isaias cabrera

"Richard Hipp" wrote...

> On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf <[hidden email]>
> wrote:
>
>>
>> Although the parser does not care how you format your query, human
>> readability is improved by using nice formatting.
>>
>
> Indeed, the error would have likely been spotted much faster had the query
> been reformatted for readability.
>
> That should perhaps be the first rule of trouble-shooting a query:  First
> reformat the SQL so that the spacing and indentation help to elucidate the
> intent of the query, not obscure it.

Thank you both.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users