Using NVL and to_char
844783Apr 27 2011 — edited Apr 27 2011Hi guys,
Some help needed here; I'm using the following code to create a report in Oracle APEX 3.0:
select "EBA_BT_PROBLEM"."ID" as " BUG ID",
"EBA_BT_PROBLEM"."SUBJECT" as "SUBJECT",
"EBA_BT_URGENCY"."URGENCY_NAME" as "PRIORITY",
"EBA_BT_USER"."LOGIN_NAME" as "ASSIGNED TO",
"EBA_BT_PROBLEM"."SUBMITTED_BY_ID" as "SUBMITTED BY"
from "EBA_BT_PROBLEM" INNER JOIN "EBA_BT_USER" ON "EBA_BT_PROBLEM"."ASSIGNED_TO_ID" = "EBA_BT_USER"."ID"
JOIN "EBA_BT_URGENCY" ON "EBA_BT_PROBLEM"."URGENCY_ID" = "EBA_BT_URGENCY"."ID"
JOIN "EBA_BT_STATUS" ON "EBA_BT_PROBLEM"."STATUS_ID"
= "EBA_BT_STATUS"."ID"
JOIN "EBA_BT_PRODUCT" ON "EBA_BT_PROBLEM"."PRODUCT_ID"
= "EBA_BT_PRODUCT"."ID"
JOIN "EBA_BT_CATEGORY" ON "EBA_BT_PROBLEM"."CATEGORY_ID"
= "EBA_BT_CATEGORY"."ID"
where
to_char("EBA_BT_PROBLEM"."STATUS_ID") = NVL(:P64_STATUS,to_char("EBA_BT_PROBLEM"."STATUS_ID"))
It is returning all of the fields and data that I am after, the only issue I am having is with my "where". I have a drop down select box called P64_STATUS, which allowes me to chose from several options, which will in turn give me different outputs of the report. It does this ok, but when it is set to the defualt or "null" value, I want to to just give me all of the possible rows from the Select; instead it gives me nothing back.
So in essence I need to correct the code in my where clause, I think mainly here:
NVL(:P64_STATUS,to_char(*"EBA_BT_PROBLEM"."STATUS_ID"*))
in order to give me what I am after
I've tried to explain what I am after as best I can, any help would be much appreciated; thanks!