0

I have a report that has two parameters, PM_DATE and PM_STOCK_ID. It's LOV query is

SELECT DISTINCT ID FROM STOCK_COUNT

I want to filter the parameter PM_STOCK_ID based on the date which user specifies in date parameter. In the where clause of LOV I tried using:

SELECT DISTINCT ID FROM STOCK_COUNT
WHERE TRUNC (SCHEDULE_DATE) = NVL (:PM_DATE, SCHEDULE_DATE)

but this didn't work.

I've also tried with a range using two parameters, P_SDATE (Start Date) and P_EDATE (End Date):

SELECT distinct id  FROM STOCK_COUNT
WHERE 
(TRUNC(SCHEDULE_DATE)) BETWEEN NVL(:P_SDATE, TRUNC(SCHEDULE_DATE)) AND NVL(:P_EDATE, TRUNC(SCHEDULE_DATE))

which gets error:

Parameter name: P_STOCK_ID Unparseable date: ""

How should I be doing this?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Imran Hemani
  • 599
  • 3
  • 12
  • 27
  • "this doesn't work" isn't very helpful. Why are you truncating `schedule_date` on the left hand side but not inside the NVL? – Alex Poole Feb 29 '16 at 12:53
  • I have edited the answer with the new query. It's giving the error Parameter name: P_STOCK_ID Unparseable date: "" – Imran Hemani Feb 29 '16 at 13:01
  • `unparseable date` is a Java date conversion error, so the cause does not lie in the SQL you've posted here - especially as none of those queries use P_STOCK_ID, the parameter referenced in the error message. – APC Mar 01 '16 at 07:48

1 Answers1

0

By just specifying the string :PM_DATE without a to_date function around it, you are getting implicit type conversion which is likely the root cause of the difficulty.

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7