error code when validating: System.DmlException: Insert failed

Question

I am having an issue where i have sufficient coverage but I'm failing validation.

Sidenote: line 33 starts with decimal baserate

trigger:

trigger onDependent on dependant__c (before insert, before update) {
onNewDependent.newRow(trigger.new);}

testcode:

@isTest(seealldata=true)
public class testOnDependent {

    @isTest
    public static void verifyChange()
    {

        dependant__c dep = new dependant__c(Name='sdkjflsdkfj',
                                            Age__c ='99',
                                            relationship__c = 'Dependent',
                                            Rating_Region__c='Region 1',
                                            age_rate__c = 1.000,
                                            Plan_Rate__c = 1.000,
                                            sequence_number__c = 1);
        if (dep!=null)
        {insert dep;        
        Test.startTest();
        dep.Age__c = '65';
        dep.relationship__c = 'Spouse';
        dep.Rating_Region__c='Region 1';
        dep.Plan_Rate__c = 1.000;
        update dep;       
        dep.Age__c = '21';
        dep.Rating_Region__c='Region 2';
        dep.relationship__c = 'Dependent';
        update dep; 
        dep.age_rate__c = 1.000;
        dep.Age__c = '22';
        update dep; 
        dep.Age__c = '23';
        update dep; 
        dep.Age__c = '24';
        update dep; 
        dep.Age__c = '25';
        update dep; 
        dep.Age__c = '26';
        update dep; 
        dep.Age__c = '27';
        update dep; 
        dep.Age__c = '28';
        update dep; 
        dep.Age__c = '29';
        update dep; 
        dep.Age__c = '30';
        update dep; 
        dep.Age__c = '31';
        update dep; 
        dep.Age__c = '32';
        update dep; 
        dep.Age__c = '33';
        update dep; 
        dep.Age__c = '34';
        update dep; 
        dep.Age__c = '35';
        update dep; 
        dep.Age__c = '36';
        update dep;
        dep.Age__c = '37';
        update dep; 
        dep.Age__c = '38';
        update dep; 
        dep.Age__c = '39';
        update dep; 
        dep.Age__c = '40';
        update dep; 
        dep.Age__c = '41';
        update dep; 
        dep.Age__c = '42';
        update dep; 
        dep.Age__c = '43';
        update dep; 
        dep.Age__c = '44';
        update dep; 
        dep.Age__c = '45';
        update dep; 
        dep.Age__c = '46';
        update dep; 
        dep.Age__c = '47';
        update dep; 
        dep.Age__c = '48';
        update dep; 
        dep.Age__c = '49';
        update dep; 
        dep.Age__c = '50';
        update dep; 
        dep.Age__c = '51';
        dep.Age__c = '52';
        update dep; 
        dep.Age__c = '53';
        update dep; 
        dep.Age__c = '54';
        update dep; 
        dep.Age__c = '55';
        update dep; 
        dep.Age__c = '56';
        update dep; 
        dep.Age__c = '57';
        update dep; 
        dep.Age__c = '58';
        update dep; 
        dep.Age__c = '59';
        update dep; 
        dep.Age__c = '60';
        update dep; 
        test.stopTest();
        }
    }
}

code:

