PL/SQL Insert Query

[sqldevrequiretektips]

Table Name : WLCustAct_Emp
Structure and data

CustLifeNo WLActCatId WLDayId WeekNo WLEmpTypeId
330374 FRI 1
330374 FRI 1 C
330374 M FRI 1
330374 M FRI 1
330374 M FRI 1 C
330374 FRI 1
330374 FRI 1
330374 FRI 1 C
330374 FRI 1
330374 D MON 1
330374 M MON 1
330374 M MON 1
330374 D MON 1 C
330374 MON 1
330374 MON 1 C
330374 MON 1
330374 M MON 1 C
330374 D MON 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 M WED 1
330374 O WED 1
330374 O WED 1 C
330374 O WED 1
330374 WED 1
330374 WED 1 C
330374 WED 1
330374 M WED 1
330374 M WED 1 C

Table WLCust_Denormalized :

CustLifeNo,
WeekNo,
MON_Act_M,
MON_Act_O
MON_Act_D
MON_Emp_C
MON_Emp_F
MON_Emp_P

TUE_Act_M
TUE_Act_O
TUE_Act_D
TUE_Emp_C
TUE_Emp_F
TUE_Emp_P

WED_Act_M
WED_Act_O
WED_Act_D
WED_Emp_C
WED_Emp_F
WED_Emp_P

THU_Act_M
THU_Act_O
THU_Act_D
THU_Emp_C
THU_Emp_F
THU_Emp_P

FRI_Act_M
FRI_Act_O
FRI_Act_D
FRI_Emp_C
FRI_Emp_F
FRI_Emp_P

SAT_Act_M
SAT_Act_O
SAT_Act_D
SAT_Emp_C
SAT_Emp_F
SAT_Emp_P

SUN_Act_M
SUN_Act_O
SUN_Act_D
SUN_Emp_C
SUN_Emp_F
SUN_Emp_P

I want to insert recrods from In tblWLCust_Denormalized table from WLCustAct_Emp table

More in detail M,O,D Value belogs to Act So, And C,F,P value Belongs to Emp

After Insert query Table Denormalized will have only one record..

Any help really appriciate...

Thx
AD

 

[SantaMufasa]

Amar,

Is your question about the (PL/SQL) logic of how to consolidate the denormalised rows into normalised rows or is your question about how to do an INSERT in PL/SQL? Please clarify.

Also, Amar, could you show a sample of what your resulting normalised rows would look like they have been consolidated from the de-normalised rows?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:19 (02Feb05) UTC (aka "GMT" and "Zulu"),
@ 10:19 (02Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sqldevrequiretektips]

Thanks for your reply ...
I want to insert recrods from In tblWLCust_Denormalized table from WLCustAct_Emp table..

WLCustAct_Emp table created using tblWLCust_Denormalized table

Let me tell you in detail..

table WLCustAct_Emp_table get created from
WLCustAct & WLCustEmp table

WLCustAct table Structure
-- CustLifeNo WLActCatId WLDayId WeekNo

WLCustEmp table structure


-- CustLifeNo WLEmpTypeId WLDayId WeekNo

*****
And WLCustAct & WLCustEmp table created using Table WLCust_Denormalized :

I had created store procedure to achive this task For example :

DECLARE
@COUNTER NUMERIC
SELECT @COUNTER = 0
WHILE (@COUNTER <6)

BEGIN
SELECT @COUNTER = @COUNTER +1

Insert into WLCustAct (Cust_Life_Num,WLActCatId,WLDayId,WeekNo)

--MONDAY
select cust_life_num,merch_mon ,'MON',@COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_mon ,'MON',@COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_mon ,'MON',@COUNTER from WLCust_Denormalized
union all

--TUESDAY
select cust_life_num,merch_tue ,'TUE', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_tue ,'TUE', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_tue ,'TUE', @COUNTER from WLCust_Denormalized
union all

--WEDNESDAY
select cust_life_num,merch_wed ,'WED', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_wed ,'WED', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_wed ,'WED', @COUNTER from WLCust_Denormalized
union all

--THRUSDAY
select cust_life_num,merch_thu ,'THU', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_thu,'THU', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_thu,'THU', @COUNTER from WLCust_Denormalized
union all

--FRIDAY
select cust_life_num,merch_fri ,'FRI', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_fri,'FRI', @COUNTER from WLCust_Denormalized
union all
select cust_life_num, Delivery_fri,'FRI', @COUNTER from WLCust_Denormalized
union all


--SATURDAY
select cust_life_num,merch_sat ,'SAT', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_sat,'SAT', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_sat ,'SAT', @COUNTER from WLCust_Denormalized
union all

--SUNDAY

select cust_life_num,merch_sun ,'SUN', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,order_sun ,'SUN', @COUNTER from WLCust_Denormalized
union all
select cust_life_num,Delivery_sun ,'SUN', @COUNTER from WLCust_Denormalized


End

 

[sqldevrequiretektips]

If you really wants to see database

I am going to give you my username and password for Briefcase in yahoo so u can dowload database called db3.zip
which is access sample database...

http://www.briefcase.yahoo.com

username : juliolopaz
password : vihangamar

please help me out when you get a chance...

AD

 

[sqldevrequiretektips]

I am still waiting for your reply....

More in detail to my above question If you could help me out with logic/syntex to insert records in Denormalized table from Normalized table....

If any one else has any suggestion please let me know...

Thanks

AD