asked on

finding groups based on values Oracle 12

I have to determine which spec_int have a tests where all have any result. The other group would be spec_int which have tests where one or more does not have a result_id

Have tried to put together inner queries to get counts of tests with results, then bring it out to an outer query that would then list the group where all tests have result, and/or the groups of spec_int in which one or more tests have no result_id.

Table spec_int contains spec_int_id and hold.
table test3 contains spec_int_id and test_ids,
table result4 contains test_ids and result_id.
Table 5 contains results.

I need to determine which spec_int's are not on hold and have tests that all have result_id's. Does not a matter what the result is, just whether there is a result for every test contained in a spec_int.

Data
Spec_int
Spec_int_id hold
1 N
2 N
3 N
4 Y

Spec_int_id Test result
1 69
1 55 5
2 87 8
2 44 6
3 45

Desired results:           
Groups  status
   1           open
   2          closed
  3           open
  4           on hold

Open in new window

CREATE TABLE SPEC_INT
(
  SPEC_INT_ID  NUMBER(10),
  HOLD         CHAR(1 BYTE)
);

CREATE TABLE TEST3
(
  SPEC_INT_IID  NUMBER(10),
  TEST_ID       NUMBER(10)
);

CREATE TABLE TESTS4
(
  RESULT_ID  NUMBER(10),
  TEST       NUMBER(10)
);

Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(1, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(2, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(3, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(4, 'y');

Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(8, 45);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(9, 87);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(6, 58);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(8, 69);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(9, 95);

Insert into TESTS4(RESULT_ID, TEST) Values(8, 87);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 45);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 58);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 69);
Insert into TESTS4(RESULT_ID, TEST) Values(5, 95);

Open in new window

HainKurt

try this

SELECT s.*,
       CASE hold
         WHEN 'Y'
         THEN
           'on hold'
         ELSE
           CASE (SELECT COUNT (1)
                   FROM TEST3 t3 INNER JOIN TESTS4 t ON t3.TEST_ID = t4.TEST
                  WHERE t3.SPEC_INT_IID = SPEC_INT_ID AND RESULT_ID IS NULL)
             WHEN 0 THEN 'closed'
             ELSE 'open'
           END
       END
         status
  FROM Spec_int s

Open in new window

I am assuming you insert a record into test4 for every record inserted into test3

Jblue R

ASKER

yep, looks like in the inserts for tests4 were cut off...

 Insert into TESTS4(RESULT_ID, TEST) Values(8, 87);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 45);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 58);
Insert into TESTS4(RESULT_ID, TEST) Values(NULL, 69);
Insert into TESTS4(RESULT_ID, TEST) Values(5, 95);

Open in new window

slightwv (䄆 Netminder)

John,

Can you double check your test data?

TEST3 has spec_int_ids that don't seem to match the SPEC_INT table. I don't see how you are getting your expected results with the data provided.

Jblue R

ASKER

from your code I get

SPEC_INT_ID HOLD STATUS
----------- ---- -------
1 n closed
2 n closed
3 n closed
4 y closed

1 and 3 should be open-- they have tests that do not have results.

HainKurt

check your data... it should work if you have correct data...
if all gives closed, then it means you dont have any open result in test4 for that spec where result is null...

Jblue R

ASKER

corrected data inserts:

 Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (3, 45);
Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (2, 87);
Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (1, 55);
Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (6, 58);
Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (1, 69);
Insert into TEST3
   (SPEC_INT_IID, TEST_ID)
 Values
   (2, 44);
COMMIT;

Open in new window

Jblue R

ASKER

it has been a bit confusing trying to build pseudo test data to match the names and functions of the tables.... thank you!!

Jblue R

ASKER

I have to say .. the elegance is a little stunning...... I worked your code back into the actual tables and seem to have the results I need. It will take a while for me to test it fully, but it sure looks good from here...

Thank you very much........ my apologies for the data confusion............ the confusion was mine.

Jblue R

ASKER

when I applied my data and tables, nothing came up closed. I have a meeting I must attend.. will return asap.. please look over my corrected data,,...

HainKurt

maybe you dont have nulls in test4 but empty string or spaces or 0...