public class onNewDependent {         
    static final Decimal fallbackRate = 3.000;
    static final map<string, Decimal> ageToRate = new map<String, Decimal>
    {
        '0'=>.635,'1'=>.635,'2'=>.635,'2'=>.635,'3'=>.635,'4'=>.635,'5'=>.635,'6'=>.635,'7'=>.635,'8'=>.635,
        '9'=>.635,'10'=>.635,'11'=>.635,'12'=>.635,'13'=>.635,'14'=>.635,'15'=>.635,'16'=>.635,'17'=>.635,
        '18'=>.635,'19'=>.635,'20'=>.635,'21'=>1.000,'22'=>1.000,'23'=>1.000,'24'=>1.000,'25'=>1.004,
        '26'=>1.024,'27'=>1.048,'28'=>1.087,'29'=>1.119,'30'=>1.135,'31'=>1.159,'32'=>1.183,'33'=>1.198,
        '34'=>1.214,'35'=>1.222,'36'=>1.23,'37'=>1.238,'38'=>1.246,'39'=>1.262,'40'=>1.278,'41'=>1.302,'42'=>1.325,
        '43'=>1.357,'44'=>1.397,'45'=>1.444,'46'=>1.5,'47'=>1.563,'48'=>1.635,'49'=>1.706,'50'=>1.786,'51'=>1.865,
        '52'=>1.952,'53'=>2.02,'54'=>2.135,'55'=>2.23,'56'=>2.333,'57'=>2.437,'58'=>2.548,'59'=>2.603,'60'=>2.714,
        '61'=>2.81,'62'=>2.873,'63'=>2.952,'64'=>3.000,'65'=>3.000
    };
    public static void newRow(dependant__c[] depdts)
    {

        Premium_Rate__c rate = new Premium_Rate__c();
        for (dependant__c dep :depdts)
        {

            if (ageToRate.containsKey(dep.Age__c)){dep.age_rate__c = ageToRate.get(dep.age__c);}
            else{dep.age_rate__c = fallbackRate;}

            if (String.isNotBlank(dep.rating_region__c))
            {
                if(dep.rating_region__c.contains('Region 1'))
                {dep.rating_region_number__c=1.000;}
                if(dep.rating_region__c.contains('Region 2')){dep.rating_region_number__c=1.009;}      
                if(dep.rating_region__c.contains('Region 3')){dep.rating_region_number__c=0.984;}  
                if(dep.rating_region__c.contains('Region 4')){dep.rating_region_number__c=1.033;}
            }

            decimal  baseRate = [SELECT Premium_Rate__c.Base_Rate__c FROM Premium_Rate__c WHERE Premium_Rate__c.id = :dep.Premium_Rate__c].Base_Rate__c;

            decimal ayy = [SELECT Premium_Rate__c.Plan_rate__c FROM Premium_Rate__c where Premium_Rate__c.id = :dep.Premium_Rate__c].Plan_rate__c;
            string aplus = String.valueOf(ayy);

            decimal byy = [SELECT Premium_Rate__c.Individual_Plan_Rate__c FROM Premium_Rate__c where Premium_Rate__c.id = :dep.Premium_Rate__c].Individual_Plan_Rate__c;

            if(string.isEmpty(aplus)){dep.Plan_Rate__c = byy;}
            else{dep.Plan_Rate__c = ayy;}

            if((dep.sequence_number__c<=3 && (dep.Age__c.equals('0')||dep.Age__c.equals('1')||dep.Age__c.equals('2')||dep.Age__c.equals('3')||dep.Age__c.equals('4')||dep.Age__c.equals('5')||dep.Age__c.equals('6')||dep.Age__c.equals('7')||dep.Age__c.equals('8')||dep.Age__c.equals('9')||dep.Age__c.equals('10')||dep.Age__c.equals('11')||dep.Age__c.equals('12')||dep.Age__c.equals('13')||dep.Age__c.equals('14')||dep.Age__c.equals('15')||dep.Age__c.equals('16')||dep.Age__c.equals('17')||dep.Age__c.equals('18')||dep.Age__c.equals('19')||dep.Age__c.equals('20')||dep.Age__c.equals('21'))&& dep.relationship__c.equals('Dependent'))||(dep.relationship__c.equals('Dependent')&& (dep.Age__c.equals('22')||dep.Age__c.equals('23')||dep.Age__c.equals('24')||dep.Age__c.equals('25')||dep.Age__c.equals('26')||dep.Age__c.equals('27')||dep.Age__c.equals('28')||dep.Age__c.equals('29')||dep.Age__c.equals('30')||dep.Age__c.equals('31')||dep.Age__c.equals('32')||dep.Age__c.equals('33')||dep.Age__c.equals('34')||dep.Age__c.equals('35')||dep.Age__c.equals('36')||dep.Age__c.equals('37')||dep.Age__c.equals('38')||dep.Age__c.equals('39')||dep.Age__c.equals('40')||dep.Age__c.equals('41')||dep.Age__c.equals('42')||dep.Age__c.equals('43')||dep.Age__c.equals('44')||dep.Age__c.equals('45')||dep.Age__c.equals('46')||dep.Age__c.equals('47')||dep.Age__c.equals('48')||dep.Age__c.equals('49')||dep.Age__c.equals('50')||dep.Age__c.equals('51')||dep.Age__c.equals('52')||dep.Age__c.equals('53')||dep.Age__c.equals('54')||dep.Age__c.equals('55')||dep.Age__c.equals('56')||dep.Age__c.equals('57')||dep.Age__c.equals('58')||dep.Age__c.equals('59')||dep.Age__c.equals('60')||dep.Age__c.equals('61')||dep.Age__c.equals('62')||dep.Age__c.equals('63')||dep.Age__c.equals('64')||dep.Age__c.equals('65')||dep.Age__c.equals('66')||dep.Age__c.equals('67')||dep.Age__c.equals('68')||dep.Age__c.equals('69')||dep.Age__c.equals('70')||dep.Age__c.equals('71')||dep.Age__c.equals('72')||dep.Age__c.equals('73')||dep.Age__c.equals('74')||dep.Age__c.equals('75')||dep.Age__c.equals('76')||dep.Age__c.equals('77')||dep.Age__c.equals('78')||dep.Age__c.equals('79')||dep.Age__c.equals('80')||dep.Age__c.equals('81')||dep.Age__c.equals('82')||dep.Age__c.equals('83')||dep.Age__c.equals('84')||dep.Age__c.equals('85')||dep.Age__c.equals('86')||dep.Age__c.equals('87')||dep.Age__c.equals('88')||dep.Age__c.equals('89')||dep.Age__c.equals('90')||dep.Age__c.equals('91')||dep.Age__c.equals('92')||dep.Age__c.equals('93')||dep.Age__c.equals('94')||dep.Age__c.equals('95')||dep.Age__c.equals('96')||dep.Age__c.equals('97')||dep.Age__c.equals('98')||dep.Age__c.equals('99')||dep.Age__c.equals('100')))||dep.relationship__c.equals('Spouse'))
            dep.final_rate__c = dep.Plan_rate__c*baseRate*dep.age_rate__c*dep.rating_region_number__c;            

        }
    }
}

