CODEWITHSUNDEEP
sqloracle-databaseexcelvba
Lucas Raphael Pianegonda
Lucas Raphael Pianegonda

Reputation: 1181

WHERE (set of conditions) OR (set of second conditions) OR ... written elegantly

I have the following problem:

I have a Oracle-database with Material Properties in it and I am developing a front-end in Excel-VBA for it.

There is a view ALL_TESTS which represents the whole table from which I want to load all properties in a Datasheet. To be precise I got a list of PROPERTIES, measured each with a specific TEST under specific CONDITIONS with a STATE of the specimen. For one MATERIAL of a COMPANY i always want the whole package to be loaded.

if I would do this:

SELECT * FROM ALL_TESTS WHERE COMPANY in (Company1, Company2, ...)
                        AND MATERIAL (Material1,Material2, ... )
                        AND Test in (Test1,Test2, ...)
                        AND Property in (Property1, Property2, ...)
                        AND Condition in (Condition1, Condition2, ...)
                        AND State in (State1,State2, ...)

I will get not only the tests I want but also records where the indexes do not match eachother. For example when the record matches company1, material2, test3,property4 and State2. But this is not what I want, I want the following:

SELECT * FROM ALL_TESTS WHERE (COMPANY = Company1
                        AND MATERIAL = Material1
                        AND Test = Test1
                        AND Property = Property1
                        AND Condition = Condition1
                        AND State = State1)

                        OR (COMPANY = Company2
                        AND MATERIAL = Material2
                        AND Test = Test2
                        AND Property = Property2
                        AND Condition = Condition2
                        AND State = State2)
                        OR ...

I there a way I can write that in a more elegant way, then just constructing a SQL query which is super long?

Thank you for your time!

Upvotes: 1

Views: 77

Answers (4)

Rusty
Rusty

Reputation: 2138

In addition to Gordon's solution I can suggest to use oracle object types with custom compare logic to handle NULL comparison issue:

CREATE OR REPLACE TYPE test_obj as object(
  company varchar2(100),
  material varchar2(100),
  property varchar2(100),
  condition varchar2(100),
  map member function get_unique_key return varchar2  
)
/

CREATE OR REPLACE TYPE BODY test_obj as 
  map member function get_unique_key return varchar2
  as
  begin
    return nvl(company,'?')||'|'||nvl(material,'?')||'|'||nvl(property,'?')||'|'||nvl(condition,'?');
  end;  
end;
/

DROP TABLE all_tests;

CREATE TABLE all_tests
(
  test          VARCHAR2(100),
  test_obj      TEST_OBJ
)
;

insert into all_tests
select 'TEST1', test_obj('XXX', null, 'test', null)
  from dual 
;

select * from all_tests
where test_obj = test_obj('XXX', null, 'test', null);

TEST     TEST_OBJ
------   --------------
TEST1    (XXX, , test, )

Upvotes: 0

MrZed
MrZed

Reputation: 85

Put your where in a (temp) table (columns: MATERIAL, Test, Property, Condition, State), insert the possible values into it (every OR is a row), then inner join this table to the original (ALL_TESTS), with all the columns from the temp table like:

Select *
from ALL_TESTS
inner join temp1 on ALL_TESTS.MATERIAL = temp1.MATERIAL 
AND ALL_TESTS.Test = temp1.Test
AND ALL_TESTS.Property = temp1.Property
.... all other columns

And so you could have a lot of rows in the temp1, all a different OR, and a simple select to ask for it

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You should be able to do:

SELECT *
FROM ALL_TESTS
WHERE (COMPANY, MATERIAL, Test, Property, Condition, State) IN
          ( (Company1, Material1, Test1, Property1, Condition1, State1),
            (Company2, Material2, Test2, Property2, Condition2, State2)
          )

Upvotes: 7

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

If you had the data for the matching companies in another table, then you could do a simple join and obtain your result:

SELECT at.*
FROM ALL_TESTS
INNER JOIN MATCHING_COMPANIES mc
    ON at.COMPANY   = mc.COMPANY AND
       at.MATERIAL  = mc.MATERIAL AND
       at.Test      = mc.Test AND
       at.Property  = mc.Property AND
       at.Condition = mc.Condition AND
       at.State     = mc.State

Of course, this requires a separate table, but creating a temp table in Oracle or VBA hopefully is not so much trouble than it cannot be done.

Upvotes: 0

Related Questions