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