Home » SQL & PL/SQL » SQL & PL/SQL » how to get the values from the another table if the table has null
how to get the values from the another table if the table has null [message #613474] Wed, 07 May 2014 08:42 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I have table emp and xxc_emp, if emp table null then get it from xxc_emp, please i want to know how to write a query please.


Thanks

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613476 is a reply to message #613474] Wed, 07 May 2014 08:48 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define what you mean by "table has null"

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613477 is a reply to message #613474] Wed, 07 May 2014 08:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Assuming both tables have same structure:

 SELECT  *
   FROM  EMP
UNION ALL
 SELECT  *
   FROM  XXC_EMP
   WHERE 0 = (
              SELECT  COUNT(*)
                FROM  EMP
                WHERE ROWNUM = 1
             )
/


SY.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613478 is a reply to message #613476] Wed, 07 May 2014 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613481 is a reply to message #613476] Wed, 07 May 2014 08:59 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Define what you mean by "table has null"



In my EMP Table, assume sal column value is NULL then , i want to get it from xxc_emp.

Let me know if you have any doubts and help me!!

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613483 is a reply to message #613477] Wed, 07 May 2014 09:00 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
how can i use Join condition

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613485 is a reply to message #613481] Wed, 07 May 2014 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the relationship between emp and xxx_emp is what exactly?

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613488 is a reply to message #613485] Wed, 07 May 2014 09:06 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
And the relationship between emp and xxx_emp is what exactly?


Deptno, and how can i use the Outer Joins in between

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613489 is a reply to message #613481] Wed, 07 May 2014 09:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
CASE STATEMENT

EDIT : Did not see OPs post above as it's posted at same time. You want to join the tables, can you post a test case to show us what exactly you are trying to achieve. I suggested CASE STATEMENT assuming a simple requirement depending on whether values are null or not.

[Updated on: Wed, 07 May 2014 09:09]

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613490 is a reply to message #613477] Wed, 07 May 2014 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@SY,

Quote:
SELECT COUNT(*)
FROM EMP
WHERE ROWNUM = 1

The statement will always return one and only one record so what is the purpose of ROWNUM=1? Materialization?

I think in this case NOT EXISTS is a better choice (no need to count all rows just check if one exists).

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613492 is a reply to message #613488] Wed, 07 May 2014 09:12 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Wed, 07 May 2014 15:06
Quote:
And the relationship between emp and xxx_emp is what exactly?


Deptno, and how can i use the Outer Joins in between


The link between two tables that store (presumably) employee information is deptno?
Post column lists and unique keys for both tables.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613493 is a reply to message #613490] Wed, 07 May 2014 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 07 May 2014 15:08

@SY,

Quote:
SELECT COUNT(*)
FROM EMP
WHERE ROWNUM = 1

The statement will always return one and only one record so what is the purpose of ROWNUM=1? Materialization?

I would imagine it's to stop it counting all the rows in the table.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613494 is a reply to message #613481] Wed, 07 May 2014 09:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
mist598 wrote on Wed, 07 May 2014 19:29
Let me know if you have any doubts and help me!!


For help, TEST CASE please.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613495 is a reply to message #613492] Wed, 07 May 2014 09:18 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
The link between two tables that store (presumably) employee information is deptno?


Yes .

Quote:
Post column lists and unique keys for both tables.


Sorry cookiemonster i have used 5 tables and that are belonging to the custom Tables have more than 100 columns .


1) Before i have 100 records
2) i added one column in the select stmt and ran the query, i want to display the only 100 records

Before and after adding same results i want...

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613496 is a reply to message #613494] Wed, 07 May 2014 09:21 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi i have 100 records before, after adding the only one column also i want only 100 records.

for that i have taken emp, xxc_emp table joins , but how can i use outer joins in between .

1) i ran the original query i got 100 records
2) after adding the one column , i am getting 86 records only

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613497 is a reply to message #613490] Wed, 07 May 2014 09:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Wed, 07 May 2014 10:08

The statement will always return one and only one record so what is the purpose of ROWNUM=1? Materialization?

I think in this case NOT EXISTS is a better choice (no need to count all rows just check if one exists).



ROWNUM = 1 is not for materialization. And no, it will not return one and only one row. It will return 0 or 1. When OP said EMP is NULL I thought he meant empty. Anyway, without ROWNUM = 1 Oracle wil read whole table which can take some time while with ROWNUM = 1 it will stop after fetching first row. And yes, you are right, NOT EXISTS is a better choice. And, in fact, optimizer getting smarter and smarter. It replace it with NOT EXISTS anyway:

SQL> EXPLAIN PLAN FOR
  2   SELECT  *
  3     FROM  EMP
  4  UNION ALL
  5   SELECT  *
  6     FROM  EMP1
  7     WHERE 0 = (
  8                SELECT  COUNT(*)
  9                  FROM  EMP
 10  --                WHERE ROWNUM = 1
 11               )
 12  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3884441943

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |    42 |  1582 |     7  (58)| 00:00:01 |
|   1 |  UNION-ALL          |                   |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP               |    14 |   546 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
|*  3 |   FILTER            |                   |       |       |            |          |
|   4 |    TABLE ACCESS FULL| EMP1              |    28 |  1036 |     3   (0)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | EMP_ONE_PRESIDENT |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
   3 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP"))

