Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: How can i do this using dynamic query

    Hi,

    Please find the below query.

    CREATE OR REPLACE Package L_Gsa_Invc_Srch
    Is
    Type T_Cust_Acct_Arr is Table Of Number;
    Type Invc_Base_Rec_Type Is Record (
    Invoice_Control_No Customer_History.Invoice_Control_No%Type,
    Billing_Cycle_Id Eb_Oor_Cust_Online.Billing_Cycle_Id%Type,
    Bill_Cycl_Occr_No Eb_Oor_Cust_Online.Bill_Cycl_Occr_No%Type,
    Invoice_Dt Bill_Cycl_Occr_Inf.Invoice_Date%Type,
    Invc_Acct_Id Eb_Oor_Cust_Online.Customer_Acct_Id%Type,
    Acct_Id Eb_Oor_Cust_Online.Customer_Acct_Id%Type
    );
    Type Invc_Base_Refcur_Type Is Ref Cursor
    Return Invc_Base_Rec_Type;
    Procedure P_Get_Approved_Whole_Invcs (
    In_From_Invc_Dt In Date,
    In_To_Invc_Dt In Date,
    In_Cust_Acct_List In T_Cust_Acct_Arr,
    Out_Eb_Invcs Out Invc_Base_Refcur_Type,
    On_Error_No Out Number
    );
    End L_Gsa_Invc_Srch;
    /

    CREATE OR REPLACE Package Body L_Gsa_Invc_Srch
    Is

    Procedure P_Get_Approved_Whole_Invcs (
    In_From_Invc_Dt In Date,
    In_To_Invc_Dt In Date,
    In_Cust_Acct_List In T_Cust_Acct_Arr,
    Out_Eb_Invcs Out Invc_Base_Refcur_Type,
    On_Error_No Out Number
    )
    Is
    Invalid_Exception Exception;
    Begin
    On_Error_No := 0;

    If In_Cust_Acct_List.Count > 0 Then
    Forall i In 1..In_Cust_Acct_List.Count
    Insert Into Eb_Cust_Acct_Whole_Tmp (Cust_Acct_Val)
    Values (In_Cust_Acct_List (i));
    End If;

    If In_From_Invc_Dt is Null Then
    Begin
    Open Out_Eb_Invcs For
    Select
    Ch.Invoice_Control_No,
    Eoco.Billing_Cycle_Id,
    Eoco.Bill_Cycl_Occr_No,
    Bcoi.Invoice_Date,
    Eoco.Customer_Acct_Id As Invc_Acct_Id,
    Eoco.Customer_Acct_Id As Acct_Id
    From
    Eb_Oor_Cust_Online Eoco,
    Customer_History Ch,
    Bill_Cycl_Occr_Inf Bcoi,
    Audit_Var_Log Avl,
    Audit_Var_Def Avd,
    Eb_Cust_Acct_Whole_Tmp Tmp
    Where
    Avd.Audit_Var_Cd = 'CYCLE_APPROVAL'
    And Avl.Audit_Var_Sub_Grp_Val = 0
    And Avl.Audit_Var_Char_Val = 'COMPLETE'
    And Bcoi.Invoice_Date <= In_To_Invc_Dt
    And Eoco.Customer_Acct_Id = Tmp.Cust_Acct_Val
    And Eoco.Customer_Acct_Id = Ch.Customer_Acct_Id
    And Eoco.Billing_Cycle_Id = Ch.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Ch.Bill_Cycl_Occr_No
    And Eoco.Billing_Cycle_Id = Bcoi.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Bcoi.Bill_Cycl_Occr_No
    And Avd.Audit_Var_Id = Avl.Audit_Var_Id
    And Avl.Audit_Var_Grp_Id = Bcoi.Audit_Var_Grp_Id;
    Exception
    When Others Then
    On_Error_No := 0;
    Raise Invalid_Exception;
    End;
    Elsif In_To_Invc_Dt is Null Then
    Begin
    Open Out_Eb_Invcs For
    Select
    Ch.Invoice_Control_No,
    Eoco.Billing_Cycle_Id,
    Eoco.Bill_Cycl_Occr_No,
    Bcoi.Invoice_Date,
    Eoco.Customer_Acct_Id As Invc_Acct_Id,
    Eoco.Customer_Acct_Id As Acct_Id
    From
    Eb_Oor_Cust_Online Eoco,
    Customer_History Ch,
    Bill_Cycl_Occr_Inf Bcoi,
    Audit_Var_Log Avl,
    Audit_Var_Def Avd,
    Eb_Cust_Acct_Whole_Tmp Tmp
    Where
    Avd.Audit_Var_Cd = 'CYCLE_APPROVAL'
    And Avl.Audit_Var_Sub_Grp_Val = 0
    And Avl.Audit_Var_Char_Val = 'COMPLETE'
    And Bcoi.Invoice_Date >= In_From_Invc_Dt
    And Eoco.Customer_Acct_Id = Tmp.Cust_Acct_Val
    And Eoco.Customer_Acct_Id = Ch.Customer_Acct_Id
    And Eoco.Billing_Cycle_Id = Ch.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Ch.Bill_Cycl_Occr_No
    And Eoco.Billing_Cycle_Id = Bcoi.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Bcoi.Bill_Cycl_Occr_No
    And Avd.Audit_Var_Id = Avl.Audit_Var_Id
    And Avl.Audit_Var_Grp_Id = Bcoi.Audit_Var_Grp_Id;
    Exception
    When Others Then
    On_Error_No := 0;
    Raise Invalid_Exception;
    End;
    Elsif (In_From_Invc_Dt is Null And In_To_Invc_Dt is Null) Then
    Begin
    Open Out_Eb_Invcs For
    Select
    Ch.Invoice_Control_No,
    Eoco.Billing_Cycle_Id,
    Eoco.Bill_Cycl_Occr_No,
    Bcoi.Invoice_Date,
    Eoco.Customer_Acct_Id As Invc_Acct_Id,
    Eoco.Customer_Acct_Id As Acct_Id
    From
    Eb_Oor_Cust_Online Eoco,
    Customer_History Ch,
    Bill_Cycl_Occr_Inf Bcoi,
    Audit_Var_Log Avl,
    Audit_Var_Def Avd,
    Eb_Cust_Acct_Whole_Tmp Tmp
    Where
    Avd.Audit_Var_Cd = 'CYCLE_APPROVAL'
    And Avl.Audit_Var_Sub_Grp_Val = 0
    And Avl.Audit_Var_Char_Val = 'COMPLETE'
    And Eoco.Customer_Acct_Id = Tmp.Cust_Acct_Val
    And Eoco.Customer_Acct_Id = Ch.Customer_Acct_Id
    And Eoco.Billing_Cycle_Id = Ch.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Ch.Bill_Cycl_Occr_No
    And Eoco.Billing_Cycle_Id = Bcoi.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Bcoi.Bill_Cycl_Occr_No
    And Avd.Audit_Var_Id = Avl.Audit_Var_Id
    And Avl.Audit_Var_Grp_Id = Bcoi.Audit_Var_Grp_Id;
    Exception
    When Others Then
    On_Error_No := 0;
    Raise Invalid_Exception;
    End;
    Else
    Begin
    Open Out_Eb_Invcs For
    Select
    Ch.Invoice_Control_No,
    Eoco.Billing_Cycle_Id,
    Eoco.Bill_Cycl_Occr_No,
    Bcoi.Invoice_Date,
    Eoco.Customer_Acct_Id As Invc_Acct_Id,
    Eoco.Customer_Acct_Id As Acct_Id
    From
    Eb_Oor_Cust_Online Eoco,
    Customer_History Ch,
    Bill_Cycl_Occr_Inf Bcoi,
    Audit_Var_Log Avl,
    Audit_Var_Def Avd,
    Eb_Cust_Acct_Whole_Tmp Tmp
    Where
    Avd.Audit_Var_Cd = 'CYCLE_APPROVAL'
    And Avl.Audit_Var_Sub_Grp_Val = 0
    And Avl.Audit_Var_Char_Val = 'COMPLETE'
    And Bcoi.Invoice_Date >= In_From_Invc_Dt
    And Bcoi.Invoice_Date <= In_To_Invc_Dt
    And Eoco.Customer_Acct_Id = Tmp.Cust_Acct_Val
    And Eoco.Customer_Acct_Id = Ch.Customer_Acct_Id
    And Eoco.Billing_Cycle_Id = Ch.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Ch.Bill_Cycl_Occr_No
    And Eoco.Billing_Cycle_Id = Bcoi.Billing_Cycle_Id
    And Eoco.Bill_Cycl_Occr_No = Bcoi.Bill_Cycl_Occr_No
    And Avd.Audit_Var_Id = Avl.Audit_Var_Id
    And Avl.Audit_Var_Grp_Id = Bcoi.Audit_Var_Grp_Id;
    Exception
    When Others Then
    On_Error_No := 0;
    Raise Invalid_Exception;
    End;
    End If;
    Exception
    When Others Then
    On_Error_No := -1;
    Raise Invalid_Exception;
    End P_Get_Approved_Whole_Invcs;
    End L_Gsa_Invc_Srch;
    /

    This Pkg compiles fine. I don't have any issue with that. But i want to optimize this query using dynamic sql/query. If you see here, I have used 4 if..elseif..elseif...else conditions inside the procedure for the same 'SELECT' statement adding some etc "where" condition. Is there a better way that i could do this using dynamic query.

    Thanks,
    PLS

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a better way
    Better as measured by what metric?

    Having fewer characters in the SQL statement does not mean faster run time performance.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2008
    Posts
    3
    Quote Originally Posted by anacedent
    >Is there a better way
    Better as measured by what metric?

    Having fewer characters in the SQL statement does not mean faster run time performance.
    I agree. But in terms of better coding style, i want to reduce the no. of lines in the code using dynamic query than using the typical style of using 'If....Else'

    Any suggestions that how could i use the dynamic sql on this query.

    Thanks,
    Last edited by plsnonstop; 12-17-08 at 01:49.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i want to reduce the no. of lines in the code
    Then make the code a single line; which is the shortest length which does something.

    I contend your metric is arbitrary & meaningless.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2008
    Posts
    3
    Quote Originally Posted by anacedent
    >i want to reduce the no. of lines in the code
    Then make the code a single line; which is the shortest length which does something.

    I contend your metric is arbitrary & meaningless.
    Might be. But i need a work around. How this could be done.

    CREATE OR REPLACE Package Body L_Gsa_Invc_Srch
    Is

    Procedure P_Get_Approved_Whole_Invcs (
    In_From_Invc_Dt In Date,
    In_To_Invc_Dt In Date,
    In_Cust_Acct_List In T_Cust_Acct_Arr,
    Out_Eb_Invcs Out Invc_Base_Refcur_Type,
    On_Error_No Out Number
    )
    Is
    Query varchar2(4000);
    Invalid_Exception Exception;
    Begin
    On_Error_No := 0;

    If In_Cust_Acct_List.Count > 0 Then
    Forall i In 1..In_Cust_Acct_List.Count
    Insert Into Eb_Cust_Acct_Whole_Tmp (Cust_Acct_Val)
    Values (In_Cust_Acct_List (i));
    End If;


    Begin

    Query := 'Select Ch.Invoice_Control_No,';
    Query := Query||' Eoco.Billing_Cycle_Id,';
    Query := Query||' Eoco.Bill_Cycl_Occr_No,';
    Query := Query||' Bcoi.Invoice_Date,';
    Query := Query||' Eoco.Customer_Acct_Id As Invc_Acct_Id,';
    Query := Query||' Eoco.Customer_Acct_Id As Acct_Id ';
    Query := Query||'From Eb_Oor_Cust_Online Eoco,';
    Query := Query||' Customer_History Ch,';
    Query := Query||' Bill_Cycl_Occr_Inf Bcoi,';
    Query := Query||' Audit_Var_Log Avl,';
    Query := Query||' Audit_Var_Def Avd,';
    Query := Query||' Eb_Cust_Acct_Whole_Tmp Tmp ';
    Query := Query||'Where Avd.Audit_Var_Cd = ''CYCLE_APPROVAL''';
    Query := Query||' And Avl.Audit_Var_Sub_Grp_Val = 0';
    Query := Query||' And Avl.Audit_Var_Char_Val = ''COMPLETE''';
    Query := Query||' And Eoco.Customer_Acct_Id = Tmp.Cust_Acct_Val';
    Query := Query||' And Eoco.Customer_Acct_Id = Ch.Customer_Acct_Id';
    Query := Query||' And Eoco.Billing_Cycle_Id = Ch.Billing_Cycle_Id';
    Query := Query||' And Eoco.Bill_Cycl_Occr_No = Ch.Bill_Cycl_Occr_No';
    Query := Query||' And Eoco.Billing_Cycle_Id = Bcoi.Billing_Cycle_Id';
    Query := Query||' And Eoco.Bill_Cycl_Occr_No = Bcoi.Bill_Cycl_Occr_No';
    Query := Query||' And Avd.Audit_Var_Id = Avl.Audit_Var_Id';
    Query := Query||' And Avl.Audit_Var_Grp_Id = Bcoi.Audit_Var_Grp_Id';


    If In_From_Invc_Dt Is Null Then
    Query:=Query||'And Bcoi.Invoice_Date <= In_To_Invc_Dt';
    End If;

    If In_To_Invc_Dt Is Null Then
    Query:=Query||'And Bcoi.Invoice_Date >= In_From_Invc_Dt';
    End If;

    If (In_From_Invc_Dt != '' And In_To_Invc_Dt != '') Then
    Query:=Query||'And Bcoi.Invoice_Date >= In_From_Invc_Dt';
    Query:=Query||'And Bcoi.Invoice_Date <= In_To_Invc_Dt';
    End If;

    Open Out_Eb_Invcs For Query;
    Exception
    When Others Then
    On_Error_No := 0;
    Raise Invalid_Exception;
    End;
    Exception
    When Others Then
    On_Error_No := -1;
    Raise Invalid_Exception;
    End P_Get_Approved_Whole_Invcs;
    End L_Gsa_Invc_Srch;
    /
    I tried this. But it has some issues while opening the cursor.

Posting Permissions

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