5 Replies Latest reply: May 20, 2012 10:08 AM by PurnaC10 RSS

DB Storedproc or function

PurnaC10

I have a database stored function and I want to implement the same function on qlikview side for dynamic calculation when ever the user selection changes. so i would like to know the best option to handle this sceneario ie. write a vb script or setAnalysis.

 

Thanks

  • DB Storedproc or function
    Kaushik Solanki

    Hi,

     

        I would suggest you to use set analysis if you are able to achieve what you want with that.

     

        The reason is set analysis is very fast as compared to vb script code.

     

    Regards,

    Kaushik Solanki

    • DB Storedproc or function
      PurnaC10

      Let me try SA first then.. Thanks for your quick responce...

      • DB Storedproc or function
        Kaushik Solanki

        Hi,

         

           Yes try and let us know if you have any more problem.

         

        Regards,

        Kaushik Solanki

        • DB Storedproc or function
          PurnaC10

          I am trying to convert this fun to SetAnalysis and i was confused on how to convert WHERE conditions and input variable in this function. your suggestions are welcome...

           

          CREATE FUNCTION dbo.fnGetABCAAmount

              (

                @ABC_ZZZD INT ,

                @ZZZZcode VARCHAR(5) ,

                @IsTransisitonedFacility BIT ,

                @DateOfService DATE

              )

          RETURNS MONEY

          AS

              BEGIN

                 

                  IF @DateOfService IS NULL

                      SET @DateOfService = GETDATE();

           

                  DECLARE @amount MONEY

           

                  SELECT  @amount = ( ( ( WorkABC * WorkZZZ )

                                      * ( CASE WHEN YEAR(@DateOfService) < 2006 THEN 1

                                               WHEN YEAR(@DateOfService) = 2007

                                               THEN 0.8994

                                               WHEN YEAR(@DateOfService) = 2008

                                               THEN 0.8806

                                               ELSE 1

                                          END  -- Budget Adjustor Value

                                                ) )  + ( PEZZZ

                                                     * ( CASE WHEN @IsTransisitonedFacility = 1

                                                              THEN TransitionedFacilityPExpenseABC

                                                              ELSE TransitionedNon_FacilityPExpenseABC

                                                         END ) ) + ( MalpracticeABC

                                                                     * MPZZZ ) )

                          * ConversionFactor

                  FROM    dbo.ABC_PprABC ABC JOIN

                          dbo.ABC_GPC gpc ON ABC.ABC_HeaderID = gpc.ABC_HeaderID

                          JOIN dbo.ABC_Header h ON gpc.ABC_HeaderID = h.ABC_HeaderID

                          AND @DateOfService BETWEEN StartDate AND EndDate

                  WHERE   ZZZZcode = @ZZZZcode

                          AND ABC_ZZZD = @ABC_ZZZD

           

                  

                  RETURN CAST( @amount AS DECIMAL(10,2))

              END

          go