Announcement

Collapse
No announcement yet.

vvIn_virtual inside a CTE with dates

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • vvIn_virtual inside a CTE with dates

    I have a rather long running query that uses CTEs to do various status aggregations on the data. As part of this CTE, the base query is a data heavy query that I would like to limit to user selected time frames. The reason for this is that pulling this data for all time can take up significant amounts of time. Often 20 minutes or more. I instead want to display a a limited range of months by default by limiting the initial query to a month previous to the current date and 3 months after the current date. However, the users should be able to also select dates outside of this range as well through the app we are designing.

    Because this is a CTE, I want to limit the time range on the base query for performance reasons. If we don't filter by date until the end, it can take that full 20+ minute time frame to aggregate all of the data before it ultimately filters out what it doesn't need by date. I instead want to filter out the data by date before the aggregation. I can run these queries by hand and it makes a drastic difference in performance. It can take it from 20 minutes to about 15 seconds when you want to display just a single week whereas filtering at the end of the CTE still takes the full time.

    The issue is that I can't seem to make vvIn_virtual work for this. The relevant portion of the query is this:

    Code:
    myDate >= vvin_virtual('fromDate', 'current_date - 1 month', 'char', 50) and
    myDate <= vvin_virtual('toDate', 'current_date + 3 months', 'char', 50)
    I've attempted quite a few variants of this and none of them will work without errors in NAB. If I hardcode the date to something like '2021-07-19', it ultimately processes fine but I don't want the default date to be hardcoded. I want it to change with the current date. I've tried removing the quotes around the string as well as changing the 3 parameter to things like 'date' and none of them seem to work. I can't find any documentation no how vvIn_virtual is defined either. I can only find examples that use the 'char' type.

    Does anybody have any suggestions for this?

  • #2
    It might help to see what SQL statement it's attempting to run. If you go into Portal Admin > Settings, scroll down to Hidden Settings, and be sure the "Log all SQL statements executed from Nitro App data sources" is checked. (If you don't see a Hidden Settings section, check the "Show hidden settings" box in the Portal Administration section, then Save.)

    With the log turned on and saved, you should see each SQL statement NAB attempts to run in the VVGENLOG file, which you can pull up in the File Editor app. Find the one executed for your data source with the vvin_virutal and let us know what you see.

    Comment


    • #3
      Originally posted by robert.swanson View Post
      It might help to see what SQL statement it's attempting to run. If you go into Portal Admin > Settings, scroll down to Hidden Settings, and be sure the "Log all SQL statements executed from Nitro App data sources" is checked. (If you don't see a Hidden Settings section, check the "Show hidden settings" box in the Portal Administration section, then Save.)

      With the log turned on and saved, you should see each SQL statement NAB attempts to run in the VVGENLOG file, which you can pull up in the File Editor app. Find the one executed for your data source with the vvin_virutal and let us know what you see.
      This is unfortunately not very helpful because it is reporting the problem when I am trying to hit the Next button in the data source. When it reports the errors, it does not log the actual SQL in VVGENLOG. It only shows up when the SQL runs successfully and it shows the JSON after it validates.

      Comment


      • #4
        I guess I'm not clear on what the problem is then. Can you post a screenshot of the error you're seeing and the underlying SQL statement?

        Also let us know what exact Valence build you're on, as this could be a factor as well.

        Comment


        • #5
          Valence Version: 6.0.20210818.0

          Here is the minimal viable example I could come up with to demonstrate the issue:

          Code:
          SELECT *
          FROM TABLE((
              SELECT
                  current_date AS mydate,
                  'SomeGuy' AS myname
              FROM sysibm.sysdummy1))
          WHERE mydate = vvIn_Virtual('someDate', current_date, 'date')
          And the resultant error when I try to use this as a DataSource:

          Screenshot 2021-08-24 153650.png
          Last edited by Player1st; 08-24-2021, 05:42 PM. Reason: Added Valence Version

          Comment


          • #6
            Try this:

            Code:
            WHERE mydate = vvin_virtual('someDate','current_date','char',10,NULL,'false')
            The key is that final 'false' parameter, which tells it not to wrap the value ('current_date' in this case, since the app variable isn't going to be found) in quotes.

            FYI, if you type vvIn_virtual then right-click over it, you'll get a prompt that will create the function call for you...

            vvin_virtual_prompt.jpg

            Comment


            • #7
              Fantastic! That works. Thank you. One final question then. How does the length parameter work in this case? Does it limit the length of the characters after current_date is evaluated? If so, I would have to assume it works the same way if I did something like current_date + 4 weeks as well?

              Comment


              • #8
                The length is only taken into account if use the vvIn_virtual field as a user filter. In that case, we would limit the amount of characters that could be typed into the field.

                Comment


                • #9
                  Perfect. Thank you for your support and taking time to answer my questions!

                  Comment

                  Working...
                  X