Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Do not include in the LoV the car numbers already used in the second table

AndriiPrincipSep 7 2019 — edited Sep 8 2019

I have two tables, in one table “Car number”, I have a list of car numbers there. In the second table “Registered numbers” I have registered car numbers there. In the “Registered Numbers” table, I fill out the form using the LOV list taken from the “Number Table” table. Is it possible to make sure that in the form when filling out "Registered numbers" in the LOV list there are only those numbers that have not been registered before. Since now all the numbers from the “Car number” table appear in the list. Thanks for the help)

CREATE TABLE number_car
id NUMBER(10) ,
number varcahar2(10),
date_add varcahar2(30)
);

CREATE TABLE registered_numbers
id NUMBER(10) ,
reg_number varcahar2(10),
date_reg varcahar2(30)
);

select for form table  registered_numbers (LoV);
select number as num,number from number_car

Comments

Pavel_p

Hi,

number is a reserved word, so either you have to enclose the column name with double-quotes or (more preferably) give it some other name like CAR_NUMBER (and assuming that your columns are varchar2, not varcahar2  you can create a LOV like:

select n.car_number d, n.id from  number_car n where not exists(select 1 from registered_numbers r where n.id = r.id);
--or
select car_number d, id r from number_car where id not in(select id from registered_numbers);

May I ask why you have 2 tables? Is the relation between tables 1:1 or 1:n (i.e. there can be more records for the same id in REGISTERED_NUMBERS table)?

Regards,

Pavel

Ora_Learner890

in addition to @Pavel solution, one another way using MINUS as below with appropriate WHERE clause to not mess-up multiple entries found in Second_Table as @Pavel already mentioned for.

select car_number from table_a  MINUS  select car_number from second_table

hope you already have done as per Pavel guide you

1 - 2

Post Details

Added on Sep 7 2019
#apex-discussions, #sql-pl-sql
2 comments
119 views