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 is the SQL I'm using. It doesn't return any rows. I think I've
tried this a dozen different ways but nothing returns the result set
that I need.
select t.task_id, t.task_code,
(select short_name from taskactv, actvcode
where taskactv.task_id = t.task_id
and actvcode.actv_code_id = taskactv.actv_code_id
and taskactv.actv_code_type_id = (select actv_code_type_id
from actvtype
where actv_code_type = 'Outage Code')) as
outage,
(select short_name from taskactv, actvcode
where taskactv.task_id = t.task_id
and actvcode.actv_code_id = taskactv.actv_code_id
and taskactv.actv_code_type_id = (select actv_code_type_id
from actvtype
where actv_code_type = 'Lead Craft')) as
LeadCraft
from task t
where t.task_id in
(
( select taskactv.task_id "id" from taskactv
where
taskactv.actv_code_id in (select actv_code_id
from actvcode
where short_name like '%FIN'
and actv_code_type_id = (select
actv_code_type_id
from actvtype
where actv_code_type = 'Lead
Craft'))
and
taskactv.actv_code_id not in (select actv_code_id
from actvcode
where short_name is null and
actv_code_type_id = (select
actv_code_type_id
from actvtype
where actv_code_type = 'Outage
Code'))
group by task_id having count(task_id) >= 2 )
)
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')
/
Regards
Michel Cadot
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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
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.
Michel,
It works!!! You made it sound so simple!!!!
Thank you, thank you, thank you!!!
cmw
explain plan for
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';
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 106 | 11 (19)|
00:00:01 |
|* 1 | HASH JOIN ANTI | | 2 | 106 | 11 (19)|
00:00:01 |
|* 2 | HASH JOIN | | 4 | 160 | 7 (15)|
00:00:01 |
|* 3 | TABLE ACCESS FULL | ACTV_CODE | 4 | 80 | 3 (0)|
00:00:01 |
| 4 | TABLE ACCESS FULL | TASK | 8 | 160 | 3 (0)|
00:00:01 |
| 5 | VIEW | | 4 | 52 | 4 (25)|
00:00:01 |
| 6 | HASH UNIQUE | | 4 | 100 | 4 (25)|
00:00:01 |
|* 7 | TABLE ACCESS FULL| ACTV_CODE | 4 | 100 | 3 (0)|
00:00:01 |
----------------------------------------------------------------------------------
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');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 7 (15)|
00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 57 | 7 (15)|
00:00:01 |
|* 2 | TABLE ACCESS FULL| ACTV_CODE | 4 | 128 | 3 (0)|
00:00:01 |
|* 3 | TABLE ACCESS FULL| ACTV_CODE | 4 | 100 | 3 (0)|
00:00:01 |
--------------------------------------------------------------------------------
Though the result sets are not exactly the same.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
It would be interesting to see how the different solutions perform as
additional data is added - I suspect that Michel Cadot's solution will
be the most efficient, even after his SQL statement is modified to
retrieve the extra TASK_CODE column that the OP indicated in the
desired result set. A re-test after modifying the tables so that
TAS_ID cannot be null:
ALTER TABLE TASK MODIFY (
TASK_ID NOT NULL);
ALTER TABLE ACTV_CODE MODIFY (
TASK_ID NOT NULL);
SELECT /*+ GATHER_PLAN_STATISTICS */
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';
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN ANTI | | 1 | 2 | 2 |00:00:00.01 | 21 |
898K| 898K| 701K (0)|
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 14 |
1066K| 1066K| 676K (0)|
|* 3 | TABLE ACCESS FULL | ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
| 4 | TABLE ACCESS FULL | TASK | 1 | 8 | 8 |00:00:00.01 | 7
| | | |
| 5 | VIEW | | 1 | 4 | 4 |00:00:00.01 | 7 | |
| |
| 6 | HASH UNIQUE | | 1 | 4 | 4 |00:00:00.01 | 7 |
| | |
|* 7 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
-----------------------------------------------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */
T.TASK_ID,
T.TASK_CODE
FROM
TASK T,
ACTV_CODE AC,
(SELECT
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';
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN ANTI | | 1 | 2 | 2 |00:00:00.01 |
21 | 898K| 898K| 663K (0)|
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 14 |
1066K| 1066K| 682K (0)|
|* 3 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
| 4 | TABLE ACCESS FULL| TASK | 1 | 8 | 8 |00:00:00.01 | 7
| | | |
| 5 | VIEW | | 1 | 4 | 4 |00:00:00.01 | 7 | |
| |
|* 6 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
----------------------------------------------------------------------------------------------------------------------
select /*+ GATHER_PLAN_STATISTICS */
t.task_id,
t.task_code
from actv_code a,
task t
where a.short_name like '%FIN'
and a.code = 'Lead Craft'
and a.task_id=t.task_id
and not exists (select null from actv_code b where b.task_id =
a.task_id and b.code = 'Outage Code');
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 2 |00:00:00.01 | 21 |
1066K| 1066K| 422K (0)|
|* 2 | HASH JOIN ANTI | | 1 | 1 | 2 |00:00:00.01 |
14 | 1066K| 1066K| 682K (0)|
|* 3 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
|* 4 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
| 5 | TABLE ACCESS FULL | TASK | 1 | 8 | 8 |00:00:00.01 | 7
| | | |
----------------------------------------------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */
* 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' );
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers |
--------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 2 |00:00:00.01 | 119 |
| 2 | TABLE ACCESS FULL | TASK | 1 | 8 | 8 |00:00:00.01 | 7
|
| 3 | MINUS | | 8 | | 2 |00:00:00.01 | 112 |
| 4 | SORT UNIQUE NOSORT| | 8 | 1 | 4 |00:00:00.01 | 56 |
|* 5 | TABLE ACCESS FULL| ACTV_CODE | 8 | 1 | 4 |00:00:00.01 |
56 |
| 6 | SORT UNIQUE NOSORT| | 8 | 1 | 4 |00:00:00.01 | 56 |
|* 7 | TABLE ACCESS FULL| ACTV_CODE | 8 | 1 | 4 |00:00:00.01 |
56 |
--------------------------------------------------------------------------------------------
Not requested, but just for entertainment:
select /*+ GATHER_PLAN_STATISTICS */
t.task_id,
t.task_code
from actv_code a,
task t
where a.short_name like '%FIN'
and a.code = 'Lead Craft'
and a.task_id=t.task_id
and a.task_id not in (select b.task_id from actv_code b where
b.task_id =
a.task_id and b.code = 'Outage Code');
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 2 |00:00:00.01 | 21 |
1066K| 1066K| 405K (0)|
|* 2 | HASH JOIN ANTI | | 1 | 1 | 2 |00:00:00.01 |
14 | 1066K| 1066K| 682K (0)|
|* 3 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
|* 4 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
| 5 | TABLE ACCESS FULL | TASK | 1 | 8 | 8 |00:00:00.01 | 7
| | | |
----------------------------------------------------------------------------------------------------------------------
Without help from Oracle 10.2.0.2's transformations:
select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */
t.task_id,
t.task_code
from actv_code a,
task t
where a.short_name like '%FIN'
and a.code = 'Lead Craft'
and a.task_id=t.task_id
and a.task_id not in (select b.task_id from actv_code b where
b.task_id =
a.task_id and b.code = 'Outage Code');
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 2 |00:00:00.01 | 34 |
| | |
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 14 |
1066K| 1066K| 646K (0)|
|* 3 | TABLE ACCESS FULL| ACTV_CODE | 1 | 4 | 4 |00:00:00.01
| 7 | | | |
| 4 | TABLE ACCESS FULL| TASK | 1 | 8 | 8 |00:00:00.01 | 7
| | | |
|* 5 | TABLE ACCESS FULL | ACTV_CODE | 4 | 1 | 2 |00:00:00.01
| 20 | | | |
----------------------------------------------------------------------------------------------------------------------
>
> 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,
One thing that can be done for quick lookups on the tail end of a
column
is to put a function-basd index on REVERSE(col), e.g.,
CREATE INDEX i_actv_code on actv_code (upper(reverse(short_name)))
then use
WHERE upper(reverse(short_name)) LIKE reverse('%FIN')
I use this to deal with things like searching for "credit card ends
with ..."
Here's an example of it in action; note how Oracle evaluates the
constant
expression and only then uses it in the filter predicate:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=SH&qid=65
Hope that helps,
John Hinsdale