July 23, 2009 at 9:21 am
More like this;
DECLARE Crs_Name CURSOR
FOR
SELECT
A.POLICYNr,
A.COV_I,
A.PREMIUM_I
A.COV_II,
A.PREMIUM_II,
A.PROGRAMCODE
FROM APLICATION AS A
FOR SELECT --<OPEN Crs_Name
DECLARE
@POLICYNr AS CHAR(12),
@COV_I AS MONEY,
@PREMIUM_I AS MONEY,
@COV_II as MONEY,
@PREMIUM_II AS MONEY,
@PROGRAMCODE AS CHAR(2) --EXAMPLES 01,02,03
FETCH NEXT FROM CRS_Name INTO
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE
WHILE FETCH_STATUS = 0 BEGIN
select
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE,
CASE
WHEN @PROGRAMCODE = '01'
THEN (@COV_I / @PREMIUM_I) * 100 AS RATE
ELSE (@COV_II / @PREMIUM_II) * 100 AS RATE
END;
FETCH NEXT FROM CRS_Name INTO
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE
END
CLOSE CRS_Name
DEALLOCATE CRS_Name
July 23, 2009 at 9:54 am
Great!! so that helps to answer something that i had to know too, i should put the whole select after the first Fecth not just the cases.
in other words put the cusor around the select which will turn into a make table qry when finally finished and compiled as an SP instead of
finding the CASES in the select and putting cursors around them
like(keep in mind until the help of u 'all I was cursor clueless aside from my TSQL book.)
i was doing something like this:
SELECT
'????' AS Program_ID,
A.CertificateNrClean AS Policy_Number,
CONVERT(varchar(10),A.TermBegin,101) AS Policy_Effective_Date,
A.CertificateNrClean AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
'' AS Deductable,
'' AS Covered_Cause_Of_Loss,
'80' AS [Co-Insurance_Factor],
Declare cursor............
CASE
WHEN A.ProgramCode = '01' AND A.CovII > 0 THEN (A.PremiumII / A.CovII) * 100
ELSE (A.PremiumI / A.CovI) * 100
END AS Rate
........Close cursor 😀
from table
where........
July 23, 2009 at 10:28 am
To be honest, being cursor clueless was probably better. Cursors are evil, even if there are some very rare cases where they are the correct solution for a problem.
Where you are now is not the best plae to be unless you can convince your boss that for the benefit of the systems and the users that persuing set-based solutions are the best way to go.
Sounds like this individual is stuck back in the old days using languages like COBOL with ISAM databases.
July 23, 2009 at 10:36 am
Also, this query will out perform the cursor solution over a large result set:
SELECT
A.POLICYNr,
A.COV_I,
A.PREMIUM_I
A.COV_II,
A.PREMIUM_II,
A.PROGRAMCODE,
case when A.PROGRAM_CODE = '01'
then A.COV_I / A.PREMIUM_I
else A.COV_II / A.PREMIUM_II
end * 100 as RATE
FROM
dbo.APLICATION AS A
July 23, 2009 at 10:38 am
Lynn, haha!! u r excatly write , he is old school and keeps wishing we could still use COBOL. u hit that on the mark! Once i get this I will do it set based and show him the difference. he does not seem stubborn, if i show him a way that will do what it needs to do AND better performance, he will be glad I showed him and allow me to skin the cat the best way possible next time. I hope anyway!
July 23, 2009 at 10:50 am
adam spencer (7/23/2009)
Lynn, haha!! u r excatly write , he is old school and keeps wishing we could still use COBOL. u hit that on the mark! Once i get this I will do it set based and show him the difference. he does not seem stubborn, if i show him a way that will do what it needs to do AND better performance, he will be glad I showed him and allow me to skin the cat the best way possible next time. I hope anyway!
Not hard to imagine. I am reformed COBOL programmer having helped support a COBOL application for over eleven years at a previous employer. Same employer whare I helped implement SQL Server.
July 23, 2009 at 12:44 pm
error Msg. 'Missing keyword near select' -I asume its the second select, tried with a semi colon after the from , i tried to use a table Alias and not to use one, still same message about 'Missing keyword near select'
DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT
--how to handle in cursor-- '????' AS Program_ID,
A.CertificateNrClean AS Policy_Number,
--how to handle in cursor-- CONVERT(varchar(10),A.TermBegin,101) AS Policy_Effective_Date,
A.CertificateNrClean AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
--how to handle in cursor--'' AS Deductable,
--how to handle in cursor--'' AS Covered_Cause_Of_Loss,
--how to handle in cursor--'80' AS [Co-Insurance_Factor],
A.ProgramCode, A.CovI, A.PremiumI, A.CovII, A.PremiumII, A.CovIV, A.CovOptOtherStructures, A.CovIII_LU,
A.CovOptLossOfUse,A.CovEPersonalLiability, A.CovOptPersonalLiability, A.CovFMedPay, A.CovOptMedPay,
A.CovReplacementCostStructures, A.CovOptReplacementCostStructures, A.CovReplacementCostContents,
A.CovOptReplacementCostContents, A.CovPersonalInjury, A.CovOptPersonalInjury, A.CovAnimalLiability,
A.CovOptAnimalLiability, A.CovOrdinanceLaw, A.CovOptOrdinanceLaw, A.CovOptWaterBackUp,
A.CovLossAssessment_NonHO6, A.CovOptLossAssessment, A.CovWaterDamage, A.CovOptWaterDamage
FROM APLICATION AS A
FOR SELECT --OPEN Crs_DelosCatSkinner
DECLARE
@CertificateNrCleanForPolicy_Number VARCHAR(14) ,
@CertificateNrCleanForRisk_ID_Location_ID VARCHAR(14),
@ProgramCode VARCHAR(2), @CovI MONEY, @PremiumI MONEY, @CovII MONEY, @PremiumII MONEY, @CovIV MONEY, @CovOptOtherStructures MONEY, @CovIII_LU MONEY,
@CovOptLossOfUse MONEY,@CovEPersonalLiability MONEY, @CovOptPersonalLiability MONEY, @CovFMedPay MONEY, @CovOptMedPay MONEY,
@CovReplacementCostStructures INT, @CovOptReplacementCostStructures MONEY, @CovReplacementCostContents BIT,
@CovOptReplacementCostContents MONEY, @CovPersonalInjury BIT, @CovOptPersonalInjury MONEY, @CovAnimalLiability INT,
@CovOptAnimalLiability MONEY, @CovOrdinanceLaw DECIMAL(9,6), @CovOptOrdinanceLaw MONEY,@CovWaterBackUp INT,@CovOptWaterBackUp MONEY,
@CovLossAssessment_NonHO6 MONEY, @CovOptLossAssessment MONEY, @CovWaterDamage MONEY, @CovOptWaterDamage MONEY
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse,@CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
WHILE FETCH_STATUS = 0 BEGIN
SELECT--CT
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse, @CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
FROM vwApplication AS A
July 23, 2009 at 1:01 pm
adam spencer (7/23/2009)
error Msg. 'Missing keyword near select' -I asume its the second select, tried with a semi colon after the from , i tried to use a table Alias and not to use one, still same message about 'Missing keyword near select'DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT
--how to handle in cursor-- '????' AS Program_ID,
A.CertificateNrClean AS Policy_Number,
--how to handle in cursor-- CONVERT(varchar(10),A.TermBegin,101) AS Policy_Effective_Date,
A.CertificateNrClean AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
--how to handle in cursor--'' AS Deductable,
--how to handle in cursor--'' AS Covered_Cause_Of_Loss,
--how to handle in cursor--'80' AS [Co-Insurance_Factor],
A.ProgramCode, A.CovI, A.PremiumI, A.CovII, A.PremiumII, A.CovIV, A.CovOptOtherStructures, A.CovIII_LU,
A.CovOptLossOfUse,A.CovEPersonalLiability, A.CovOptPersonalLiability, A.CovFMedPay, A.CovOptMedPay,
A.CovReplacementCostStructures, A.CovOptReplacementCostStructures, A.CovReplacementCostContents,
A.CovOptReplacementCostContents, A.CovPersonalInjury, A.CovOptPersonalInjury, A.CovAnimalLiability,
A.CovOptAnimalLiability, A.CovOrdinanceLaw, A.CovOptOrdinanceLaw, A.CovOptWaterBackUp,
A.CovLossAssessment_NonHO6, A.CovOptLossAssessment, A.CovWaterDamage, A.CovOptWaterDamage
FROM APLICATION AS A
FOR SELECT --OPEN Crs_DelosCatSkinner
DECLARE
@CertificateNrCleanForPolicy_Number VARCHAR(14) ,
@CertificateNrCleanForRisk_ID_Location_ID VARCHAR(14),
@ProgramCode VARCHAR(2), @CovI MONEY, @PremiumI MONEY, @CovII MONEY, @PremiumII MONEY, @CovIV MONEY, @CovOptOtherStructures MONEY, @CovIII_LU MONEY,
@CovOptLossOfUse MONEY,@CovEPersonalLiability MONEY, @CovOptPersonalLiability MONEY, @CovFMedPay MONEY, @CovOptMedPay MONEY,
@CovReplacementCostStructures INT, @CovOptReplacementCostStructures MONEY, @CovReplacementCostContents BIT,
@CovOptReplacementCostContents MONEY, @CovPersonalInjury BIT, @CovOptPersonalInjury MONEY, @CovAnimalLiability INT,
@CovOptAnimalLiability MONEY, @CovOrdinanceLaw DECIMAL(9,6), @CovOptOrdinanceLaw MONEY,@CovWaterBackUp INT,@CovOptWaterBackUp MONEY,
@CovLossAssessment_NonHO6 MONEY, @CovOptLossAssessment MONEY, @CovWaterDamage MONEY, @CovOptWaterDamage MONEY
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse,@CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
WHILE FETCH_STATUS = 0 BEGIN
SELECT--CT
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse, @CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
FROM vwApplication AS A
WHILE FETCH_STATUS = 0 BEGIN
SELECT--CT
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse, @CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
FROM vwApplication AS A <<-- why is this here?? where is the FETCH NEXT and END to the WHILE?
July 23, 2009 at 1:27 pm
sorry , it was there I just did not past it.
I looks like this
DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT
--how to handle in cursor-- '????' AS Program_ID,
A.CertificateNrClean AS Policy_Number,
--how to handle in cursor-- CONVERT(varchar(10),A.TermBegin,101) AS Policy_Effective_Date,
A.CertificateNrClean AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
--how to handle in cursor--'' AS Deductable,
--how to handle in cursor--'' AS Covered_Cause_Of_Loss,
--how to handle in cursor--'80' AS [Co-Insurance_Factor],
A.ProgramCode, A.CovI, A.PremiumI, A.CovII, A.PremiumII, A.CovIV, A.CovOptOtherStructures, A.CovIII_LU,
A.CovOptLossOfUse,A.CovEPersonalLiability, A.CovOptPersonalLiability, A.CovFMedPay, A.CovOptMedPay,
A.CovReplacementCostStructures, A.CovOptReplacementCostStructures, A.CovReplacementCostContents,
A.CovOptReplacementCostContents, A.CovPersonalInjury, A.CovOptPersonalInjury, A.CovAnimalLiability,
A.CovOptAnimalLiability, A.CovOrdinanceLaw, A.CovOptOrdinanceLaw, A.CovOptWaterBackUp,
A.CovLossAssessment_NonHO6, A.CovOptLossAssessment, A.CovWaterDamage, A.CovOptWaterDamage
FROM dbo.vwApplication AS A
FOR SELECT --OPEN Crs_DelosCatSkinner
DECLARE
@CertificateNrCleanForPolicy_Number VARCHAR(14) ,
@CertificateNrCleanForRisk_ID_Location_ID VARCHAR(14),
@ProgramCode VARCHAR(2), @CovI MONEY, @PremiumI MONEY, @CovII MONEY, @PremiumII MONEY, @CovIV MONEY, @CovOptOtherStructures MONEY, @CovIII_LU MONEY,
@CovOptLossOfUse MONEY,@CovEPersonalLiability MONEY, @CovOptPersonalLiability MONEY, @CovFMedPay MONEY, @CovOptMedPay MONEY,
@CovReplacementCostStructures INT, @CovOptReplacementCostStructures MONEY, @CovReplacementCostContents BIT,
@CovOptReplacementCostContents MONEY, @CovPersonalInjury BIT, @CovOptPersonalInjury MONEY, @CovAnimalLiability INT,
@CovOptAnimalLiability MONEY, @CovOrdinanceLaw DECIMAL(9,6), @CovOptOrdinanceLaw MONEY,@CovWaterBackUp INT,@CovOptWaterBackUp MONEY,
@CovLossAssessment_NonHO6 MONEY, @CovOptLossAssessment MONEY, @CovWaterDamage MONEY, @CovOptWaterDamage MONEY
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse,@CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
WHILE FETCH_STATUS = 0 BEGIN
SELECT--CT
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse, @CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
SELECT A.CertificateNrClean
FROM dbo.vwApplication AS A
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number,
@CertificateNrCleanForRisk_ID_Location_ID,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse,@CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
END
CLOSE Crs_DelosCatSkinner
DEALLOCATE Crs_DelosCatSkinner
July 23, 2009 at 1:45 pm
just a syntax thing so i left out the meat and just left the bones:
DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT
--how to handle in cursor-- '????' AS Program_ID,
A.CertificateNrClean AS Policy_Number
FROM dbo.vwApplication AS A
FOR SELECT --OPEN Crs_DelosCatSkinner
DECLARE
@CertificateNrCleanForPolicy_Number VARCHAR(14)
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number
WHILE FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@CertificateNrCleanForPolicy_Number
END
CLOSE Crs_DelosCatSkinner
DEALLOCATE Crs_DelosCatSkinner
July 23, 2009 at 2:08 pm
FOR UPDATE --Problem was that even though i am just selecting,needed update there for some reason before the
OPEN Crs_DelosCatSkinner line.
it is counter intuitive if i am using the corsor to select but it works now.
if i am wrong tell me please.
July 23, 2009 at 2:37 pm
I don't write cursors, so I can only help you so much. I have never written a cursor that was used to update data through the cursor, so that is something I can't help you with.
July 24, 2009 at 2:40 am
hi,
You have to specify which column should modify with the cursor declaration.
/***Try this code for declaring the cursor**/
Declare crs_CoveragePremiums Cursor For
Select CP.CovID,
CP.Location,
CP.BaseRate,
A.Full_Address
From dbo.tblCoveragePremiums AS CP
Left Join dbo.tblAddress AS A
On CP.CovID = A.CovID
where CP.Location Not Like '_u%'
Order By CP.PolicyBeginDate , A.Full_Address
For Update CP.BaseRate /*this line Added */
July 27, 2009 at 5:39 am
1. Should my cursor bring back 1 result set per record? is that what it should be doing.attatched is a pick of reuslts
2. i am getting a divide by 0 error encounter, in any of my cases u can see how i am trying to avoid ,is it isnt working its like ...AND column2 > 0
---------my cursor code -SOrry its long
--works but need to figure how the Policy effective date needs to be syntaxed. if not sure then remove all
--occurance with -- and or use the bak
DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT
'????' AS Program_ID,
A.CertificateNrClean AS Policy_Number,
CONVERT(varchar(10),A.TermBegin,101) AS Policy_Effective_Date,
A.CertificateNrClean AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
'' AS Deductable,
'' AS Covered_Cause_Of_Loss,
'80' AS [Co-Insurance_Factor],
0 AS RateCovCode1, 0 AS RateCovCode2, 0 AS RateCovCode3, 0 AS RateCovCode4, 0 AS RateCovCode5,
0 AS RateCovCode6, 0 AS RateCovCode7, 0 AS RateCovCode8, 0 AS RateCovCode9, 0 AS RateCovCode12,
0 AS RateCovCode13, 0 AS RateCovCode14, 0 AS RateCovCode15, 0 AS RateCovCode19, 0 AS RateCovCode20,
A.ProgramCode, A.CovI, A.PremiumI, A.CovII, A.PremiumII, A.CovIV, A.CovOptOtherStructures, A.CovIII_LU,
A.CovOptLossOfUse,A.CovEPersonalLiability, A.CovOptPersonalLiability, A.CovFMedPay, A.CovOptMedPay,
A.CovReplacementCostStructures, A.CovOptReplacementCostStructures, A.CovReplacementCostContents,
A.CovOptReplacementCostContents, A.CovPersonalInjury, A.CovOptPersonalInjury, A.CovAnimalLiability,
A.CovOptAnimalLiability, A.CovOrdinanceLaw, A.CovOptOrdinanceLaw, A.CovOptWaterBackUp,
A.CovLossAssessment_NonHO6, A.CovOptLossAssessment, A.CovWaterDamage, A.CovOptWaterDamage
FROM dbo.vwApplication AS A
--FOR UPDATE --Problem was that even though i am just selecting,needed update there for some reason
--works with noth so i think my syntax choices where FOR UPDATE or do not specfy anything
--and will default to SELECT
OPEN Crs_DelosCatSkinner
DECLARE
@Program_ID AS CHAR(4),
@CertificateNrCleanForPolicy_Number VARCHAR(14),
@PolicyEffectiveDate VARCHAR(10),
@CertificateNrCleanForRisk_ID_Location_ID VARCHAR(14),
--AS CoveCode,
--AS Limit,
@Deductable MONEY, @Covered_Cause_Of_Loss VARCHAR(max),
@Co_Insurance_Factor VARCHAR(max),
@RateCovCode1 MONEY, @RateCovCode2 MONEY, @RateCovCode3 MONEY,@RateCovCode4 MONEY, @RateCovCode5 MONEY,
@RateCovCode6 MONEY, @RateCovCode7 MONEY, @RateCovCode8 MONEY, @RateCovCode9 MONEY,
@RateCovCode12 MONEY, @RateCovCode13 MONEY, @RateCovCode14 MONEY, @RateCovCode15 MONEY,
@RateCovCode19 MONEY, @RateCovCode20 MONEY,
@ProgramCode VARCHAR(2), @CovI MONEY, @PremiumI MONEY, @CovII MONEY, @PremiumII MONEY, @CovIV MONEY, @CovOptOtherStructures MONEY, @CovIII_LU MONEY,
@CovOptLossOfUse MONEY,@CovEPersonalLiability MONEY, @CovOptPersonalLiability MONEY, @CovFMedPay MONEY, @CovOptMedPay MONEY,
@CovReplacementCostStructures INT, @CovOptReplacementCostStructures MONEY, @CovReplacementCostContents BIT,
@CovOptReplacementCostContents MONEY, @CovPersonalInjury BIT, @CovOptPersonalInjury MONEY, @CovAnimalLiability INT,
@CovOptAnimalLiability MONEY, @CovOrdinanceLaw DECIMAL(9,6), @CovOptOrdinanceLaw MONEY,@CovWaterBackUp INT,@CovOptWaterBackUp MONEY,
@CovLossAssessment_NonHO6 MONEY, @CovOptLossAssessment MONEY, @CovWaterDamage MONEY, @CovOptWaterDamage MONEY
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@Program_ID,
@CertificateNrCleanForPolicy_Number,
@PolicyEffectiveDate,
@CertificateNrCleanForRisk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
@Deductable, @Covered_Cause_Of_Loss,
@Co_Insurance_Factor,
@RateCovCode1, @RateCovCode2, @RateCovCode3, @RateCovCode4, @RateCovCode5,
@RateCovCode6, @RateCovCode7, @RateCovCode8, @RateCovCode9,
@RateCovCode12, @RateCovCode13, @RateCovCode14, @RateCovCode15, @RateCovCode19, @RateCovCode20,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse, @CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Program_ID AS Program_ID,
@CertificateNrCleanForPolicy_Number AS Policy_Number,
@PolicyEffectiveDate AS Policy_Effective_Date,
@CertificateNrCleanForRisk_ID_Location_ID AS Risk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
@Deductable AS Deductable, @Covered_Cause_Of_Loss AS Covered_Cause_Of_Loss,@Co_Insurance_Factor AS [Co-Insurance_Factor],
-----------------------------------------------------------
CASE WHEN @ProgramCode = '01' AND @CovII > 0 AND @CovI > 0 AND @PremiumII > 0 AND @PremiumI > 0
THEN (@CovII / @PremiumII) * 100
ELSE (@CovI / @PremiumI) * 100
END AS RateCovCode1,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0
END AS RateCovCode2,
--CASE WHEN @ProgramCode = '01' AND @CovII > 0 AND @CovI > 0 AND @PremiumII > 0 AND @PremiumI > 0
--THEN (@CovI / @PremiumI) * 100
--ELSE (@CovII / @PremiumII) * 100
-- END AS RateCovCode3,
CASE WHEN @CovIII_LU > 0 AND @CovOptLossOfUse > 0
THEN (@CovIII_LU / @CovOptLossOfUse) * 100
ELSE 0
END AS RateCovCode4,
/*
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0
END AS RateCovCode5,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0
END AS RateCovCode5,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode6,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode7,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode8,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode9,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode12,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode13,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode8,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode9,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode12,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode13,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode8,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode9,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode12,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode13,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode14,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode15,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode19,
CASE WHEN @ProgramCode In ('02','03') AND @CovIV > 0 AND @CovOptOtherStructures > 0
THEN (@CovIV / @CovOptOtherStructures ) * 100
ELSE 0m
END AS RateCovCode20,
*/
-----------------------------------------------------------
@ProgramCode AS ProgramCode,@CovI AS CovI, @PremiumI AS PremiumI, @CovII AS CovII, @PremiumII AS PremiumII, @CovIV AS CovIV, @CovOptOtherStructures AS CovOptOtherStructure,@CovIII_LU AS CovIII_LU, @CovOptLossOfUse AS CovOptLossOfUse, @CovEPersonalLiability AS CovEPersonalLiability, @CovOptPersonalLiability ASCovOptPersonalLiability, @CovFMedPay AS CovFMedPay, @CovOptMedPay AS CovOptMedPay, @CovReplacementCostStructures AS CovReplacementCostStructures, @CovOptReplacementCostStructures AS CovOptReplacementCostStructures, @CovReplacementCostContents AS CovReplacementCostContents, @CovOptReplacementCostContents AS CovOptReplacementCostContents, @CovPersonalInjury AS CovPersonalInjury,@CovOptPersonalInjury AS CovOptPersonalInjury, @CovAnimalLiability AS CovAnimalLiability, @CovOptAnimalLiability AS CovOptAnimalLiability, @CovOrdinanceLaw AS CovOrdinanceLaw, @CovOptOrdinanceLaw AS CovOptOrdinanceLaw, @CovOptWaterBackUp AS CovOptWaterBackUp, @CovLossAssessment_NonHO6 AS CovLossAssessment_NonHO6, @CovOptLossAssessment AS CovOptLossAssessment, @CovWaterDamage AS CovWaterDamage, @CovOptWaterDamage AS CovOptWaterDamage
-----------------below done by FC (Forum Contributer-------------------
/*
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE
WHILE FETCH_STATUS = 0 BEGIN
select
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE,
CASE
WHEN @PROGRAMCODE = '01'AND A.CovII > 0
THEN A.PremiumII / A.CovII) * 100 AS Rate
ELSE (A.PremiumI / A.CovI) * 100 AS Rate
END;
*/
-----------------above done by FC-------------------------------------------------------------
FETCH NEXT FROM Crs_DelosCatSkinner INTO
@Program_ID,
@CertificateNrCleanForPolicy_Number,
@PolicyEffectiveDate,
@CertificateNrCleanForRisk_ID_Location_ID,
--AS CoveCode,
--AS Limit,
@Deductable, @Covered_Cause_Of_Loss,
@Co_Insurance_Factor,
@RateCovCode1, @RateCovCode2, @RateCovCode3, @RateCovCode4, @RateCovCode5,
@RateCovCode6, @RateCovCode7, @RateCovCode8, @RateCovCode9,
@RateCovCode12, @RateCovCode13, @RateCovCode14, @RateCovCode15, @RateCovCode19, @RateCovCode20,
@ProgramCode, @CovI, @PremiumI, @CovII, @PremiumII, @CovIV, @CovOptOtherStructures, @CovIII_LU,
@CovOptLossOfUse,@CovEPersonalLiability, @CovOptPersonalLiability, @CovFMedPay, @CovOptMedPay,
@CovReplacementCostStructures, @CovOptReplacementCostStructures, @CovReplacementCostContents,
@CovOptReplacementCostContents, @CovPersonalInjury, @CovOptPersonalInjury, @CovAnimalLiability,
@CovOptAnimalLiability, @CovOrdinanceLaw, @CovOptOrdinanceLaw, @CovOptWaterBackUp,
@CovLossAssessment_NonHO6, @CovOptLossAssessment, @CovWaterDamage, @CovOptWaterDamage
END
CLOSE Crs_DelosCatSkinner
DEALLOCATE Crs_DelosCatSkinner
July 27, 2009 at 6:38 am
Here is part of your problem, your code:
CASE
WHEN @ProgramCode = '01' AND
@CovII > 0 AND
@CovI > 0 AND
@PremiumII > 0 AND
@PremiumI > 0
THEN (@CovII / @PremiumII) * 100 -- If all the above is true, this is executed
ELSE (@CovI / @PremiumI) * 100 -- else this is executed (If @PremiumI is <= 0 for example)
END AS RateCovCode1,
Does this help point you in what needs to be fixed? Big suggestion, use BOL (Books Online, the SQL Server Help System, you can access it by pressing the {f1} function while in SSMS).
Look up the CASE statement. The samples may be for set based queries, but it should help you correct your problems here.
Also, remember when you are working with cursors, you are working with one row at a time. The "result set" of the query that defines the cursor should, if run on its own, return multiple rows, but you are stepping throgh that result set one row at a time. This is what makes cursors slow.
If you would like help rewriting one of your cursor-based procedures into a set-based solution, start a new thread, and provide the DDL for the table(s), sample data for the table(s), expected results, and the current code. For help on this, read the first article I reference below in my signature block regarding asking for assistance and follow the guidelines presented.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply