locked
Functions Producing Inconsistent Results RRS feed

  • Question

  • Hi!  I'd like to ask for some help if anyone is willing to offer input...

    I have a table valued function that is intended to produce the final output that is essentially a series of scalar function calls for different parameters.  I had to remove the results that were correct due to HIPAA and I like my job so don't focus on the fact that values are missing.  One set of function calls (I put a red box around the one I'm trying to prove) is not producing accurate results.  I'm including the definition for each of the function definitions along with some queries calling each function for a specific case.

    The table function call produces null values for the case in question, as does the scalar function for that particular case.  But if I run the SQL for that case as SQL (not part of a function), it produces accurate results.  Could anyone offer input on what I'm doing wrong?  Been looking at this all afternoon.  I keep coming back to it's probably a syntax thing but the SQL executes correctly without anything more than just substituting in values in place of variables.  I'm kind of stumped...any help is most appreciated!

    Tuesday, December 12, 2017 1:55 AM

Answers

  • I know what the entire function returns, the odds are, it's because the voided transactions are returning null (since you don't have any), what would @ProviderPay - NULL return?
    Tuesday, December 12, 2017 2:57 AM

All replies

  • What is your "Voided Transactions" query returning?
    Tuesday, December 12, 2017 2:25 AM
  • The entire function returns Null.

    For this particular case there are no voided transactions so I didn't test.

    Tuesday, December 12, 2017 2:35 AM
  • I know what the entire function returns, the odds are, it's because the voided transactions are returning null (since you don't have any), what would @ProviderPay - NULL return?
    Tuesday, December 12, 2017 2:57 AM
  • Good morning!

    Added code to check for null values for @ProviderPay and @VoidedTransactions and it works perfectly!  Thank you!

    ALTER FUNCTION [dbo].[BenefitUtilizationVolumeByRelationship] 
    (
    @GroupNumber varchar(25),
    @StartDate datetime,
    @EndDate datetime,
    @Relationship varchar(255),
    @BenefitType varchar(255)
    )
    RETURNS int
    AS
    BEGIN
    DECLARE @ProviderPay Decimal(10,2);
    SELECT @ProviderPay = SUM(ClaimsData.ProviderPayAmount) 
    FROM ClaimsData INNER JOIN PatientRelationships ON ClaimsData.PatientFamilyRelationship = 
    PatientRelationships.PatientRelationshipCode INNER JOIN RetailTypes ON ClaimsData.RetailTypeCode = 
    RetailTypes.RetailTypeCode INNER JOIN RetailSubcategories ON RetailTypes.RetailSubcategoryID = 
    RetailSubcategories.RetailSubcategoryID INNER JOIN RetailCategories ON RetailSubcategories.RetailCategoryID = 
    RetailCategories.RetailCategoryID
    WHERE (ClaimsData.ClientGroupNumber = @GroupNumber) AND 
    (ClaimsData.CheckDate BETWEEN @StartDate AND @EndDate) AND 
    (PatientRelationships.PatientRelationshipDescription = @Relationship) AND 
    (RetailCategories.ReportingCategory = @BenefitType) AND 
    (ClaimsData.ProviderPayAmount > 0);
    SELECT @ProviderPay = ISNULL(@ProviderPay,0)

    DECLARE @VoidedTransactions Decimal(10,2);
    SELECT @VoidedTransactions = SUM(ClaimsData.ProviderPayAmount) 
    FROM ClaimsData INNER JOIN PatientRelationships ON ClaimsData.PatientFamilyRelationship = 
    PatientRelationships.PatientRelationshipCode INNER JOIN RetailTypes ON ClaimsData.RetailTypeCode = 
    RetailTypes.RetailTypeCode INNER JOIN RetailSubcategories ON RetailTypes.RetailSubcategoryID = 
    RetailSubcategories.RetailSubcategoryID INNER JOIN RetailCategories ON RetailSubcategories.RetailCategoryID = 
    RetailCategories.RetailCategoryID
    WHERE (ClaimsData.ClientGroupNumber = @GroupNumber) AND 
    (ClaimsData.CheckDate BETWEEN @StartDate AND @EndDate) AND 
    (PatientRelationships.PatientRelationshipDescription = @Relationship) AND 
    (RetailCategories.ReportingCategory = @BenefitType) AND 
    (ClaimsData.ProviderPayAmount < 0);
    SELECT @VoidedTransactions = ISNULL(@VoidedTransactions,0)

    RETURN @ProviderPay - @VoidedTransactions
    END

    GO

    Tuesday, December 12, 2017 2:39 PM
  • Good morning!

    Added code to check for null values for @ProviderPay and @VoidedTransactions and it works perfectly!  Thank you!

    Hi Little Mojo Puppy,

    It's happy to hear that you have solved your issue by yourself. Well, since you have solved your problem, please help mark the useful reply as answer. Your contribution is highly appreciated.

    Have a good time!

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 10:46 AM