just a syntax thing so I left out the meat and heres the bones

[Big1934]

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
OPEN Crs_DelosCatSkinner -- the servercentral did not include this,i think se equivoquO ->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

 

[bborissov]

/* Declaration of the CURSOR. You must put whole SELECT clause here /*
DECLARE Crs_DelosCatSkinner CURSOR
FOR
SELECT A.CertificateNrClean AS Policy_Number
       FROM dbo.vwApplication AS A

/* END OF DECLARATION. THAT'S IT NOTHING MORE NEEED */

--- Open the cursor
OPEN Crs_DelosCatSkinne

--- Declare variable where you get the result
DECLARE @CertificateNrCleanForPolicy_Number VARCHAR(14)

FETCH NEXT FROM Crs_DelosCatSkinner
      INTO @CertificateNrCleanForPolicy_Number

WHILE FETCH_STATUS = 0 
      BEGIN
         --- Body of the code
          FETCH NEXT FROM Crs_DelosCatSkinner
                INTO @CertificateNrCleanForPolicy_Number
      END

---- Close and release curtsor
CLOSE Crs_DelosCatSkinner
DEALLOCATE Crs_DelosCatSkinner

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

of course if you are doing an update, delete or insert with that data in the cursor, likely a cursor is the worng solution and should not be used at all as a set-based solution would perform better.

"NOTHING is more important in a database than integrity." ESquared

 

[Big1934]

1. Should my cursor bring back 1 result set per record? in the result grid there is a result set 1 (grid row) result set row per record?
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