where clause with date range give strange result

Question

In an xml publisher report I have the following where clause:

    where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number  
     and xx.ordered_date between NVL(TRUNC(fnd_date.canonical_to_date(:P_FROM_DATE)), xx.ORDERED_DATE) and NVL(TRUNC(fnd_date.canonical_to_date(:P_TO_DATE)), xx.ORDERED_DATE)  
    AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id

but its behavior is strange, with date range its result is ok, but with out date range its result is NULL.

I tried the following too:

where  NVL(:P_ORDER_NUMBER, xx.order_number) = xx.order_number  
 and ( NVL(TRUNC(fnd_date.canonical_to_date(:P_FROM_DATE)), xx.ORDERED_DATE) > xx.ordered_date  
       or  NVL(TRUNC(fnd_date.canonical_to_date(:P_TO_DATE)), xx.ORDERED_DATE) < xx.ordered_date )  
AND NVL(:P_CUSTOMER, xx.customer_id) = xx.customer_id

in concurrent program the dates parameters has FND_STANDARD_DATE.

I want if user select only order_number it should display only that order, but if user select a date range and a customer, it must display orders by that customer with in that date range. — Muhammad Nadeem, Jun 22 '15 at 10:28
could you show some sample data and expected output, it would help us to understand what you want — davegreen100, Jun 22 '15 at 10:29
https://docs.google.com/spreadsheets/d/1qv1ikEbAvNXfZIyXB6ZD_ystSEB3huo4_h7P6ULAbPg/edit?usp=sharing — Muhammad Nadeem, Jun 22 '15 at 10:41
Please add your information to the question. Questions should be self-contained, without requiring visiting other websites. Thanks. — Bob Jarvis - Слава Україні, Jun 22 '15 at 10:51
I think this is self-explained, need just a where clause where the user can select options and if no option is selected all the records should be displayed. — Muhammad Nadeem, Jun 22 '15 at 11:01

score 0 · Accepted Answer · answered Jun 22 '15 at 12:03

0

there are many ways of skinning this particular cat, this is just one method. By the way in your example you gave a date of 31st April, this does not exists.

SELECT TO_Char(ordered_date,'DD-MON-YYYY') as ordered_date,
    order_number, customer_name   
FROM order_tbl
WHERE NVL(:P_ORDER_NUMBER, order_number) = order_number
  AND ordered_date between NVL(TO_DATE(:P_FROM_DATE,'DD-MON-YYYY'),TO_DATE('01-MAR-1900','DD-MON-YYYY')) and NVL(to_date(:P_TO_DATE,'DD-MON-YYYY'),TO_DATE('31-DEC-2100','DD-MON-YYYY'))   
  AND NVL(:P_CUSTOMER_NAME, customer_name) = customer_name

answered Jun 22 '15 at 12:03

davegreen100

2,055
3
13
24

in example, where is 31st April? – Muhammad Nadeem Jun 24 '15 at 06:34
the problem is with YYYY, with YYYY it return nothing, and with 'RRRR' it works, can you tell me whats the difference? – Muhammad Nadeem Jun 24 '15 at 07:17
31st April is in your example 1 in the spreadsheet. I believe YYYY never assumes the century whereas RRRR does (so you can get away with not being explicit), i always use YYYY and have not problem as i specify the century so there may be other differences i am not aware of. – davegreen100 Jun 24 '15 at 07:43

where clause with date range give strange result

1 Answers1