Problem with Cursor Insert
-
Guys hi, I have a problem that i need your help. This is how the story goes.
A stored procedure inserts records of candidates from table candidates into the table Dim_candidate, that is used as a dimension in Olap Cubes.
The candidate data that is entered into the table concerns name, occupation, zip code, his examination card number and other information related to candidate. My problem lies with the occupation of the candidate. The SQL code loads the occupation_id from the table dim_occupation, and inserts it to the table Dim_candidates. Roughly the code goes like this.
Declare @Variables
Declare c1 cursor for
select cand_login, cand_lastname, cand_firstname, profession_description, etc..
from Candidates
Open c1
fetch next from c1 into
@cand_login, @last_name, @firstname, @prof_desc...
while @@fetch_status=0
begin ........
Now here is the problem. Because many candidates fill their occupation as 'Other', the occupation_id of the profession Description "Other" (that is inserted into the table dim_candidates) every so often, exceeds the 64.000 members (common problem to analysis). So the Logic that i have created is this.
Check for the profession of the candidate, and retrieve the requested Occupation_ID (that i need to insert into the table DIm_candidates). Perform a count for this profession from the table dim_candidate, to determine if the count for this profession is 64000 members. If it is, then go to table dim_occupation, insert a new id with the same profession description. Then, fetch me the new occupation_id, and insert this into table dim_candidate, for this candidate. All this code is inside the cursor, and you can read it below.
(from previous...)while @@fetch_status=0
begin ........
/* fetch me the occupation ID i need. If for the specified profession we have two occupation id's fetch me the bigger (latest) one
SET @OCCUPATION_ID= (
SELECT TOP 1
(DIMOC.OCCUPATION_ID)
FROM
DIM_OCCUPATION DIMOC
LEFT OUTER JOIN DIM_CANDIDATE DIMCA
ON DIMCA.OCCUPATION_ID=DIMOC.OCCUPATION_ID
WHERE
OCCUPATION_DESC LIKE @PROF_DESC
GROUP BY
DIMOC.OCCUPATION_DESC
, DIMOC.OCCUPATION_ID
ORDER BY
DIMOC.OCCUPATION_ID DESC)
/*for this occupation_id see how many members exist in the table */
SET @COUNTOCCUPATION = (
SELECT
COUNT(CANDIDATE_ID)
FROM
DIM_CANDIDATE
WHERE
@OCCUPATION_ID=OCCUPATION_ID
 
/*if there are 63999 members, then insert a new occupation_id with the same description in the table dim_occupation and fetch me the new occupation_id.*/
IF @COUNTOCCUPATION=63999
BEGIN
INSERT INTO DIM_OCCUPATION
(OCCUPATION_DESC)
VALUES
(@PROF_DESC)
SET @OCCUPATION_ID= (
SELECT TOP 1
(DIMOC.OCCUPATION_ID)
FROM
DIM_OCCUPATION DIMOC
LEFT OUTER JOIN DIM_CANDIDATE DIMCA
ON DIMCA.OCCUPATION_ID=DIMOC.OCCUPATION_ID
WHERE
OCCUPATION_DESC LIKE @PROF_DESC
GROUP BY
DIMOC.OCCUPATION_DESC
, DIMOC.OCCUPATION_ID
ORDER BY
DIMOC.OCCUPATION_ID DESC
 
END
My problem is this. Although the code works, every time the code is executed, it adds the first member it finds with the previous occupation_id, and all the others as the code instructs. Like it is outside the loop. In other words. Lets say that in the table dim_occupation, we have 2 occupation id's that have occupation description "other". These id's are id=8 and id=216. The id 216 was created by the above code because the id 8 "filled" 64000 member. The id 8 has 64000 members, and the id 216 has about 22000 members. Now each time (daily) the stored procedure is executed, the first candidate that has a profession "other", gets occupation id=8, while all the others get id=216 as they are supposed to do. This of course causes the analysis to fail processing. Where is the mistake in the code? why does it seem like for the first record, the code does not work?
Your help is appreciated,
DF
-
Hi,
I'm no expert at all when it comes to cursors, but there are a few things that you could try to change. Maybe some of it will help... if not, then maybe you could post the entire script, including the part that actually does the insert - to give us more info to think about.
- use MAX instead of TOP 1
- don't join to the DIM_CANDIDATE table at all when setting @occupation_id (both occasions); I don't see that the table would be used in any way here... so:
SET @OCCUPATION_ID= (
SELECT MAX (DIMOC.OCCUPATION_ID)
FROM
DIM_OCCUPATION DIMOC
WHERE
DIMOC.OCCUPATION_DESC LIKE @PROF_DESC)
- use > 63998 instead of = 63999
-
I'm not sure from your code what is going wrong. I would need to see the rest of the script, especially up to the point of the actual insert. But some of your code appears to be overkill since your already have the stuff you need from the cursor select. Try something similiar to the following (and if that doesn't work repost entire script).
while @@fetch_status=0
begin
SET @OCCUPATION_ID= (SELECT MAX (OCCUPATION_ID)
FROM DIM_OCCUPATION DIMOC
WHERE OCCUPATION_DESC LIKE @PROF_DESC)
SET @COUNTOCCUPATION = (SELECT COUNT(CANDIDATE_ID)
FROM DIM_CANDIDATE
WHERE <A href="mailto
CCUPATION_ID=@OCCUPATION_ID">OCCUPATION_ID=@OCCUPATION_ID)if @COUNTOCCUPATION >= 63999
begin
INSERT INTO DIM_OCCUPATION (OCCUPATION_DESC)
VALUES(@PROF_DESC)
@OCCUPATION_ID = @@identity --or repeat the "select max ... statement above if not using identity keys
end
--finally your actual insert into dim_candidate will occur here.
insert into .dim_candidate (occupation_id, ......)
values (@occupation_id,......)
end --while loop
--James.
-
A sincere thanks both for your answers. Sometimes you (I :crying
do stupid things. Indeed, i did not need the select top 1, but max instead. However, i particulalry liked the idea of the identity. I had never thought of it this wayINSERT INTO DIM_OCCUPATION (OCCUPATION_DESC)
VALUES(@PROF_DESC)
@OCCUPATION_ID = @@identity
The results of your suggestions seem right. I can not explain, but it seems that select max instead of selet top 1 made the difference. I executed the procedure and the members (of the min occupation id) did not increase by one.
I keep track of a couple of executions more and will notify you of the results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply