5 Replies Latest reply: Apr 11, 2014 4:42 PM by Ysaki RSS

    Parameter is doesnt work when choosing ALL values

    Ysaki

      Dear All

       

      i am facing this issue again with the parameter.

       

      The issue now is, when i choose the value of parameter as ALL, its retrieving "  http://obi-dev.jr.local:9704/xmlpserver/resource/blafplus/dialog/title_error.pngError occurred on retrieving data xml"

       

      i ran a count for the items to see how many items in the list of values and its almost 3000 items

       

      am i facing this error because of the big number of values or the parameter can handle this much of data or am i doing something wrong ?

       

       

      Please Advice, Thank You


       

        • 1. Re: Parameter is doesnt work when choosing ALL values
          Manikandan-S-Oracle

          I'm not able to see your error message. Can you post it again?

           

          In general,

           

          In your parameter definition, what type of selection you are using ? is it "Null Values Passed" or "All values passed".

           

          Selecting "All values passed will not work for the parameter having more than 1000 values".

          If you use "Null values passed", you need to handle NVL function at your SQL code.

          • 2. Re: Parameter is doesnt work when choosing ALL values
            Ysaki

            Manikandan

             

            Thank  you for the reply and helping.

             

            The error message is

             

             

            http://obi-dev.jr.local:9704/xmlpserver/resource/blafplus/dialog/title_error.pngError occurred on retrieving data xml

            the SQL is : 

            select

             

            Distinct a.padded_concatenated_segments

            , a.item_description

            , a.subinventory_code

            , a.organization_id

            , a.total_qoh

             

            ,c.segment1  || ' - ' || c.segment2  || ' - ' || c.segment3  || ' - ' || c.segment4  || ' - ' || c.segment5  || ' - ' || c.segment6  || ' - ' || c.segment7  as Category 

            , b.item_cost

            from

            mtl_onhand_sub_v a

            , mtl_categories c

            ,cst_item_cost_type_v b

            where (a.organization_id = ('298')

            or a.organization_id = ('276'))

            and a. padded_concatenated_segments = b.padded_item_number

            and b.category_id = c.category_id

            and a.padded_concatenated_segments = NVL(:PARA_PADDED_ITEM_NUMBER,PADDED_ITEM_NUMBER)

             

            order by a.padded_concatenated_segments

            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             

            As for the Parameter SQl is :

            select distinct  padded_concatenated_segments

            from mtl_onhand_sub_v

            -- where (organization_id = ('298')

            --or organization_id = ('276'))

            where padded_concatenated_segments like '9%'

            or padded_concatenated_segments like 'N%'

             

            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             

            The type of Prameter is list one and i have checked to do multiple selection, can select all and null value passed.

             

            the Issue is happening when i chose all or even when i chose more than one value from the list of values. it is working fine as long as i am not choosing more than one item

             

             

            Your guidance is highly appreciated

             

            Thank you

             

             

             

             

             

             

            • 3. Re: Parameter is doesnt work when choosing ALL values
              AlexAnd

              and a.padded_concatenated_segments = NVL(:PARA_PADDED_ITEM_NUMBER,PADDED_ITEM_NUMBER)

              and so

              the Issue is happening when i chose all or even when i chose more than one value from the list of values. it is working fine as long as i am not choosing more than one item

              above WHERE clause part it's for one value parameter like

              and a.padded_concatenated_segments = NVL(/*:PARA_PADDED_ITEM_NUMBER*/123,PADDED_ITEM_NUMBER)

               

              but if you have several values in parameters it's looks like

              and a.padded_concatenated_segments = NVL(/*:PARA_PADDED_ITEM_NUMBER*/123,678,PADDED_ITEM_NUMBER)

              that is incorrect

               

              NOTE: i may wrong for

              NVL(123,678,PADDED_ITEM_NUMBER)

              but looks like it's

              because

              NVL('123,678',PADDED_ITEM_NUMBER)

              is syntactically correct

               

              so you must differ one value case and several values case

               

              so as idea

              - create lexical reference in sql statement ( "Lexical References" Oracle Business Intelligence Publisher Report Designer's Guide )

              - create foo package for filling lexical reference like

               

              package foo

              as

              where_clause varchar2(1000);

              para_padded_item_number varchar2(1000);

                .....

               

              package body foo

              as

              .....

              select 1

              into local_variable1 from mtl_onhand_sub_v a

              where a.padded_concatenated_segments = NVL(PARA_PADDED_ITEM_NUMBER,PADDED_ITEM_NUMBER);

               

              where_clause := 'and a.padded_concatenated_segments = NVL(PARA_PADDED_ITEM_NUMBER,PADDED_ITEM_NUMBER)';

               

               

              exception ...

              where_clause := 'and a.padded_concatenated_segments in PARA_PADDED_ITEM_NUMBER';

              so you check correctness of working query for PARA_PADDED_ITEM_NUMBER

              if it works then you have one value ( or all - not sure about your logic)

              if doesn't work then goto exception section and it'll be statement with IN

               

              you can check needed logic by some test

              - select one value (or all) and check correctness of working with NVL as is

              - select several values and try IN instead of NVL

               

              then you can forming dynamic handling

              • 4. Re: Parameter is doesnt work when choosing ALL values
                Manikandan-S-Oracle

                I have also encountered this issue and I tried the below approach which worked for me..

                 

                 

                I understand that you are using Multi-Select type at the same time you are also passing Null values when ALL is chosen.

                 

                 

                Changes have to do with your SQL code

                 

                 

                Assuming that below is your parameter where clause :

                 

                 

                and (a.padded_concatenated_segments in (:PARA_PADDED_ITEM_NUMBER)

                        or

                        a.padded_concatenated_segments = DECODE(:PARA_PADDED_ITEM_NUMBER,NULL,PADDED_ITEM_NUMBER,'-1')

                 

                 

                Note that "or" command should be inside the bracket.

                 

                 

                Now,

                 

                     When you choose ALL as a value in your parameter, the below code will be taken care

                     a.padded_concatenated_segments = DECODE(:PARA_PADDED_ITEM_NUMBER,NULL,PADDED_ITEM_NUMBER,'-1')

                 

                     When you select single value or a multiple value, the below code will be taken care

                     a.padded_concatenated_segments in (:PARA_PADDED_ITEM_NUMBER)

                • 5. Re: Parameter is doesnt work when choosing ALL values
                  Ysaki

                  Manikandan

                   

                  Thank you for the answer and  response, much appreciated and i apologize for the belated response.

                   

                  The shared SQL for the parameter is working perfectly, and i have done the same concept but with few changes to apply another parameter for the Organization Name

                   

                   

                  Thank You