Oracle Where clause error, select count unexpected returns 0 when putting clauses in particular order

[Gert-Jan van Putten]

Yavor,

select count(*) as cnt from TABLE_X  D_ADE where VALID_TO_DATE >= to_date('12062017','ddmmyyyy') and VALID_FROM_DATE < to_date('12062017','ddmmyyyy') 

returns 15

When I reorder the where clause it goes wrong:

select count(*) as cnt from TABLE_X  D_ADE where  VALID_FROM_DATE < to_date('12062017','ddmmyyyy') and VALID_TO_DATE >= to_date('12062017','ddmmyyyy')

returns 0

When I run the queries in Oracle SQL developer they both return 15.

What's wrong?

Gert-Jan

 

[Yavor Nikolov]

How exactly do you execute these queries? (E.g. if using dbfit Query - could you share how exactly does it look like).

On thing in mind is that sometimes <, > characters are parsed as tag delimiters when you have the query wrapped in !- -! in Flow Mode: https://github.com/dbfit/dbfit/issues/284

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[Gert-Jan van Putten]

Yavor,

here is the complete script I used:

!path lib\*.jar

!|dbfit.OracleTest|

!|ConnectUsingFile|./FitNesseRoot/ConnectionOracleDWHA.txt|

!|Store Query|!- select count(*) as cnt

 from Table_x D_ADE

 where VALID_TO_DATE >= to_date('12062017','ddmmyyyy') and VALID_FROM_DATE < to_date('12062017','ddmmyyyy')  -! |fromTable|

!|dbfit.OracleTest|

!|ConnectUsingFile|./FitNesseRoot/ConnectionOracleDWHP.txt|

!|Store Query|!- select count(*) as cnt

 from Table_x D_ADE

 where VALID_TO_DATE >= to_date('12062017','ddmmyyyy') and VALID_FROM_DATE < to_date('12062017','ddmmyyyy')  -! |toTable|

|Compare Stored Queries|fromTable|toTable|

|cnt| 

Op woensdag 14 juni 2017 10:18:48 UTC+2 schreef Yavor Nikolov:

How exactly do you execute these queries? (E.g. if using dbfit Query - could you share how exactly does it look like).

On thing in mind is that sometimes <, > characters are parsed as tag delimiters when you have the query wrapped in !- -! in Flow Mode: https://github.com/dbfit/dbfit/issues/284

On Wed, Jun 14, 2017 at 10:23 AM, Gert-Jan van Putten <gjvp...@gmail.com> wrote:

Yavor,

select count(*) as cnt from TABLE_X  D_ADE where VALID_TO_DATE >= to_date('12062017','ddmmyyyy') and VALID_FROM_DATE < to_date('12062017','ddmmyyyy') 

returns 15

When I reorder the where clause it goes wrong:

select count(*) as cnt from TABLE_X  D_ADE where  VALID_FROM_DATE < to_date('12062017','ddmmyyyy') and VALID_TO_DATE >= to_date('12062017','ddmmyyyy')

returns 0

When I run the queries in Oracle SQL developer they both return 15.

What's wrong?

Gert-Jan

 

--
You received this message because you are subscribed to the Google Groups "dbfit" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.

[Gert-Jan van Putten]

Yavor,

The issue you mentioned is in fact the problem. 

Gert-Jan

PS. the script I send you was the one with the correct outcome.

Op woensdag 14 juni 2017 09:23:57 UTC+2 schreef Gert-Jan van Putten:

[Yavor Nikolov]

OK, 10x. The workaround is to avoid using !- -!. I.e. you should fit the query on single line. (Alternatively you may switch to Standalone Mode but that's rather something implying other side effects so it might be not ideal).

--

You received this message because you are subscribed to the Google Groups "dbfit" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.