Below is the code for my filters in a chart in APEX. Wanted to know if I could put in default values for the date filters, otherwise they are null and wont display anything until a date is inputed. There is a "Date From" and "Date to" value. Ideally I'd like for date to to be the current date and the date from to be from a year ago.
(MODEL = :P9_Model or :P9_Model is null)
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND SCRAP_DATE > :P9_DATE_FROM
AND SCRAP_DATE < :P9_DATE_TO
CodePudding user response:
(MODEL = :P9_Model or :P9_Model is null)
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND ((:P9_DATE_FROM IS NULL AND SCRAP_DATE > sysdate - INTERVAL '1' YEAR)
OR (SCRAP_DATE > :P9_DATE_FROM)
)
AND ((:P9_DATE_TO IS NULL AND SCRAP_DATE < SYSDATE)
OR (SCRAP_DATE < :P9_DATE_TO)
)
or use NVL
(MODEL = :P9_Model or :P9_Model is null)
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND SCRAP_DATE > NVL(:P9_DATE_FROM,SYSDATE - INTERVAL '1' YEAR)
AND SCRAP_DATE < NVL(:P9_DATE_TO,SYSDATE)
As a side note, it is advised to always pass the date format when converting strings to dates. :P9_DATE_FROM
is a string (everything in apex is a string) so it's better to write TO_DATE(:P9_DATE_FROM,'DD-MON-YYYY')
. Replace the 'DD-MON-YYYY' with the format of your choice. Same for :P9_DATE_TO
CodePudding user response:
you can try now()
(MODEL = :P9_Model or :P9_Model is null)
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND ( :P9_DATE_FROM = now() or SCRAP_DATE > :P9_DATE_FROM )
AND ( :P9_DATE_TO = now() or SCRAP_DATE < :P9_DATE_TO )