Re: SQL Help

On Jul 11, 3:32 pm, cmwat..._at_cal.ameren.com wrote:
> 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 )
> )

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. Received on Wed Jul 11 2007 - 14:56:53 CDT