Error code when validating:

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, onDependent: execution of BeforeInsert caused by: System.QueryException: List has no rows for assignment to SObject Class.onNewDependent.newRow: line 33, column 1 Trigger.onDependent: line 2, column 1: [] Stack Trace: Class.testOnDependent.verifyChange: line 30, column 1

Answer 1

If you work things backwards from the error, it's actually rather straightforward. The issue lies within your test setup.

@isTest(seeAllData=true) is highly discouraged, but that's not the immediate cause of the error you're seeing. You should always be creating the entirety of your test environment though.

System.QueryException: List has no rows for assignment to SObject Class.onNewDependent.newRow: line 33

points to the following line in your code (the class being tested)

decimal  baseRate = [SELECT Premium_Rate__c.Base_Rate__c FROM Premium_Rate__c WHERE Premium_Rate__c.id = :dep.Premium_Rate__c].Base_Rate__c;

now, going back to your test class, we see that you never set the Premium_Rate__c field on dependant__c dep

Since your dependant__c record doesn't have Premium_Rate__c set, your query on line 33 is effectively

decimal  baseRate = [SELECT Premium_Rate__c.Base_Rate__c
    FROM Premium_Rate__c 
    WHERE Premium_Rate__c.id = **null**].Base_Rate__c;

This returns an empty List<Premium_Rate__c>. Since the .Base_Rate__c at the end of the query needs an sObject to work on, and the query returned no rows, you get the System.QueryException: List has no rows for assignment to SObject error which you are seeing.

+edit:

It's also worth mentioning that your test class isn't actually testing anything, and looks like it is attempting to test too much. Generally speaking, it's better practice to break up your unit tests so they only test one or two things at a time. It's true that you're getting coverage, but covered != tested. To test your code, you need to make assertions.

The very fast and dirty explanation for what you need to have assertions for is this:

• Does the code I'm testing update a field on some sObject? If so, assert that the code actually did update that field
• Does the code I'm testing insert/update/delete some new sObject records? If so, assert that the new sObjects were actually inserted/updated/deleted

There's more to it than that, but that's probably a good starting point.

A quick example tailored to your test class would be:

if (dep!=null){
    insert dep;
    Test.startTest();
    dep.Age__c = '65';
    dep.relationship__c = 'Spouse';
    dep.Rating_Region__c='Region 1';
    dep.Plan_Rate__c = 1.000;
    update dep;

    // new assertion
    system.assertEquals(1.000, dep.rating_region_number__c, ' incorrect rating region number for "Region 1"');
    // end new assertion
    ...other lines omitted...
}

Answer 2

You should bulkify your code. In addition to not querying the same data three times within the loop, you should pull your queries out of your for loop altogether with something like:

Set<Id> rateIds = new Set<Id>();
for (Dependant__c record : dependants) rateIds.add(record.Premium_Rate__c);
Map<Id, Premium_Rate__c> rates = new Map<Id, Premium_Rate__c>([
    SELECT Base_Rate__c, Individual_Plan_Rate__c , Plan_rate__c
    FROM Premium_Rate__c WHERE Id IN :rateIds
]);
for (Dependant record : dependants)
{
    Premium_Rate__c rate = rates.get(record.Premium_Rate__c);
    if (rate != null)
    {
        // now you can get your Decimal values safely.
    }
}