Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
String to long to fit - limit on aggregate functions ?
Message
 
To
21/07/2007 17:29:02
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
01242567
Message ID:
01242571
Views:
10
This message has been marked as a message which has helped to the initial question of the thread.
That works for me:
CREATE CURSOR DetailPolEndCovHazDiscountedPremiums (;
Pol_cKey I;
, Pol_cPolicyNo I ;
,Pol_cFileNo I ;
,Pol_cQuoteNum I ;
,Pol_dBound I ;
,Pol_nDues I ;
,Pol_nCommRate I ;
,Pol_nMaxRateMod I ;
,Pol_nDiscountMaxRateOverage I ;
,Pol_nPremium I ;
,Pol_nMinPrem I ;
,Pol_nPremiumTax I ;
,Pol_nPerilPrem I ;
,Pol_nMinCharge I ;
,Pol_nMinChgDef I ;
,Pol_nPolicyFee I ;
,Pol_nPolicyFeeTax I ;
,Pol_ntfDecimal I ;
,Pol_nMinTermFactor I ;
,End_cKey I;
,End_cQuoteNum I ;
,End_dQuoteDate I ;
,End_dBound I ;
,End_dDate I ;
,End_EffectiveDate I ;
,End_EffectiveTime I ;
,End_tEffectiveDateTime I ;
,End_ntfDecimal I ;
,End_mScript M ;
,Cov_cKey I;
,Coverage I ;
,Cov_nMaxRateMod I ;
,Cov_nDiscountMaxRateOverage I ;
,Cov_Discount I ;
,CovDisc_nIsPolicyDiscount I ;
,CovDisc_nDiscount I ;
,CovDisc_nRateModPer I ;
,CovDisc_nRateModAmt I ;
,Cov_TotCovPerPrem I ;
,Cov_PerMinChgDef I ;
,Cov_ExtraCostPerils M ;
,Cov_TotCovHazDis I ;
,Cov_HazardDiscountsApplied M ;
,Hazard M ;
,ChildHazard M ;
,PremiumB4Discounts I ;
,EffectivePremium I ;
)


SELECT Pol_cKey ;
,MAX(Pol_cPolicyNo) AS Pol_cPolicyNo ;
,MAX(Pol_cFileNo) AS Pol_cFileNo ;
,MAX(Pol_cQuoteNum) AS Pol_cQuoteNum ;
,MAX(Pol_dBound) AS Pol_dBound ;
,MAX(Pol_nDues) AS Pol_nDues ;
,MAX(Pol_nCommRate) AS Pol_nCommRate ;
,MAX(Pol_nMaxRateMod) AS Pol_nMaxRateMod ;
,MAX(Pol_nDiscountMaxRateOverage) AS Pol_nDiscountMaxRateOverage ;
,MAX(Pol_nPremium) AS Pol_nPremium ;
,MAX(Pol_nMinPrem) AS Pol_nMinPrem ;
,MAX(Pol_nPremiumTax) AS Pol_nPremiumTax ;
,MAX(Pol_nPerilPrem) AS Pol_nPerilPrem ;
,MAX(Pol_nMinCharge) AS Pol_nMinCharge ;
,MAX(Pol_nMinChgDef) AS Pol_nMinChgDef ;
,MAX(Pol_nPolicyFee) AS Pol_nPolicyFee ;
,MAX(Pol_nPolicyFeeTax) AS Pol_nPolicyFeeTax ;
,MAX(Pol_ntfDecimal) AS Pol_ntfDecimal ;
,MAX(Pol_nMinTermFactor) AS Pol_nMinTermFactor ;
,End_cKey ;
,MAX(End_cQuoteNum) AS End_cQuoteNum ;
,MAX(End_dQuoteDate) AS End_dQuoteDate ;
,MAX(End_dBound) AS End_dBound ;
,MAX(End_dDate) AS End_dDate ;
,MAX(End_EffectiveDate) AS End_EffectiveDate ;
,MAX(End_EffectiveTime) AS End_EffectiveTime ;
,MAX(End_tEffectiveDateTime) AS End_tEffectiveDateTime ;
,MAX(End_ntfDecimal) AS End_ntfDecimal ;
,CAST(MAX(ALLTRIM(End_mScript)) AS Memo) AS End_mScript ;
,Cov_cKey ;
,MAX(Coverage) AS Coverage ;
,MAX(Cov_nMaxRateMod) AS Cov_nMaxRateMod ;
,MAX(Cov_nDiscountMaxRateOverage) AS Cov_nDiscountMaxRateOverage ;
,MAX(Cov_Discount) AS Cov_Discount ;
,MAX(CovDisc_nIsPolicyDiscount) AS CovDisc_nIsPolicyDiscount ;
,MAX(CovDisc_nDiscount) AS CovDisc_nDiscount ;
,MAX(CovDisc_nRateModPer) AS CovDisc_nRateModPer ;
,MAX(CovDisc_nRateModAmt) AS CovDisc_nRateModAmt ;
,MAX(Cov_TotCovPerPrem) AS Cov_TotCovPerPrem ;
,MAX(Cov_PerMinChgDef) AS Cov_PerMinChgDef ;
,CAST(MAX(ALLTRIM(Cov_ExtraCostPerils)) AS Memo) AS Cov_ExtraCostPerils ;
,MAX(Cov_TotCovHazDis) AS Cov_TotCovHazDis ;
,CAST(MAX(ALLTRIM(Cov_HazardDiscountsApplied)) AS Memo) AS Cov_HazardDiscountsApplied ;
,CAST(NULL AS Memo) AS HazardList ;
,CAST(MAX(LEN(ALLTRIM(Hazard))) AS INTEGER) AS MaxHazardLen ;
,CAST(MAX(LEN(ALLTRIM(ChildHazard))) AS INTEGER) AS MaxChildHazardLen ;
,SUM(PremiumB4Discounts) AS TotalPremiumB4Discounts ;
,SUM(PremiumB4Discounts) - SUM(EffectivePremium) AS TotalHazardDiscounts ;
,SUM(EffectivePremium) AS TotalHazardPremium ;
INTO CURSOR PolEndCovSummary ;
FROM DetailPolEndCovHazDiscountedPremiums ;
GROUP BY Pol_cKey ;
        ,End_cKey ;
        ,Cov_cKey ;
ORDER BY 31 ;
READWRITE
I set all the fields where I can't guess their types to int :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform