I have simplified our requirement like this:
Our Forms application has this in one of the forms this hardcoded line:
SELECT 'cow, cat, bird, snake' fl FROM dual; -- fl = fields line
We have a new ref table with the following rows:
SELECT *
FROM
(
select 'cat' ft from dual -- ft = field table
union
select 'cow' from dual
union
select 'dog' from dual
union
select 'snake' from dual
) t1;
What we want to know is whether all hard-coded values in the Form is available in the DB or not.
How to do this using SQL?
In the above example they are not equal. Line has bird which is not in DB, and DB has dog which is not in the line.
It has to be exactly equal.
How do join the above 2 SQLs to output say 0 if there is an exact match and say -1 if they don't match?
PS: The ordering in the line and fields will be different and no value will repeat in both places. DB is 11g R2