Using SQL*PLUS issue with where statement with trunc function
In running the following I receive no rows selected but if I take the where clause off I receive a row.
In the tool called sql developer , I run the same sql statement the where clause works.
Why? Is it because the trunc function is not valid?
sqlplus -s 1234/1234<<END
spool farmerList.csv replace
select service_date, open_qty, closed_qty, cancelled_qty, open_closed_canc_qty
from dashboard_servticket_disps
spool off;
exit;
END
In the tool called sql developer , I run the same sql statement the where clause works.
Why? Is it because the trunc function is not valid?
sqlplus -s 1234/1234<<END
spool farmerList.csv replace
select service_date, open_qty, closed_qty, cancelled_qty, open_closed_canc_qty
from dashboard_servticket_disps
spool off;
exit;
END
If you have an index on service_date, you might not want to use trunc on the column, instead, provide a date range that allows the index to be used.
This would be for performance only. This is the same functionality as what you had but more efficient
SELECT service_date,
open_qty,
closed_qty,
cancelled_qty,
open_closed_canc_qty
FROM dashboard_servticket_disps
WHERE service_date >= TRUNC(SYSDATE - 1) AND service_date < TRUNC(sydate)
This would be for performance only. This is the same functionality as what you had but more efficient
SELECT service_date,
open_qty,
closed_qty,
cancelled_qty,
open_closed_canc_qty
FROM dashboard_servticket_disps
WHERE service_date >= TRUNC(SYSDATE - 1) AND service_date < TRUNC(sydate)
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.