cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

cursor

Author

Topic

rolmau
Starting Member

10 Posts

Posted - 2009-12-31 : 11:55:46

Hi everyone,
using this cursor fine but I am not getting data into my dw_time table and dw_spend_fact
Any help please !!!

SQL> DECLARE
2 cursor c1 is select
3 a.transaction_id,a.supplier_id,a.CATEGORY_ID,a.gro ss,a.order_date,c.cardholder_id, d.department_id
4 FROM transaction_cleansig1 a,card_cleansig b,cardholder_cleansig c,department_cleansig d
5 WHERE a.card_id=b.card_id
6 AND
7 b.cardholder_id=c.cardholder_id
8 AND
9 c.department_id=d.department_id
10 order by a.transaction_id;
11
12 icount integer := 0;
13 spid varchar2(20);
14 ctid varchar2(20);
15 chid varchar2(20);
16 depid varchar2(20);
17 month integer;
18 year integer;
19
20 BEGIN
21 for rec in c1 loop
22
23 icount := icount + 1;
24
25 month := to_char(rec.order_date, 'MM');
26 year := to_char(rec.order_date, 'YYYY');
27 insert into dw_time values (icount,rec.order_date,year,month);
28
29 select supplier_id into spid from dw_supplier where supplier_id=rec.supplier_id;
30 select category_id into ctid from dw_category where CATEGORY_ID=rec.CATEGORY_ID;
31 select cardholder_id into chid from dw_cardholder where cardholder_id=rec.cardholder_id;
32 select department_id into chid from dw_department where department_id=rec.department_id;
33 insert into dw_spend_fact values (icount, depid, spid, ctid, chid, rec.gross);
34 end loop;
35 END;
36 /

PL/SQL procedure successfully completed.

SQL> select *from dw_time;

no rows selected

SQL> select *from dw_spend_fact;

no rows selected

I am not getting data into my dw_time table and dw_spend_fact
Any help please !!!

edit: moved to other forum

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-31 : 12:32:51

what dbms are you using?

rolmau
Starting Member

10 Posts

Posted - 2009-12-31 : 14:29:34

using 11g

rolmau
Starting Member

10 Posts

Posted - 2009-12-31 : 16:51:18

any suggestion to make it works !!!

event the new one ???

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-01 : 10:50:00

Might try posting on an Oracle forum. This is a Microsoft SQL Server forum. Though someone here may be able to help...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 01:51:06

Try at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

rolmau
Starting Member

10 Posts

Posted - 2010-01-04 : 08:17:14

Oki Thanks

rolmau
Starting Member

10 Posts

Posted - 2010-01-04 : 11:35:02

I have time dimension
time_id number,
month
year

I want to add today’s date to the Time dimension

Thanks Guys

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 11:56:13

Script Library is for working scripts that can be used by others. Please post your questions in the "New to SQL Server" section.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-01-05 : 04:16:46

you seem to have declared your cursor...but not actually opened it..ie fileld it with data. i suspect it's not going into the "for rec in c1 loop" statement as there are no records loaded.

put some "print I am here" type statements into the code....before and after the "for rec in c1 loop" lines....to see where it is stopping/not running.

rolmau
Starting Member

10 Posts

Posted - 2010-01-08 : 20:55:08

Oki Thanks AndrewMurphy I found the resolve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources