asked on 3/9/2017

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_dispstats where trunc(service_date) = trunc(sysdate -1);
spool off;
exit;
END

Oracle Database SQL

Last Comment

Lawrence Avery

3/9/2017

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

3/9/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Sean Stuber

3/9/2017

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_dispstats
WHERE service_date >= TRUNC(SYSDATE - 1) AND service_date < TRUNC(sydate)

Lawrence Avery

3/9/2017

ASKER

I did forget to commit in sql developer. Thanks