6 Replies Latest reply on Jul 7, 2016 2:32 PM by Yuriy Fal

    NULL Value Parameters

    Garrett Snyder

      I'm using custom SQL that returns data based on a user input parameter. The parameter is called "Batch ID" and I want the view to stay blank until the user has input the parameter value (integer). Unfortunately, I can't set the default parameter value to NULL. This means that every time the view opens, it runs the query using the default parameter value which can take several minutes. I've tried using 0 and it still runs the query every time the view is initially opened.

        • 1. Re: NULL Value Parameters
          Yuriy Fal

          Hi Garrett,

           

          You may want to set up your [Batch ID Parameter] parameter

          as an Input box with Allowable Values set to All

          and a Default Value set to blank.

           

          The you could create a [Batch ID Filter] calculated filter like this:

           

          CONTAINS( UPPER([Batch ID]), UPPER( [Batch ID Parameter]) )

           

          Put it on Filters shelf, select True and then -- it's important --

          in the Filter dialog go to the Custom Value List section

          and UN-CHECK Include all values when empty flag. That's it.

           

          Hope it could help.

           

          Yours,

          Yuri

          • 2. Re: NULL Value Parameters
            Dmitry Chirkov

            Can you share a snippet of your query where parameter value is used?

            • 3. Re: NULL Value Parameters
              Garrett Snyder

              select

                  r.*,

                  rc.bandwidth_config,

                  rc.latency_config,

                  rc.engine

              from

                  results as r,

                  (

                  select

                      rc.result_id,

                      r.test_type,

                      max(CASE WHEN config_name = 'bandwidth' THEN config_value END) as bandwidth_config,

                      max(CASE WHEN config_name = 'latency' THEN config_value END) as latency_config,

                      CASE WHEN r.test_type IN (1,2,3,4) THEN 'Flash' WHEN r.test_type in (5,6) THEN 'Java Script' ELSE 'Unknown' END AS Engine

                  from

                      speedtestnet.results_configs rc LEFT JOIN speedtestnet.results r ON rc.result_id = r.result_id

                  group by

                      rc.result_id, r.test_type

                  ) as rc

              where

                  r.result_id = rc.result_id

                  AND

                  r.result_id in (

                      select distinct result_id from results_configs

                      where config_name = 'batchId' and config_value in (<Parameters.Batch ID>)

                  )

              • 4. Re: NULL Value Parameters
                Garrett Snyder

                Hi Yuriy,

                 

                Thank you for your reply! I have my parameter setup as an input box with allowable values set to all but I'm unable to set the default value you blank:

                Screen Shot 2016-07-06 at 10.12.49 AM.png

                Whenever I delete the "Current Value" to show blank, it populates a 0 again, even when I do this from within the view filter:

                Screen Shot 2016-07-06 at 10.13.32 AM.png

                This seems to be the crux of my issue, being unable to set the default value to blank.

                • 5. Re: NULL Value Parameters
                  Dmitry Chirkov

                  Fact: there is no way to avoid query execution.

                   

                  So what the problem do you have exactly? Starting with blank view or amount of time it takes to get to blank view?

                  If latter - then I would try to add extra conditions to your SQL to help database process it faster.

                   

                  Something like WHERE IIF(<Parameters.Batch ID> < 0, 'FALSE', 'TRUE') AND [order id] IN (<Parameters.Batch ID>)

                  You might need to experiment as to where exactly (and in how many places) you'd need to add this in order to make it execute very fast. Can't tell from the syntax what's your backend but, perhaps, adding it to outer WHERE condition might be sufficient.

                  • 6. Re: NULL Value Parameters
                    Yuriy Fal

                    Hi Garrett,

                     

                    This is because your Parameter is of Integer type.

                    Change it to a String one -- and you could set it blank.

                     

                    Of course CASTing from STR to INT would be required then:

                     

                    // Modified Batch ID Filter

                    [Batch ID] = INT( [Batch ID Parameter] )

                    Yours,

                    Yuri