April 9, 2009 at 2:57 pm
Probably I would need to write a procedure to accomplish this. I am not familiar with writing procedures. I have data in two tables and need to populate the third table depending on the data in these two tables.
Table - empTraining
===============
columns and sample data are as follows (contains few thousand rows)
userId (int) classId (int) appCategory (varchar)
---------- ----------- ---------------------
111 25 NULL
111 26 Eq. Training/Experience
111 NULL NULL
222 25 Eq. Training/Experience
222 27 NULL
classId may be NULL. appCategory will be either NULL or a text 'Eq. Training/Experience'.
Table - classParamDetails
===================
columns and sample data are as follows.
classId (int) classParamId (tinyint) classParamUrl (varchar)
----------- -------------------- ----------------------
25 1 http://myurl1.com
25 2 http://myurl2.com
25 3 NULL
25 4 NULL
25 5 http://myurl3.com
26 1 NULL
26 2 http://myurl4.com
26 3 NULL
26 4 http://myurl5.com
26 5 NULL
This table will have 5 rows for each classId with different classParamId (1 to 5). classParamUrl may be NULL.
Table - courseProgress
=================
This table is empty and must be populated based on the data in the above tables.
For each row in the 'empTraining' table (process only rows with classId NOT NULL), 'n' number of records must be inserted into this table where 'n' is equal to number of 'NOT NULL classParamUrl' records in classParamDetails table for the corresponding classId.
Inserted data should be as follows.
userId (int) classId (int) classParamId (tinyint) status (varchar)
---------- ----------- -------------------- ---------------
111 25 1 Complete
111 25 2 Complete
111 25 5 Complete
111 26 2 Incomplete
111 26 4 Incomplete
Where status would be determined by whether appCategory is NULL (Complete) or contains text 'Eq. Training/Experience (Incomplete).
Can anyone help with writing a procedure for the above. Thanks for your help.
April 9, 2009 at 3:01 pm
Do a select that joins the two tables on the class_id column, and with a Where clause that eliminated the null rows that you don't want. Does that get you what you need? Or do you need help with that step?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2009 at 7:45 am
Thanks. Somehow I missed thinking from that angle. The following query worked fine for me.
Insert Into courseProgress (userId, classId, classParamId, status)
select empTraining.userId, empTraining.classId, classParamDetails.classParamId, (CASE WHEN appCategory = 'Eq. Training/Experience' THEN 'Incomplete' ELSE 'Complete' END) AS status
from empTraining, classParamDetails
where empTraining.classId IS NOT NULL and classParamDetails.classParamUrl IS NOT NULL and classParamDetails.classId = empTraining.classId
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply