VVin_Virtuals, Field Aliases, and Order Bys

[TonyDB]

No problem! Thank you for your time!

[Johnny Major]

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

[TonyDB]

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.

[sean.lanktree]

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

[TonyDB]

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

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:

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:

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:

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!

[sean.lanktree]

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

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')

[Johnny Major]

Hi,

Can you supply some example SQL statements

Thanks

[TonyDB]

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.