We help IT Professionals succeed at work.

Using SQL*PLUS issue with where statement with trunc function

189 Views
Last Modified: 2017-03-09
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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 AverySystem Developer

Author

Commented:
I did forget to commit in sql developer. Thanks
Unlock the solution to this question.
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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.