Board index » MS SQL Server

All times are UTC



 what am I doing wrong in this procedure? 
Author Message
Alex Ivasc
#1 / 3
 what am I doing wrong in this procedure?

--  exec nmsched_create_span2
drop procedure nmsched_create_span2
go
create procedure nmsched_create_span2
as
declare
















  -- GET THE org_key's from nmsched_org_dim
  --
  declare c0 cursor for
    select distinct org_key
 from nmsched_org_dim
  --
  -- get all the orgs from nmsched_org_avail_fact
  --
  declare c1 cursor for
    select  priority_key, priority_rank
    from nmsched_priority_dim
    order by priority_rank
  --
  -- get the minimum date the org is available
  --
  declare c2 cursor for
    select a.sql_date, a.date_key
    from nmsched_date_dim a
    where a.sql_date=(select min(b.sql_date)
                     from nmsched_org_avail_fact b

  --
  -- get the minimum shift for the begin date
  --
  declare c3 cursor for
    select a.shift_key, a.shift_seq, a.shift_avail_time
    from nmsched_shift_dim a
    where a.shift_key = (select shift_key
                       from nmsched_org_avail_fact b


                         and b.shift_seq= (select min(shift_seq)
                                           from nmsched_org_avail_fact


  --
  -- get the maximum date the org is available
  --
  declare c4 cursor for
    select a.sql_date, a.date_key
    from nmsched_date_dim a
    where convert(varchar,a.sql_date,112)=(select
convert(varchar,max(c.sql_date),112)
                                          from nmsched_org_avail_fact b,
nmsched_date_dim c

                                            and b.date_key=c.date_key)
  --
  -- get the maximum shift for the end date
  --
  declare c5 cursor for
    select a.shift_key, a.shift_seq, a.shift_avail_time
    from nmsched_shift_dim a
    where a.shift_key = (select shift_key
                         from nmsched_org_avail_fact b


                           and b.shift_seq= (select max(shift_seq)
                                             from nmsched_org_avail_fact


   --
   --  get time span
   --
   declare c6 cursor for

           + sum(b.shift_avail_time)

     from nmsched_shift_dim b, nmsched_org_avail_fact c







       and b.shift_key  = c.shift_key
     group by c.org_key
begin
delete nmsched_org_span_avail_fact
open c0
--loop




begin
  open c1
--  loop


    print 'NO rows found in c1 '

  begin
    open c2


+char(10)



   --
      open c3


   --
        open c4

        print 'NO rows found in c4 for OrgKey:

   --
       open c5


   --
          open c6;


    begin
      insert into nmsched_org_span_avail_fact(org_key,start_date_key,
start_shift_key, start_shift_time_span, end_date_key, end_shift_key,
         priority_key, end_shift_time_span,
total_time_span,start_sql_date,start_shift_seq, end_sql_date, end_shift_seq,
         start_shift_avail_time, end_shift_avail_time, priority_rank)



 IF Cursor_Status('local', 'c2' ) >= 0
 or Cursor_Status('global', 'c2' ) >= 0
 Begin
     close c2
         deallocate c2
 End
 IF Cursor_Status('local', 'c3' ) >= 0
 or Cursor_Status('global', 'c3' ) >= 0
 Begin
     close c3
     deallocate c3
 End
 IF Cursor_Status('local', 'c4' ) >= 0
 or Cursor_Status('global', 'c4' ) >= 0
 Begin
     close c4
     deallocate c4
 End
 IF Cursor_Status('local', 'c5' ) >= 0
 or Cursor_Status('global', 'c5' ) >= 0
 Begin
     close c5
     deallocate c5
 End
 IF Cursor_Status('local', 'c6' ) >= 0
 or Cursor_Status('global', 'c6' ) >= 0
 Begin
     close c6
     deallocate c6
 End
    end

  end
  IF Cursor_Status('local', 'c1' ) >= 0
  Begin
    close c1
    deallocate c1
  End

  end
  CLOSE C0
  DEALLOCATE C0
end



Fri, 18 Nov 2005 17:54:41 GMT
Wayne Snyde
#2 / 3
 what am I doing wrong in this procedure?

Are you getting some error?
Perhaps you might post your intended purpose and perhaps some table
definitions with sample inserts... You will be more likely to get a helpful
response...

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C{*filter*}te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org


Quote:
> --  exec nmsched_create_span2
> drop procedure nmsched_create_span2
> go
> create procedure nmsched_create_span2
> as
> declare
















>   -- GET THE org_key's from nmsched_org_dim
>   --
>   declare c0 cursor for
>     select distinct org_key
>  from nmsched_org_dim
>   --
>   -- get all the orgs from nmsched_org_avail_fact
>   --
>   declare c1 cursor for
>     select  priority_key, priority_rank
>     from nmsched_priority_dim
>     order by priority_rank
>   --
>   -- get the minimum date the org is available
>   --
>   declare c2 cursor for
>     select a.sql_date, a.date_key
>     from nmsched_date_dim a
>     where a.sql_date=(select min(b.sql_date)
>                      from nmsched_org_avail_fact b

>   --
>   -- get the minimum shift for the begin date
>   --
>   declare c3 cursor for
>     select a.shift_key, a.shift_seq, a.shift_avail_time
>     from nmsched_shift_dim a
>     where a.shift_key = (select shift_key
>                        from nmsched_org_avail_fact b


>                          and b.shift_seq= (select min(shift_seq)
>                                            from nmsched_org_avail_fact

>                                            and b.date_key  =

>   --
>   -- get the maximum date the org is available
>   --
>   declare c4 cursor for
>     select a.sql_date, a.date_key
>     from nmsched_date_dim a
>     where convert(varchar,a.sql_date,112)=(select
> convert(varchar,max(c.sql_date),112)
>                                           from nmsched_org_avail_fact b,
> nmsched_date_dim c

>                                             and b.date_key=c.date_key)
>   --
>   -- get the maximum shift for the end date
>   --
>   declare c5 cursor for
>     select a.shift_key, a.shift_seq, a.shift_avail_time
>     from nmsched_shift_dim a
>     where a.shift_key = (select shift_key
>                          from nmsched_org_avail_fact b


>                            and b.shift_seq= (select max(shift_seq)
>                                              from nmsched_org_avail_fact

>                                                and date_key  =

>    --
>    --  get time span
>    --
>    declare c6 cursor for

>            + sum(b.shift_avail_time)

>      from nmsched_shift_dim b, nmsched_org_avail_fact c







>        and b.shift_key  = c.shift_key
>      group by c.org_key
> begin
> delete nmsched_org_span_avail_fact
> open c0
> --loop




> begin
>   open c1
> --  loop


>     print 'NO rows found in c1 '

>   begin
>     open c2




Quote:
> +char(10)


>       print 'NO rows found in c2 for OrgKey:


Quote:
>    --
>       open c3


>       print 'NO rows found in c3 for OrgKey:


- Show quoted text -

Quote:
>    --
>         open c4

>         print 'NO rows found in c4 for OrgKey:

>    --
>        open c5



>    --
>           open c6;


>     begin
>       insert into nmsched_org_span_avail_fact(org_key,start_date_key,
> start_shift_key, start_shift_time_span, end_date_key, end_shift_key,
>          priority_key, end_shift_time_span,
> total_time_span,start_sql_date,start_shift_seq, end_sql_date,
end_shift_seq,
>          start_shift_avail_time, end_shift_avail_time, priority_rank)







>  IF Cursor_Status('local', 'c2' ) >= 0
>  or Cursor_Status('global', 'c2' ) >= 0
>  Begin
>      close c2
>          deallocate c2
>  End
>  IF Cursor_Status('local', 'c3' ) >= 0
>  or Cursor_Status('global', 'c3' ) >= 0
>  Begin
>      close c3
>      deallocate c3
>  End
>  IF Cursor_Status('local', 'c4' ) >= 0
>  or Cursor_Status('global', 'c4' ) >= 0
>  Begin
>      close c4
>      deallocate c4
>  End
>  IF Cursor_Status('local', 'c5' ) >= 0
>  or Cursor_Status('global', 'c5' ) >= 0
>  Begin
>      close c5
>      deallocate c5
>  End
>  IF Cursor_Status('local', 'c6' ) >= 0
>  or Cursor_Status('global', 'c6' ) >= 0
>  Begin
>      close c6
>      deallocate c6
>  End
>     end

>   end
>   IF Cursor_Status('local', 'c1' ) >= 0
>   Begin
>     close c1
>     deallocate c1
>   End

>   end
>   CLOSE C0
>   DEALLOCATE C0
> end



Fri, 18 Nov 2005 18:00:46 GMT
Alex Ivasc
#3 / 3
 what am I doing wrong in this procedure?
good point.  I'm trying to load the nmsched_org_span_avail_fact table, based
on the records retrieved from these cursors.  C0 and C1 should be in a loop,
so while C0 and C1 is found continue to insert a record for each....
Hopefully that makes some sense.  Here's one of the few errors I get, even
though if I run the query manually, using the OrgKey 1528 - I get records
back.

NO rows found in c2 for OrgKey:  1528
NO rows found in c3 for OrgKey:  1528
NO rows found in c4 for OrgKey:  1528
NO rows found in c5 for OrgKey:  1528
NO rows found in c6 for OrgKey:  1528

Server: Msg 16905, Level 16, State 1, Procedure nmsched_create_span2, Line
106
The cursor is already open.
NO rows found in c1
Server: Msg 16905, Level 16, State 1, Procedure nmsched_create_span2, Line
106
The cursor is already open.
NO rows found in c1
Server: Msg 16905, Level 16, State 1, Procedure nmsched_create_span2, Line
106
The cursor is already open.
NO rows found in c1


Quote:
> Are you getting some error?
> Perhaps you might post your intended purpose and perhaps some table
> definitions with sample inserts... You will be more likely to get a
helpful
> response...

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), C{*filter*}te, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)

> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org



> > --  exec nmsched_create_span2
> > drop procedure nmsched_create_span2
> > go
> > create procedure nmsched_create_span2
> > as
> > declare
















> >   -- GET THE org_key's from nmsched_org_dim
> >   --
> >   declare c0 cursor for
> >     select distinct org_key
> >  from nmsched_org_dim
> >   --
> >   -- get all the orgs from nmsched_org_avail_fact
> >   --
> >   declare c1 cursor for
> >     select  priority_key, priority_rank
> >     from nmsched_priority_dim
> >     order by priority_rank
> >   --
> >   -- get the minimum date the org is available
> >   --
> >   declare c2 cursor for
> >     select a.sql_date, a.date_key
> >     from nmsched_date_dim a
> >     where a.sql_date=(select min(b.sql_date)
> >                      from nmsched_org_avail_fact b

> >   --
> >   -- get the minimum shift for the begin date
> >   --
> >   declare c3 cursor for
> >     select a.shift_key, a.shift_seq, a.shift_avail_time
> >     from nmsched_shift_dim a
> >     where a.shift_key = (select shift_key
> >                        from nmsched_org_avail_fact b


> >                          and b.shift_seq= (select min(shift_seq)
> >                                            from nmsched_org_avail_fact

> >                                            and b.date_key  =

> >   --
> >   -- get the maximum date the org is available
> >   --
> >   declare c4 cursor for
> >     select a.sql_date, a.date_key
> >     from nmsched_date_dim a
> >     where convert(varchar,a.sql_date,112)=(select
> > convert(varchar,max(c.sql_date),112)
> >                                           from nmsched_org_avail_fact b,
> > nmsched_date_dim c

> >                                             and b.date_key=c.date_key)
> >   --
> >   -- get the maximum shift for the end date
> >   --
> >   declare c5 cursor for
> >     select a.shift_key, a.shift_seq, a.shift_avail_time
> >     from nmsched_shift_dim a
> >     where a.shift_key = (select shift_key
> >                          from nmsched_org_avail_fact b


> >                            and b.shift_seq= (select max(shift_seq)
> >                                              from nmsched_org_avail_fact

> >                                                and date_key  =

> >    --
> >    --  get time span
> >    --
> >    declare c6 cursor for

> >            + sum(b.shift_avail_time)

> >      from nmsched_shift_dim b, nmsched_org_avail_fact c







> >        and b.shift_key  = c.shift_key
> >      group by c.org_key
> > begin
> > delete nmsched_org_span_avail_fact
> > open c0
> > --loop




> > begin
> >   open c1
> > --  loop


> >     print 'NO rows found in c1 '

> >   begin
> >     open c2



> > +char(10)


> >       print 'NO rows found in c2 for OrgKey:

> >    --
> >       open c3


> >       print 'NO rows found in c3 for OrgKey:

> >    --
> >         open c4

> >         print 'NO rows found in c4 for OrgKey:

> >    --
> >        open c5



> >    --
> >           open c6;

> >      print 'NO rows found in c6 for OrgKey:


- Show quoted text -

Quote:
> >     begin
> >       insert into nmsched_org_span_avail_fact(org_key,start_date_key,
> > start_shift_key, start_shift_time_span, end_date_key, end_shift_key,
> >          priority_key, end_shift_time_span,
> > total_time_span,start_sql_date,start_shift_seq, end_sql_date,
> end_shift_seq,
> >          start_shift_avail_time, end_shift_avail_time, priority_rank)







> >  IF Cursor_Status('local', 'c2' ) >= 0
> >  or Cursor_Status('global', 'c2' ) >= 0
> >  Begin
> >      close c2
> >          deallocate c2
> >  End
> >  IF Cursor_Status('local', 'c3' ) >= 0
> >  or Cursor_Status('global', 'c3' ) >= 0
> >  Begin
> >      close c3
> >      deallocate c3
> >  End
> >  IF Cursor_Status('local', 'c4' ) >= 0
> >  or Cursor_Status('global', 'c4' ) >= 0
> >  Begin
> >      close c4
> >      deallocate c4
> >  End
> >  IF Cursor_Status('local', 'c5' ) >= 0
> >  or Cursor_Status('global', 'c5' ) >= 0
> >  Begin
> >      close c5
> >      deallocate c5
> >  End
> >  IF Cursor_Status('local', 'c6' ) >= 0
> >  or Cursor_Status('global', 'c6' ) >= 0
> >  Begin
> >      close c6
> >      deallocate c6
> >  End
> >     end

> >   end
> >   IF Cursor_Status('local', 'c1' ) >= 0
> >   Begin
> >     close c1
> >     deallocate c1
> >   End

> >   end
> >   CLOSE C0
> >   DEALLOCATE C0
> > end



Fri, 18 Nov 2005 18:03:24 GMT
 
 Page 1 of 1
  [ 3 post ] 

 Relevant Pages 

1. What am I forgetting or doing wrong

2. TSQL question - what am I doing wrong?

3. what am i doing wrong?

4. Simple one what am i doing wrong

5. Darn what am i doing wrong?

6. What am I doing wrong

7. What am I doing wrong?

8. Joins - What I'am doing wrong

9. I think I am doing something wrong

10. what am I doing wrong?

11. What am I doing wrong...

12. What am I doing wrong with this SP??

 
Powered by phpBB® Forum Software