Results 1 to 3 of 3

Thread: SP issue

  1. #1
    Join Date
    Oct 2005
    Posts
    21

    Unhappy Unanswered: SP issue

    Hi Gurus,
    I am a novice in the sybase.
    My requirement is :
    The input table:tbl_SCR_ProPBM_PA_ErrorCat
    It contains:

    reject_code,drug_error_cat_ovr,days_supply_error_c at_ovr,qty_error_cat_ovr,refill_error_cat_ovr,max_ cost_error_cat_ovr,dur_error_cat_ovr
    70 1 0 0 0 0 0
    76 0 0 0 1 0 0
    71 0 1 0 0 0 1



    My required output when the input is 70,71,00,00,00,00 should be
    1,1,0,0,0,1

    The SP that i wrote is given below:

    CREATE PROCEDURE dbo.SP_SCR_ProPBM_PA_getErrorCat
    (@RejectCode1 char(2),
    @RejectCode2 char(2),
    @RejectCode3 char(2),
    @RejectCode4 char(2),
    @RejectCode5 char(2),
    @RejectCode6 char(2),
    @RC1 char(1) OUTPUT ,
    @RC2 char(1) OUTPUT ,
    @RC3 char(1) OUTPUT ,
    @RC4 char(1) OUTPUT ,
    @RC5 char(1) OUTPUT ,
    @RC6 char(1) OUTPUT )

    AS
    BEGIN

    select @RC1=drug_error_cat_ovr,
    @RC2=days_supply_error_cat_ovr,
    @RC3=qty_error_cat_ovr,
    @RC4=refill_error_cat_ovr,
    @RC5=max_cost_error_cat_ovr,
    @RC6=dur_error_cat_ovr
    from dbo.tbl_SCR_ProPBM_PA_ErrorCat
    where reject_code IN(@RejectCode1),@RejectCode2,@RejectCode3,@Reject Code4,@RejectCode5,@RejectCode6)

    END


    The output that i get with this SP is
    0 1 0 0 0 1 which is of the reject_code =71

    What to be changed to get the output as
    1 1 0 0 0 1

    Thanks in Advance.
    Nura.
    Last edited by Nura; 10-06-06 at 11:02.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use max if you want to see if an error occurred
    else use sum to count the number of errors
    select @RC1=max(drug_error_cat_ovr),
    @RC2=max(days_supply_error_cat_ovr),
    ...

  3. #3
    Join Date
    Oct 2005
    Posts
    21

    Thank you

    Thanks a lot.
    I really missed the basics

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •