Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL Help

7 views
Skip to first unread message

cmwa...@cal.ameren.com

unread,
Jul 11, 2007, 3:32:01 PM7/11/07
to
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 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 )
)

Michel Cadot

unread,
Jul 11, 2007, 3:51:07 PM7/11/07
to

<cmwa...@cal.ameren.com> a écrit dans le message de news: 1184182321....@57g2000hsv.googlegroups.com...

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


Charles Hooper

unread,
Jul 11, 2007, 3:56:53 PM7/11/07
to

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.

Chris L.

unread,
Jul 11, 2007, 4:07:17 PM7/11/07
to

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.

cmwa...@cal.ameren.com

unread,
Jul 11, 2007, 4:14:53 PM7/11/07
to
On Jul 11, 2:51 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <cmwat...@cal.ameren.com> a écrit dans le message de news: 1184182321.999728.9...@57g2000hsv.googlegroups.com...

Michel,

It works!!! You made it sound so simple!!!!

Thank you, thank you, thank you!!!

cmw

DA Morgan

unread,
Jul 11, 2007, 4:17:19 PM7/11/07
to
Just for fun:

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

Charles Hooper

unread,
Jul 11, 2007, 5:03:13 PM7/11/07
to
On Jul 11, 4:17 pm, DA Morgan <damor...@psoug.org> wrote:
> Just for fun:
>
> 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
> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.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 | | | |
----------------------------------------------------------------------------------------------------------------------

John K. Hinsdale

unread,
Jul 13, 2007, 12:26:02 PM7/13/07
to
On Jul 11, 4:07 pm, "Chris L." <dive...@uol.com.ar> wrote:

>
> 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

0 new messages