Home » SQL & PL/SQL » SQL & PL/SQL » SQL query using NVL (oracle 10.2.0.1.0,solaris10)
SQL query using NVL [message #413396] Wed, 15 July 2009 09:13 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



Hi all,


SELECT OWNER,TABLE_NAME,LAST_ANALYZED
FROM DBA_TABLES
WHERE NVL(LAST_ANALYZED,SYSDATE-50) < SYSDATE-14
AND OWNER IN ('Mohan','Ram')
ORDER BY 2;

Can anybody let me know as what could be
the outcome of the above query.

As i get struck up in WHERE clause
which is highlighted.


Thanks in advance

Mohan
Re: SQL query using NVL [message #413398 is a reply to message #413396] Wed, 15 July 2009 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well since owner is invariably in upper case I would expect that query to return no rows.
As the for the highlighted line - seems fairly straight forward, what's your issue with it?

And next time you post code can you please use code tags - see the orafaq forum guide for details.
Re: SQL query using NVL [message #413406 is a reply to message #413396] Wed, 15 July 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From some of your previous topics:
BlackSwan wrote on Sat, 04 July 2009 19:32
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
...

BlackSwan wrote on Sat, 04 July 2009 17:11
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: SQL query using NVL [message #413479 is a reply to message #413406] Thu, 16 July 2009 00:10 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



SELECT owner,
table_name,
last_analyzed
FROM dba_tables
WHERE Nvl(last_analyzed,SYSDATE - 50) < SYSDATE - 14
AND owner IN ('Mohan','Ram')
ORDER BY 2;


Am i right now??
Re: SQL query using NVL [message #413482 is a reply to message #413479] Thu, 16 July 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted and the Preview Message button would show you if you'd used it.
There is a "Test" forum at the bottom of Forum Home page, I recommend you to use it to train yourself to post.

Regards
Michel

[Updated on: Thu, 16 July 2009 00:38]

Report message to a moderator

Re: SQL query using NVL [message #413483 is a reply to message #413479] Thu, 16 July 2009 00:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
You only know WHat you want?How can we decide you are right or wrong? Smile
Re: SQL query using NVL [message #413508 is a reply to message #413479] Thu, 16 July 2009 03:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When @cookiemonster said
Quote:
Well since owner is invariably in upper case I would expect that query to return no rows.
he was refering to the contents of the column, not the name.

Rather than change the line to
AND owner IN ('Mohan','Ram')
you should have changed it to
AND OWNER IN ('MOHAN','RAM')


In answer to your original question, I would expect your query to return details about all the objects in dba objects owned by either MOHAN or RAM that are either not analysed, or that were analysed over 14 days ago
Re: SQL query using NVL [message #413531 is a reply to message #413508] Thu, 16 July 2009 04:52 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



Thanks for you information.

I will explain what i understood from your answer.


WHERE NVL(LAST_ANALYZED,SYSDATE-50) < SYSDATE-14

say for example

SQL> select sysdate-50 from dual;

SYSDATE-5
---------
27-MAY-09


SQL> select sysdate-14 from dual;

SYSDATE-1
---------
02-JUL-09


U mean to say that the query will return all the objects owned by either Mohan or Ram either not analyzed or that were analyzed between 27-MAY-09 and 02-JUL-09.


Please confirm.
Re: SQL query using NVL [message #413535 is a reply to message #413531] Thu, 16 July 2009 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Mohan10g wrote on Thu, 16 July 2009 10:52


U mean to say that the query will return all the objects owned by either Mohan or Ram either not analyzed or that were analyzed between 27-MAY-09 and 02-JUL-09.

Please confirm.



No

JRowbottom wrote on Thu, 16 July 2009 9:13

In answer to your original question, I would expect your query to return details about all the objects in dba objects owned by either MOHAN or RAM that are either not analysed, or that were analysed over 14 days ago



This:
WHERE NVL(LAST_ANALYZED,SYSDATE-50) < SYSDATE-14


Is logically equivalent to this:
WHERE NVL(LAST_ANALYZED,SYSDATE-15) < SYSDATE-14


Which is the same as:
WHERE NVL(LAST_ANALYZED,SYSDATE- <any number greater than 14>) < SYSDATE-14


to do the logic you stated you'd need to use BETWEEN.
Re: SQL query using NVL [message #413536 is a reply to message #413531] Thu, 16 July 2009 05:04 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you refuse to follow the guidelines?

Regards
Michel
Previous Topic: Cartesian join (merged 3)
Next Topic: sorting by same date type column in different tables
Goto Forum:
  


Current Time: Mon Dec 05 11:00:50 CST 2016

Total time taken to generate the page: 0.11018 seconds