Re: SQL Help

On Jul 11, 4: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 )
> )

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. Received on Wed Jul 11 2007 - 15:07:17 CDT