Jblue R

ASKER

what I have are nulls, and 3 potential values... I ran the script with not in(1,2,3) and end up with all closed -- except for the cancelled.. no opens. If I turn it around and run the code with in(1,2,3) they all come back open... except the cancelled..

seems I need to first collect a count of spec_int_id with some (or all) not nulls .. and they would be open,.. the requirement for a spec_int_id to be closed is for all its tests to have a result.

HainKurt

I am confused...

pls give some sample for SPEC_INT, TEST3, TESTS4

and show what are you getting for that sample data...
and show what needs to show up here...

Jblue R

ASKER

Thank you for your patience...

 CREATE TABLE SPEC_INT
(  SPEC_INT_ID  NUMBER(10),
  HOLD         CHAR(1 BYTE));

CREATE TABLE TEST3
(  SPEC_INT_IID  NUMBER(10),
  TEST_ID       NUMBER(10));

CREATE TABLE TESTS4
(  RESULT_ID  NUMBER(10),
  TEST_ID    NUMBER(10));

Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(1, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(2, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(3, 'n');
Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(4, 'y');

Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(3, 45);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(2, 87);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(1, 55);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(6, 58);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(1, 69);
Insert into TEST3(SPEC_INT_IID, TEST_ID) Values(2, 44);

Insert into TESTS4(RESULT_ID, TEST_ID) Values(8, 87);
Insert into TESTS4(RESULT_ID, TEST_ID) Values(NULL, 45);
Insert into TESTS4(RESULT_ID, TEST_ID) Values(5, 58);
Insert into TESTS4(RESULT_ID, TEST_ID) Values(9, 69);
Insert into TESTS4(RESULT_ID, TEST_ID) Values(1, 44);
Insert into TESTS4(RESULT_ID, TEST_ID) Values(NULL, 55);

Open in new window

desired results
spec_int_id

1 in test3 there are 2 tests in test 4 69 shows a result 55 does not.. spec_int_id should be open
2 in test3 there are 2 tests 44/87 both show result in test4.. spec_int_id should be closed
3 in test3 shows test 45 has no result spec_int_id should be open
6 one test 58 shows a result spec_int_id should be closed

Jblue R

ASKER

results lined up better in preview screen

Jblue R

ASKER

the code I pasted consumes 30 lines.. are you seeing all? There are the three tables and inserts for each.....

if they are not showing up, I can just add some not as code.? I can try inserting some images.

slightwv (䄆 Netminder)

So the table SPEC_INT isn't the master table? There is no entry for 6.

What table holds the master list for spec_int_id's?

HainKurt

ok, we need to use LEFT JOIN not INNER JOIN

try this

SELECT s.*,
       CASE hold
         WHEN 'Y'
         THEN
           'on hold'
         ELSE
           CASE (SELECT COUNT (1)
                   FROM TEST3 t3 LEFT JOIN TESTS4 t ON t3.TEST_ID = t4.TEST
                  WHERE t3.SPEC_INT_IID = SPEC_INT_ID AND RESULT_ID IS NULL)
             WHEN 0 THEN 'closed'
             ELSE 'open'
           END
       END
         status
  FROM Spec_int s

Open in new window

in my first post I said

I am assuming you insert a record into test4 for every record inserted into test3

but now, looks like it is not the case...

HainKurt

and the data you posted her, ID: 42225600
I am assuming you made a typo and it should be 4 not 6 in TEST3!
and in the expected results...

Jblue R

ASKER

my error.. there should be a 6 in spec_int

Insert into SPEC_INT(SPEC_INT_ID, HOLD) Values(6, 'n');

FROM TEST3 t3 LEFT JOIN TESTS4 t ON t3.TEST_ID = t4.TEST -- a typo here as well? t should be t4?

thank you for your patience !! and your help .

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jblue R

ASKER

I appreciate your staying with me ..

your solution returns the desired results..

It will be later this afternoon before I can alter and apply to the real objects/data, but if I was faithful in my recreation.. It will be easy to make it work there are well..

Thank you !!

Jblue R

ASKER

Thank you for your patience & insight.