Hi, I couldn't find analyze the problem yet, but I found a simpler case:
This results in 0 rows (should be 1): select x from (select x from (select 1 as x) where x > 0 and x < 2) where x = 1; select x from (select x from (select x from system_range(1, 9)) where x > 2 and x < 4) where x = 3; This works: select x from (select x from (select 1 as x) where x > 0 and x < 2) where x > 0; select x from (select x from dual where x > 0 and x < 2) where x = 1; I think this is related to parameters in view. The explain plan for the queries is quite strange. Regards, Thomas On Friday, December 5, 2014, Noel Grandin <[email protected]> wrote: > Hmmm, this does not seem to be a BETWEEN issue, since it also fails with > ZEILE_RELATIV >=16 AND ZEILE_RELATIV <= 18 > > On 2014-12-03 05:21 PM, [email protected] wrote: > >> Hi, >> >> The following statement returns 0 rows as result, obviously, the result >> should be one row containing the value 17: >> >> *SELECT >> *ZEILE_RELATIV* >> FROM >> **( >> **SELECT >> *ZEILE_RELATIV >> *FROM >> **( >> **SELECT **16 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **17 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **18 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **19 **AS *ZEILE_RELATIV >> *) >> **WHERE >> *ZEILE_RELATIV *BETWEEN **16 **AND **18 >> **) ** >> WHERE >> *ZEILE_RELATIV *= **17* >> >> The same query using an equivalent IN clause works as expected (1 record >> as result): >> >> *SELECT >> *ZEILE_RELATIV* >> FROM >> **( >> **SELECT >> *ZEILE_RELATIV >> *FROM >> **( >> **SELECT **16 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **17 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **18 **AS *ZEILE_RELATIV >> *UNION ALL >> SELECT **19 **AS *ZEILE_RELATIV >> *) >> **WHERE >> *ZEILE_RELATIV *IN **(**16*,*17*,*18**) >> ) ** >> WHERE >> *ZEILE_RELATIV *= **17* >> >> >> >> Kind regards >> >> Christoff Schmitz >> >> F I N A R I S >> Financial Software Partner GmbH >> Sömmerringstrasse 23 >> 60322 Frankfurt am Main >> >> Fon: +49 (0)69 / 254 98 - 24 >> Mobile: +49 (0)176 / 206 34 186 >> Fax: +49 (0)69 / 254 98 - 50 >> eMail: mailto:[email protected] >> www: http://www.finaris.de <http://www.finaris.de/>und >> http://www.rapidrep.com <http://www.rapidrep.com/> >> >> ============================================================ >> ==================================================== >> Disclaimer >> The information contained in this e - mail and any attachments ( together >> the "message") is intended for the addressee >> only and >> may contain confidential and/or privileged information. If you have >> received the message by mistake please delete it and >> notify >> the sender and do not copy or distribute it or disclose its contents to >> anyone. >> >> FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 >> Frankfurt/Main, Germany >> Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. >> Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. >> Inf. Werner Märkl >> ============================================================ >> ==================================================== >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to >> [email protected] <mailto: >> [email protected]>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]>. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
