SQL – Methods of Reformatting into Equivalent Forms 1

December 2, 2009

(Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/24827610d4e40a0d

I’ve tried SQL to come up with the result below but am having no luck. Please, can someone help me!!!!!:)  Believe me, any and all help will be greatly appreciated.

What I need is the task_ids that have actv_code.short_name like ‘%FIN’ where actv_code.code = ‘Lead Craft’ and no actv_code.code that equals Outage Code

 

 

task table
task_id  task_code
  1         W123456
  2         07146566
  3         07146567
  4         06230001
  5         06123321
  6         06496334
  7         W642121
  8         05462111

actv_code table
task_id  code                 short_name
   1       Outage Code         R16
   4       Outage Code         R15
   6       Outage Code         R16
   1       Lead Craft          ZFM
   5       Lead Craft          EFIN
   6       Lead Craft          MFIN
   7       Lead Craft          IFIN
   8       Outage Code         R16
   8       Lead Craft          MFIN

Result Set
task_id  task_code
   5        06123321
   7        W642121 

 

This message thread, like several others, generated suggestions from several people. 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Michel Cadot suggested the following:

Just write it as you explain it:

select task_id
from actv_code a
where short_name like '%FIN'
  and code = 'Lead Craft'
  and not exists (select null from actv_code b where b.task_id = a.task_id and b.code = 'Outage Code')
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I then offered the following:

Let’s start out with the table definitions and insert statements:

CREATE TABLE TASK(
  TASK_ID NUMBER(10),
  TASK_CODE VARCHAR2(10));
CREATE TABLE ACTV_CODE(
  TASK_ID NUMBER(10),
  CODE VARCHAR2(20),
  SHORT_NAME VARCHAR2(10));
INSERT INTO TASK VALUES (1,'W123456');
INSERT INTO TASK VALUES (2,'07146566');
INSERT INTO TASK VALUES (3,'07146567');
INSERT INTO TASK VALUES (4,'06230001');
INSERT INTO TASK VALUES (5,'06123321');
INSERT INTO TASK VALUES (6,'06496334');
INSERT INTO TASK VALUES (7,'W642121');
INSERT INTO TASK VALUES (8,'05462111');
COMMIT;

INSERT INTO ACTV_CODE VALUES (1,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (4,'Outage Code','R15');
INSERT INTO ACTV_CODE VALUES (6,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (1,'Lead Craft','ZFM');
INSERT INTO ACTV_CODE VALUES (5,'Lead Craft','EFIN');
INSERT INTO ACTV_CODE VALUES (6,'Lead Craft','MFIN');
INSERT INTO ACTV_CODE VALUES (7,'Lead Craft','IFIN');
INSERT INTO ACTV_CODE VALUES (8,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (8,'Lead Craft','MFIN');
COMMIT;

Let’s determine the list TASK_IDs that you definitely do not want:

SELECT DISTINCT
  TASK_ID
FROM
  ACTV_CODE
WHERE
  CODE='Outage Code';

We can use that list to exclude specific rows from the query results like this:

SELECT
  T.TASK_ID,
  T.TASK_CODE
FROM
  TASK T,
  ACTV_CODE AC,
  (SELECT DISTINCT
    TASK_ID
  FROM
    ACTV_CODE
  WHERE
    CODE='Outage Code') ACN
WHERE
  T.TASK_ID=ACN.TASK_ID(+)
  AND ACN.TASK_ID IS NULL
  AND T.TASK_ID=AC.TASK_ID
  AND AC.SHORT_NAME LIKE '%FIN';

We basically created an outer join between the list of records and the list of records that we do not want (T.TASK_ID=ACN.TASK_ID(+)), and then specified that the record should not be in those records that we do not want (AND ACN.TASK_ID IS NULL).

   TASK_ID TASK_CODE
========== ==========
         7 W642121
         5 06123321

You may need to make minor adjustments to the above SQL statement.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Chris L. offered the following:

SELECT * FROM task WHERE task_id IN
(
  SELECT task_id FROM actv_code WHERE short_name LIKE '%FIN'
  AND code='Lead Craft'
  MINUS
  SELECT task_id FROM actv_code WHERE code='Outage Code'
);

Though I’d do something about that “short_name ends with FIN” filter,
and I’d try and generate a table with codes (Lead Craft, Outage Code,
etc) and use the ID’s not the descriptions.