Home » SQL & PL/SQL » SQL & PL/SQL » As the current one hangs when run
As the current one hangs when run [message #251462] Fri, 13 July 2007 12:44 Go to next message
vamcs
Messages: 20
Registered: June 2007
Location: somerset
Junior Member

Trying to obtain orderline data for the Credit Memo project that produces examples where the quantity results in a fraction, using the following formula:
(Draft Revenue Amount - Invoiced Amount)/ Selling Price
As the current one hangs when run


select distinct order_number,l.LINE_NUMBER
,l.DRAFT_REVENUE_AMOUNT, l.INVOICED_AMOUNT,l.SELLING_PRICE,ol.ATTRIBUTE2 end_date
,l.DRAFT_REVENUE_AMOUNT- l.INVOICED_AMOUNT diff,
(nvl(l.DRAFT_REVENUE_AMOUNT- l.INVOICED_AMOUNT,0)/nvl(decode(l.SELLING_PRICE,0,1,l.SELLING_PRICE),1)) quantity
from xxrr_so_headers_all h, xxrr_so_schedules_all s
,xxrr_so_lines_all l,apps.oe_order_lines_all ol
where h.HEADER_ID=l.HEADER_ID
and l.LINE_ID=s.LINE_ID
and l.ORDERED_QUANTITY-l.CANCELLED_QUANTITY<>0
and nvl(l.DRAFT_REVENUE_AMOUNT,0)- nvl(l.INVOICED_AMOUNT,0) <>0
and to_date(ol.ATTRIBUTE2)between '01-JAN-2007'and sysdate
Re: As the current one hangs when run [message #251464 is a reply to message #251462] Fri, 13 July 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: As the current one hangs when run [message #251471 is a reply to message #251462] Fri, 13 July 2007 13:33 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that "apps.oe_order_lines_all" table produces Cartesian product (it is never joined to any other table). So, it doesn't really "hang", but takes a long time to finish.

BTW, shouldn't the condition you have mentioned ("(Draft Revenue Amount - Invoiced Amount)/ Selling Price") be a part of the WHERE clause?
Re: As the current one hangs when run [message #251482 is a reply to message #251462] Fri, 13 July 2007 15:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vamcs wrote on Fri, 13 July 2007 13:44

and to_date(ol.ATTRIBUTE2)between '01-JAN-2007'and sysdate



That is an invalid clause for at least two reasons. You cannot use a TO_DATE function without giving a format mask. Second, a DATE datatype cannot possibly fall between a character string and another DATE
Re: As the current one hangs when run [message #251483 is a reply to message #251462] Fri, 13 July 2007 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>That is an invalid clause for at least two reasons. You cannot use a TO_DATE function without giving a format mask. Second, a DATE datatype cannot possibly fall between a character string and another
Only for most cases of invalid; not all!
13:29:53 SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

13:29:53 SQL> create table no_joy(attr2 varchar2(11));

Table created.

13:29:54 SQL> insert into no_joy values('31-DEC-2006');

1 row created.

13:29:54 SQL> insert into no_joy values('02-JAN-2007');

1 row created.

13:29:54 SQL> select * from no_joy where to_date(attr2) between '01-JAN-2007' and sysdate;

ATTR2
-----------
02-JAN-2007



I do AGREE that this very poor coding technique.

[Updated on: Fri, 13 July 2007 15:43] by Moderator

Report message to a moderator

Re: As the current one hangs when run [message #251484 is a reply to message #251462] Fri, 13 July 2007 15:38 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
What he should have said is that you should NEVER use to_date without using a format mask, not that you couldn't. Not using a mask (as anacedent knows) is a very bad programming technique.
Previous Topic: Unique constraints
Next Topic: Table Design....
Goto Forum:
  


Current Time: Fri Dec 02 14:15:38 CST 2016

Total time taken to generate the page: 0.16395 seconds