• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

VVin_Virtuals, Field Aliases, and Order Bys

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

  • VVin_Virtuals, Field Aliases, and Order Bys

    I have discovered some issues with the NAB Data Source Editor regarding vvin_virtuals, field aliases, and order by clauses.

    I want to make a data source that selects upon a vvin_virtual, and then groups and orders the data by that vvin_virtual. This works fine until I want to make an alias for the vvin_virtual, and then put the alias in the ORDER BY clause. If I try that, I get this error:

    SQL0206 - Column or global variable {alias name} not found.

    If I try to put the SELECT statement in a CTE and then select from that CTE, ordering by the alias outside of the CTE, I still get the same error.

    Interestingly, if I place the "AS {alias name}" from the select clause directly inside of the vv_invirtual, the CTE method mentioned above works.

    However, this only works using a CTE. If I were to keep the vv_invirtual mentioned above, go back to using only a select statement, and order by the alias, then the NAB Data Source Editor will proceed to the "Preview" screen and show a popup that says this:

    Data Source
    Alias added to your column(s) starting with "VV"

    The "OK" button on this popup does not work and I have no other choice but to close NAB, meaning that I will never be able to save the data source.

  • #2
    Hi,

    Can you supply some example SQL statements

    Thanks

    Comment


    • #3
      I just tried the following statement successfully. Is this similar to what you are attempting?

      Code:
      SELECT
      vvin_virtual('field', 'cname', 'char', 10, NULL, 'false') AS myfield
      FROM
      democmast
      GROUP BY
      vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')
      ORDER BY
      vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')

      Comment


      • #4
        The code above works, but if I try to do this:

        Code:
        SELECT
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false') AS myfield
        FROM
        democmast
        GROUP BY
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')
        ORDER BY
        myfield
        I get this error: SQL0206 - Column or global variable MYFIELD not found. The same happens when I do this:

        Code:
        WITH my_cte AS (
        SELECT
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false') AS myfield
        FROM
        democmast
        GROUP BY
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')
        )
        SELECT *
        FROM my_cte
        ORDER BY
        myfield
        When I do this, it works:

        Code:
        WITH my_cte AS (
        SELECT
        vvin_virtual(
        'field',
        'cname AS myfield',
        'char',
        10,
        NULL,
        'false'
        )
        FROM
        democmast
        GROUP BY
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')
        )
        SELECT
        *
        FROM
        my_cte
        ORDER BY
        myfield
        But this only works with a CTE, because when I do this:
        Code:
        SELECT
        vvin_virtual(
        'field',
        'cname AS myfield',
        'char',
        10,
        NULL,
        'false'
        )
        FROM
        democmast
        GROUP BY
        vvin_virtual('field', 'cname', 'char', 10, NULL, 'false')
        ORDER BY
        myfield
        I get this popup in the preview screen that I can't exit out of:

        Data Source
        Alias added to your column(s) starting with "VV"

        I hope this helps!

        Comment


        • #5
          If you need to save the data source, you can try pressing the escape key for now to get past the popup window.

          Comment


          • #6
            Thank you for letting me know! After trying that, I found that the fourth statement that I wrote is still invalid because the NAB Editor added "AS vv0" to the vvin_virtual that already had "AS myfield" in it.

            Comment


            • #7
              Thanks for all the information and this will be fixed in the next 6.1 update

              Comment


              • #8
                No problem! Thank you for your time!

                Comment

                Working...
                X