Home » SQL & PL/SQL » SQL & PL/SQL » UNION and dependent LOV (Oracle R12)
UNION and dependent LOV [message #661949] Fri, 07 April 2017 01:37 Go to next message
umamagi
Messages: 1
Registered: April 2017
Junior Member
Hi all,

I have a requirement to create a valueset which will display LOT_NUMBERS from two tables.

1. MTL_LOT_NUMBERS and
2. A custom table XX_CUTS_TABLE

Both of the above tables have column LOT_NUMBER.

From first table only the lot_numbers corresponding to the organization selected on previous value set needs to be displayed along with all the lot_numbers from second table.

How could I achive this? Please help here.

Re: UNION and dependent LOV [message #661950 is a reply to message #661949] Fri, 07 April 2017 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Join the table on LOT_NUMBER (if this is the common column).

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Fri, 07 April 2017 02:43]

Report message to a moderator

Re: UNION and dependent LOV [message #661951 is a reply to message #661949] Fri, 07 April 2017 02:49 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
You do know that LOT_NUMBER is not unique in MTL_LOT_NUMBERS, not even when combined with ORGANIZATION_ID? The unique keys are these:

MTL_LOT_NUMBERS_U1	NORMAL	UNIQUE	TablespaceAPPS_TS_TX_IDX	INVENTORY_ITEM_ID 
                                                                        ORGANIZATION_ID 
                                                                        LOT_NUMBER
MTL_LOT_NUMBERS_U2	NORMAL	UNIQUE	TablespaceAPPS_TS_TX_IDX	GEN_OBJECT_ID

Re: UNION and dependent LOV [message #661962 is a reply to message #661951] Sat, 08 April 2017 03:20 Go to previous message
Littlefoot
Messages: 21331
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"LOV" and "R12" look like Forms might be involved. If that's so, when you select LOT_NUMBER from the first table (MTL_LOT_NUMBERS) and it is now in a form item, you can use it in second table's (XX_CUTS_TABLE) LOT_NUMBER List of Values by referencing MTL_LOT_NUMBERS.LOT_NUMBER in second item's LoV (i.e. its Record Group query), such as
select whatever
from xx_cuts_table
where lot_number = :mtl_lot_numbers.lot_number
Previous Topic: Pre Insert logic in Trigger
Next Topic: Hierarchical Queries
Goto Forum:
  


Current Time: Fri Jan 19 18:28:42 CST 2018

Total time taken to generate the page: 0.01561 seconds