Home » SQL & PL/SQL » SQL & PL/SQL » Query Returning Null Values
Query Returning Null Values [message #211766] Tue, 02 January 2007 04:02 Go to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Dear All

I have following query

SELECT max(to_number(substr(Order_no,3,6)))+1 into ord_no1 FROM order_master where trans_mode = 'S'
AND ORDER_DATE =(SELECT TRUNC(MAX(ORDER_DATE)) FROM ORDER_MASTER WHERE TRANS_MODE = 'S') ;

Sometimes this Query is returning Null value Even though Order_no and Order_date is existing

Can Anyone help me ?

Thanks

Sarfraz

Re: Query Returning Null Values [message #211770 is a reply to message #211766] Tue, 02 January 2007 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How long is the value of Order_no in these cases?

If it is less than 3 chrs then substr(val,3,6) will return null.
Re: Query Returning Null Values [message #211772 is a reply to message #211770] Tue, 02 January 2007 04:13 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
No, the value of order_no and Order_date is alright
Last Order_no is 06517283 so substr(order_no,3,6) will be 517283
and Last Order_date is 27-Dec-06 09:43:06

Thanks
Re: Query Returning Null Values [message #211777 is a reply to message #211772] Tue, 02 January 2007 04:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is because of the ORDER_DATE subquery:
SELECT TRUNC(MAX(order_date))
FROM order_master
WHERE record looks like your example;

return value
------------
27.12.2006.
But, your WHERE clause looks like this:
-> WHERE order_date = upper subquery
-> WHERE 27.12.2006. 09:43:06 = 27.12.2006 -> this is NOT EQUAL

Re: Query Returning Null Values [message #211779 is a reply to message #211766] Tue, 02 January 2007 04:28 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
hi

SELECT max(to_number(substr(Order_no,3,6)))+1 into ord_no1 FROM order_master where trans_mode = 'S'
AND trunc(ORDER_DATE) =(SELECT TRUNC(MAX(ORDER_DATE)) FROM ORDER_MASTER WHERE TRANS_MODE = 'S') ;

try this..!!



~Suresh
Re: Query Returning Null Values [message #211780 is a reply to message #211777] Tue, 02 January 2007 04:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahhh - me and my literal mind.

When the OP said that it returned a null value, I naievly believed them - should have guessed that they meant that the query returned no rows.
Re: Query Returning Null Values [message #211784 is a reply to message #211779] Tue, 02 January 2007 04:44 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
I know that by truncating the outer query the problem will be solved but my question is how without truncating the outer query before it was working fine and suddenly it is giving problem

Thanks
Re: Query Returning Null Values [message #211792 is a reply to message #211784] Tue, 02 January 2007 05:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then 'suddenly' you started to add time-parts during inserts/updates.
Re: Query Returning Null Values [message #211797 is a reply to message #211792] Tue, 02 January 2007 06:09 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
I have'nt modify anything in form where i am using this query and time is there in other order nos also
I dont know what is happening
Re: Query Returning Null Values [message #211798 is a reply to message #211797] Tue, 02 January 2007 06:11 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you didn't provide sample data (those include CREATE TABLE order_master along with INSERT INTO order_master statements), we can only blindly guess what might be wrong.

Provide a few "working" and "not working" records so that we could see how and why is that happening.
Previous Topic: Automatically run pl/sql or Sql*Plus script
Next Topic: Retrieving data
Goto Forum:
  


Current Time: Sat Dec 10 08:50:10 CST 2016

Total time taken to generate the page: 0.11904 seconds