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.
Leave a Reply