OraFAQ Forum: SQL & PL/SQL » SQL query using NVL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query using NVL (oracle 10.2.0.1.0,solaris10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query using NVL [message #413396]

Wed, 15 July 2009 09:13

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

Report message to a moderator

Re: SQL query using NVL [message #413398 is a reply to message #413396]

Wed, 15 July 2009 09:24

cookiemonster
Messages: 13961
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.

Report message to a moderator

Re: SQL query using NVL [message #413406 is a reply to message #413396]

Wed, 15 July 2009 10:11

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: SQL query using NVL [message #413479 is a reply to message #413406]

Thu, 16 July 2009 00:10

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??

Report message to a moderator

Re: SQL query using NVL [message #413482 is a reply to message #413479]

Thu, 16 July 2009 00:36

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: SQL query using NVL [message #413508 is a reply to message #413479]

Thu, 16 July 2009 03:13

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

Report message to a moderator

Re: SQL query using NVL [message #413531 is a reply to message #413508]

Thu, 16 July 2009 04:52

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.

Report message to a moderator

Re: SQL query using NVL [message #413535 is a reply to message #413531]

Thu, 16 July 2009 05:03

cookiemonster
Messages: 13961
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.

JRowbottom wrote on Thu, 16 July 2009 9:13

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.

Report message to a moderator

Re: SQL query using NVL [message #413536 is a reply to message #413531]

Thu, 16 July 2009 05:04

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you refuse to follow the guidelines?

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 16:00:42 CST 2024