Jun 15, 2000
I can not get this code to work, I dey try to convert a non relational database, catcde na the name of the field in the non conversional database and i have a table in the relational database that called DuesCategory, that it's DuesCategoryname has the following fields,
Retired
On Leave
No Dues
Agency Fee Payer
Student
Full Dues
3/4 Dues
1/2 Dues
1/4 Dues
1/8 Dues
the fields in the non relational are not d same that is why i am trying to map it using the case statements, and since i will be converting alot of databases i what it to be based on which peremeter they put in.
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
This is all the code
CREATE Procedure _RunIndividual_5 @tablename varchar(100) as
Exec ('declare cur_individual cursor for
SELECT last, first, mi, address1, address2, city, state, zip,
bdate, sex, soc, mstat, depnum, hometel, status, sefdte, catcde,
cefdate, poscd, poedte, rfdlt, duespthru, paydeduc, classcode,
location, deptcode, tenuresub, ocertsub, bassalary, salarystep,
salarycol, startdate, initdate, seniordate, worktel, votecope, votedate,
voteded, polparty, polactiv, precinct, condst, sendst, asmdst, rschd,
loccode, extra1, extra2, extra3, extra4, extra5, extra6, extra7,
extra8, extra9, extra10, access, pctype, os, pclocation, internet,
email, show, county, localname, localnum, bargin, offtitle2, offtitle,
regvoter, reshigh, reselem, resunit, rescollege, employer, empnum, workfax,
offtitle3, work_phone, const_code, class_titl, faxnumber, constit
FROM Conversion.dbo.'+ @tablename+' order by last,first')
----Declare variables
declare @last nvarchar (50) ,
@first nvarchar (50) ,
@mi nvarchar (50) ,
@address1 nvarchar (50) ,
@address2 nvarchar (50) ,
@city nvarchar (50) ,
@state nvarchar (50) ,
@zip nvarchar (255) ,
@bdate nvarchar (255) ,
@sex nvarchar (255) ,
@soc nvarchar (255) ,
@mstat nvarchar (255) ,
@depnum float ,
@hometel nvarchar (255) ,
@status nvarchar (255) ,
@sefdte nvarchar (255) ,
@catcde nvarchar (255) ,
@cefdate nvarchar (255) ,
@poscd nvarchar (50) ,
@poedte nvarchar (255) ,
@rfdlt nvarchar (255) ,
@duespthru nvarchar (255) ,
@paydeduc nvarchar (255) ,
@classcode nvarchar (50) ,
@location nvarchar (255) ,
@deptcode nvarchar (255) ,
@tenuresub nvarchar (255) ,
@ocertsub nvarchar (255) ,
@bassalary float ,
@salarystep float ,
@salarycol nvarchar (255) ,
@startdate nvarchar (255) ,
@initdate nvarchar (255) ,
@seniordate nvarchar (255) ,
@worktel nvarchar (255) ,
@votecope float ,
@votedate nvarchar (255) ,
@voteded nvarchar (255) ,
@polparty nvarchar (255) ,
@polactiv nvarchar (255) ,
@precinct nvarchar (255) ,
@condst nvarchar (255) ,
@sendst nvarchar (255) ,
@asmdst nvarchar (255) ,
@rschd nvarchar (255) ,
@loccode int ,
@extra1 nvarchar (255) ,
@extra2 nvarchar (255) ,
@extra3 nvarchar (255) ,
@extra4 nvarchar (255) ,
@extra5 nvarchar (255) ,
@extra6 nvarchar (255) ,
@extra7 nvarchar (255) ,
@extra8 nvarchar (255) ,
@extra9 nvarchar (255) ,
@extra10 nvarchar (255) ,
@access bit ,
@pctype nvarchar (255) ,
@os nvarchar (255) ,
@pclocation nvarchar (255) ,
@internet nvarchar (255) ,
@email nvarchar (255) ,
@show bit ,
@county nvarchar (255) ,
@localname nvarchar (255) ,
@localnum nvarchar (255) ,
@bargin nvarchar (255) ,
@offtitle2 nvarchar (255) ,
@offtitle nvarchar (255) ,
@regvoter nvarchar (255) ,
@reshigh nvarchar (255) ,
@reselem nvarchar (255) ,
@resunit nvarchar (255) ,
@rescollege nvarchar (255) ,
@employer nvarchar (255) ,
@empnum nvarchar (255) ,
@workfax nvarchar (255) ,
@offtitle3 nvarchar (255) ,
@work_phone nvarchar (255) ,
@const_code nvarchar (255) ,
@class_titl nvarchar (255) ,
@faxnumber nvarchar (255) ,
@constit nvarchar (255) ,
@DuesCategoryName nvarchar (255) ,
@DuesCategoryName1 nvarchar (255) ,
@DuesCategoryName2 nvarchar (255) ,
@DuesCategoryName3 nvarchar(255) ,
@DuesCategoryName4 nvarchar(255) ,
@DuesCategoryName5 nvarchar(255) ,
@DuesCategoryName6 nvarchar(255) ,
@DuesCategoryName7 nvarchar(255) ,
@DuesCategoryName8 nvarchar(255) ,
@DuesCategoryName9 nvarchar(255) ,
@DuesCategoryName10 nvarchar(255) ,
@DuesCategoryName11 nvarchar(255) ,
--- other variables
@prefix varchar(5),@prefixid uniqueidentifier,@worksiteid uniqueidentifier,
@chapterid uniqueidentifier,@PaymentMethodName varchar(50),@PaymentMethodID uniqueidentifier,
@DuesCategoryID uniqueidentifier,@DuesCategoryLocalID uniqueidentifier,
@DeactivateReasonID uniqueidentifier,@DeactivateReasonLocalID uniqueidentifier,
@JobClassID uniqueidentifier,@LocalJobClassID uniqueidentifier,
@MaritalStatusID uniqueidentifier,@MemberStatusID uniqueidentifier,
@SubjectID uniqueidentifier,@PoliticalPartyID uniqueidentifier,
@EmployerID uniqueidentifier, @LocalUnionID uniqueidentifier,@LocalUnionNbr char(5),
@addressID uniqueidentifier,@StateTerritoryId uniqueidentifier,
@CountryId uniqueidentifier,@PoliticallyActiveTF bit,@IndividualId uniqueidentifier
begin
open cur_individual
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @prefix= case @sex when 'Male' then 'Mr.'
when 'Female' then 'Ms.'
when ' ' then null
end
select @prefixid=prefixid from prefix where prefixname=@prefix
select @worksiteid=worksiteid,@EmployerID=EmployerID from worksite where worksitenumber=@loccode
select @chapterid=chapterid from chapter where localunionid in (select localunionid from localunion)
select @PaymentMethodName= case @paydeduc when 'YES' then 'Payroll Deduction'
end
Select @PaymentMethodID=PaymentMethodID from PaymentMethod where PaymentMethodName=@PaymentMethodName
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
select @DeactivateReasonID=DeactivateReasonID,@Deactivate ReasonLocalID= DeactivateReasonLocalID
from DeactivateReasonLocal
where DeactivateReasonLocalName=@rfdlt
select @MaritalStatusID=MaritalStatusID from MaritalStatus where MaritalStatusName=@mstat
select @MemberStatusID=MemberStatusID from MemberStatus where MemberStatusName=@Status
select @SubjectID=SubjectID from Subject where SubjectName=@tenuresub
select @PoliticalPartyID=PoliticalPartyID from PoliticalParty where PoliticalPartyName=@polparty
select @LocalUnionID=LocalUnionID,@LocalUnionNbr=LocalUni onNbr from Localunion
select @StateTerritoryId=StateTerritoryId,@CountryId=Coun tryId from Address where StateCode=@State
Insert into Address values (newid(),@Address1,@Address2,@City,null,@State,@zi p,
null,null,'USA',@StateTerritoryId,@CountryId,NULL, 0,@county,NULL,0,default,default,
default,default,default,@LocalUnionNbr,@LocalUnion Id)
Select @AddressId=AddressId from Address where uid=@@Identity
select @LocalJobClassId=LocalJobClassId from LocalJobClass where LocalJobClassCode=@classcode
---- Inserting Individual rows
insert into individual values (
newid(), @soc, null, @last,@First, @mi,null,CONVERT(DATETIME,@bdate),@PrefixId,@sex,
null, 1, @MaritalStatusId,null,null,0,null,null,null, null,@WorksiteId,null,
@JobClassId,@LocalJobClassId, CONVERT(DATETIME,@STARTDATE), null, null, CONVERT(DATETIME,@seniordate), null, @location,@deptcode,
null,null, 0, @condst, @regvoter, null, @sendst, @rschd,@asmdst, @PolActiv,@PoliticalPartyId,
@precinct,null, null, 0, @SubjectId, null,default, 0,null, @Hometel,null, @Worktel,null,
@MemberStatusId, null,null, @Email,null,@ChapterId,@DuesCategoryId,
null, @AddressId, CONVERT(DATETIME,@CEFDATE),0 , NULL,@PaymentMethodId,@DUESPTHRU,
NULL,CONVERT(DATETIME,@InitDate), @DuesCategoryLocalId,null,@DeactivateReasonId,
null, null, @LocalUnionNbr, null, null, @LocalUnionId, null, null, 0, null,
null, @WorkFax, null, null, null, null, @DeactivateReasonLocalId, null,
null, null, null, null,null,null,null,null,null,null,null,null,null, null,
null,null,null,null, null,null,null,null,null,null,default,default,defa ult,default,
default,@EmployerId, null, null, null,null,null,null,null,null,default,default,
default,default,default,default,default,default)
select @IndividualId=IndividualId from Individual where LastName=@Last and FirstName=@first and
MiddleName=@mi and ssn=@soc and dob=@bdate and Gender=@sex
insert into COPE values (newid(),null,@IndividualId,null,@votecope,convert (datetime,@votedate),
default,default,default,default,default,@LocalUnio nNbr,@LocalUnionId)
Select @prefixId=null
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
END
CLOSE CUR_individual
DEALLOCATE CUR_individual
END
update individual set homeaddressid=addressid where addressid is not null -- set default address as home
Thanks
View 2 Replies
View Related