17 rows selected.

SQL> EXPLAIN PLAN FOR
  2   SELECT  *
  3     FROM  EMP
  4  UNION ALL
  5   SELECT  *
  6     FROM  EMP1
  7     WHERE 0 = (
  8                SELECT  COUNT(*)
  9                  FROM  EMP
 10                  WHERE ROWNUM = 1
 11               )
 12  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4137344972

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |    42 |  1582 |     7  (58)| 00:00:01 |
|   1 |  UNION-ALL          |                   |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP               |    14 |   546 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
|*  3 |   FILTER            |                   |       |       |            |          |
|   4 |    TABLE ACCESS FULL| EMP1              |    28 |  1036 |     3   (0)| 00:00:01 |
|*  5 |    COUNT STOPKEY    |                   |       |       |            |          |
|   6 |     INDEX FULL SCAN | EMP_ONE_PRESIDENT |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

   3 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE ROWNUM=1))
   5 - filter(ROWNUM=1)

19 rows selected.

SQL>


SY.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613498 is a reply to message #613497] Wed, 07 May 2014 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Wed, 07 May 2014 15:18
Quote:
The link between two tables that store (presumably) employee information is deptno?


Yes .



So do emp and xxx_emp both only have 1 record per deptno?
Because if they do they are really badly misnamed.

mist598 wrote on Wed, 07 May 2014 15:18

Quote:
Post column lists and unique keys for both tables.


Sorry cookiemonster i have used 5 tables and that are belonging to the custom Tables have more than 100 columns .

So post only the two relevant tables along with the relevant columns.
And the only thing stopping you from posting the details of the unique keys is your usual lazyness. So stop being lazy and post the information requested.

mist598 wrote on Wed, 07 May 2014 15:18

1) Before i have 100 records
2) i added one column in the select stmt and ran the query, i want to display the only 100 records

The above boils down to:
1) I have a query I'm not going to show you.
2) I modified the query in a way I'm not going to tell you.
3) Something happened and I'm not going to tell you what.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613499 is a reply to message #613496] Wed, 07 May 2014 09:38 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Wed, 07 May 2014 15:21
Hi i have 100 records before, after adding the only one column also i want only 100 records.

for that i have taken emp, xxc_emp table joins , but how can i use outer joins in between .

1) i ran the original query i got 100 records
2) after adding the one column , i am getting 86 records only


By specifying an outer-join obviously. Have you bothered to look up the syntax for outer-joins in the documentation?
As usual it has examples.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613502 is a reply to message #613497] Wed, 07 May 2014 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Wed, 07 May 2014 15:21
And no, it will not return one and only one row. It will return 0 or 1.

Which is one row. Select aggregate without a group by always returns one row.
Michel misunderstood your intent by thinking of the rownum working in it's usual top-n fashion and limiting the number of rows return, but in this case it limits the number of rows counted.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613506 is a reply to message #613497] Wed, 07 May 2014 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And, in fact, optimizer getting smarter and smarter. It replace it with NOT EXISTS anyway:


So I was right.
In your version optimizer is smart enough to convert the "bad" SQL to the "good" one but this may not be the case in our versions. Wink
I don't know your EMP1 definition (constraints, indexes) so I can't test it in mine.

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613511 is a reply to message #613502] Wed, 07 May 2014 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Michel misunderstood your intent by thinking of the rownum working in it's usual top-n fashion and limiting the number of rows return, but in this case it limits the number of rows counted.


Only with a smart version of the optimizer which can detect 0=(SELECT COUNT(*)...). I bet if you remove the ROWNUM=1 you have the same thing but can't test it without the test case (and Oracle version) to reproduce.
Otherwise, in the general case, ROWNUM applies to the result set returned by the query, that is after everything but ORDER BY.

SQL Reference 11.2
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
...
If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.


In "NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE ROWNUM=1)", the ROWNUM=1 is useless as it is implies in the NOT EXISTS (Oracle obviously stops at the first row it encounters, at least since 3 decades).

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613513 is a reply to message #613511] Wed, 07 May 2014 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 07 May 2014 16:51

Quote:
Michel misunderstood your intent by thinking of the rownum working in it's usual top-n fashion and limiting the number of rows return, but in this case it limits the number of rows counted.


Only with a smart version of the optimizer which can detect 0=(SELECT COUNT(*)...). I bet if you remove the ROWNUM=1 you have the same thing but can't test it without the test case (and Oracle version) to reproduce.
Otherwise, in the general case, ROWNUM applies to the result set returned by the query, that is after everything but ORDER BY.

I'm not sure I follow. Are you suggesting that SELECT COUNT(*) with and without a rownum restriction will give the same count?

Report message to a moderator

Re: how to get the values from the another table if the table has null [message #613514 is a reply to message #613513] Wed, 07 May 2014 11:31 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Are you suggesting that SELECT COUNT(*) with and without a rownum restriction will give the same count?


Yes, this what I was suggesting, so stupidly!
I should go to bed now!

Report message to a moderator

Previous Topic: unable truncate table in plsql block
Next Topic: Validate an IP Range
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 05:59:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.