SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with trunc(date) and date

407031Aug 16 2005 — edited Aug 16 2005

Hello All!

Can anybody tell me why I get NULLs in the second SQL (with WHERE TRUNC(poddt) <> TRUNC(final_pod_dt))???

Regards,
Andrej

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> SELECT ordsecnum, TRUNC(poddt), TRUNC(final_pod_dt)
2 FROM v_ship_info
3 WHERE poddt <> final_pod_dt AND ordsecnum = '834X35702003'
4 /

ORDSECNUM TRUNC(PO TRUNC(FI
------------ -------- --------
834X35702003 03.05.05 03.05.05
834X35702003 03.05.05 03.05.05
834X35702003 03.05.05 03.05.05

SQL> SELECT ordsecnum, TRUNC(poddt), TRUNC(final_pod_dt)
2 FROM v_ship_info
3 WHERE TRUNC(poddt) <> TRUNC(final_pod_dt) AND ordsecnum = '834X35702003'
4 /

ORDSECNUM TRUNC(PO TRUNC(FI
------------ -------- --------
834X35702003 03.05.05
834X35702003 03.05.05
834X35702003 03.05.05

P.S. The view v_ship_info was created with left outer join . If I recreate the view with union it seems to work properly! Any idea?

Message was edited by:
anli

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 13 2005

Added on Aug 16 2005

5 comments

407 views