Home » SQL & PL/SQL » SQL & PL/SQL » not in, not exists query
not in, not exists query [message #608200] Mon, 17 February 2014 05:27 Go to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

Can you please help me on this query ,how to write a query..

I have only regal_inv_landed_cost_tab where organization_id=81 records.Columns are(organization_id,inventory_id)

I have mtl_system_items , with having records in organization_id=81 and 82 also. columns are (inventory_item_status_code,invenotry_id, organization_id)

My requirement is,i want Inactive,not exists and not in the both organization_id in(81,82).

Then after i want to display the Alerts.


Thank You


[MERGED by LF]

[Updated on: Tue, 18 February 2014 03:47] by Moderator

Report message to a moderator

Re: not in, not exists query [message #608201 is a reply to message #608200] Mon, 17 February 2014 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Mon, 17 February 2014 11:27

My requirement is,i want Inactive,

How is that defined?

mist598 wrote on Mon, 17 February 2014 11:27

not exists and not in the both organization_id in(81,82).

What not exists?
What not in?
Re: not in, not exists query [message #608204 is a reply to message #608201] Mon, 17 February 2014 05:36 Go to previous messageGo to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
1)i want inventory_item_status_code='Active' from the mtl_system_items table.
2)i want to use not in operator, where organization_id not in(81,82)
3)
Re: not in, not exists query [message #608205 is a reply to message #608204] Mon, 17 February 2014 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what's stopping you?
Re: not in, not exists query [message #608207 is a reply to message #608205] Mon, 17 February 2014 05:42 Go to previous messageGo to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
SELECT DISTINCT a.organization_id, 
                a.inventory_item_id 
FROM   regal.regal_inv_landed_cost_tab a, 
       mtl_system_items b 
WHERE  NOT EXISTS(SELECT 1 
                  FROM   regal.regal_inv_landed_cost_tab 
                  WHERE  a.organization_id = b.organization_id) 
AND b.inventory_item_id = a.inventory_item_id 
AND Nvl(b.inventory_item_status_code, 'Inactive') = 'Inactive' 
AND  a.organization_id not IN(SELECT organization_id 
                                    FROM   regal.regal_inv_landed_cost_tab 
                                    WHERE  a.organization_id = b.organization_id)



Please help me on this query what's going wrong?
Re: not in, not exists query [message #608209 is a reply to message #608207] Mon, 17 February 2014 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Standard response:
We don't have your tables.
We don't have your data.
So we can't run your code.
We don't know what it is doing
We don't know what you expect it to.

That said why do you have both NOT IN and NOT EXISTS? One should be sufficient.
Re: not in, not exists query [message #608210 is a reply to message #608209] Mon, 17 February 2014 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also the where clauses in the sub-queries do not reference the table in the from clause of the sub-query, which makes no sense.
Re: not in, not exists query [message #608211 is a reply to message #608210] Mon, 17 February 2014 07:14 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be clear, if you want more detailed help you need to post a Test case - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.

You'll also need to describe what you're trying to achieve in more detail.
Re: not in, not exists query [message #608212 is a reply to message #608211] Mon, 17 February 2014 07:19 Go to previous messageGo to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
CREATE TABLE statements here

We insert the records in the custom table XXC_INV_LANDED_COST_TAB from the program


[EDITED by LF: applied [spoiler] tags]

[Updated on: Tue, 18 February 2014 03:53] by Moderator

Report message to a moderator

Re: not in, not exists query [message #608213 is a reply to message #608211] Mon, 17 February 2014 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Mon, 17 February 2014 13:14
To be clear, if you want more detailed help you need to post a Test case - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.

You'll also need to describe what you're trying to achieve in more detail.

Re: not in, not exists query [message #608214 is a reply to message #608212] Mon, 17 February 2014 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem is with the ATTRIBUTE42 column.

Re: not in, not exists query [message #608216 is a reply to message #608214] Mon, 17 February 2014 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 22802
Registered: January 2009
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684
query [message #608259 is a reply to message #608200] Tue, 18 February 2014 03:45 Go to previous messageGo to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I have select item_number from xxc_inv_landed_cost_tab with having only org_id=81 values only.Columns in this table are inventory_item_id,ORGANIZATION_ID.

I have another table mtl_system_items with having records of org_id in(81,82).Coulmns are inventory_item_id,ORGANIZATION_ID

My requirement is , i want to check the item_number of xxc_inv_landed_cost_tab table in the org_id=82.

How to write a query please help
Re: query [message #608260 is a reply to message #608259] Tue, 18 February 2014 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Mon, 17 February 2014 14:14
To be clear, if you want more detailed help you need to post a Test case - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.

You'll also need to describe what you're trying to achieve in more detail.

Re: query [message #608261 is a reply to message #608260] Tue, 18 February 2014 03:50 Go to previous messageGo to next message
mist598
Messages: 944
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel Cadot, I have inserted the data through the bakend program and can you please give me the sample query please.

Otherwise i will provide the insert statements.
Re: query [message #608263 is a reply to message #608261] Tue, 18 February 2014 04:04 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Tue, 18 February 2014 09:50
Hi Michel Cadot, I have inserted the data through the bakend program and can you please give me the sample query please.

Why exactly do you think we're asking for insert statements?
What use do you think it is to us about the backend program? We don't have it, we can't use it.

mist598 wrote on Tue, 18 February 2014 09:50

Otherwise i will provide the insert statements.

Do that.
Previous Topic: materialized view
Next Topic: Oracle Compare 2 Tables
Goto Forum:
  


Current Time: Mon Sep 22 23:53:03 CDT 2014

Total time taken to generate the page: 0.05864 seconds