Why does AA not support arguments directly in field SELECT statement?

[RichCPT]

Why is it that AA does not fully support putting SQL Arguments into SELECT portion of a SQL statement?

Over and over, I have found it would be extremely convenient if I could place a SQL argument directly into the SELECT clause of fields either in an "IF" expression or directly as a column to be returned.

There are two scenarios where this always crops up for me:
1) letting SQL be aware of the current logged in user, so it can perform security related processing or so it can echo back security values for client-side processing,
2) letting SQL be aware of a particular column value from a grandparent table that is not involved directly in the query, especially when using the Nested SQL Genie builder. The grandparent table can be joined into a second level query, but it seems wasteful to do so, when the grandparent table values are already known way ahead of time (before the top level query is even run!) and could be passed in as arguments to be used in the SELECT clause directly as returned values or in "IF" statements.

[Steve Wood]

Re: Why does AA not support arguments directly in field SELECT statement?

Could you write some pseudo SQL code to show where you want the arguments to be used, and how and where you intend to use the IF clause?

I know you mean something more complete than the following example:
dim session.userid as c (assuming this stores the userid and is populated upon login)
dim cn as sql::connection
dim args as sql::arguments
cn.open("::name::conn")
vsql = "SELECT * WHERE userid = :userid"
args.add("userid",session.userid)
cn.execute(vsql, args) 'omitted error trapping
... use results
cn.close()

[GGandhi]

Re: Why does AA not support arguments directly in field SELECT statement?

you could do it if you put the if clause outside of sqlSelect statement.

[RichCPT]

Re: Why does AA not support arguments directly in field SELECT statement?

Steve,
I should have been more specific, it is various places in the AA Builders that I run into trouble if arguments are referenced when defining columns for output, either to be output directly or to be used in an alphaDAO "IF" statement. For example, when building the SQL Statement for a ViewBox, the ViewBox fails to build the list of "Fields" if an argument is used anywhere in the SELECT clause. If I'm adding a ViewBox with a connection to NorthWinds and I have an Argument for the UX defined as gRegion (character) then the following SQL statement causes the ViewBox to not list any entries in the "Fields" list:

SELECT CustomerID,  If( Region = :gRegion , 'Y', 'N')  AS Expr1, CompanyName, ContactName, ContactTitle, Address, City, Region 
FROM Customers

Gandhi,
Yes, but adding what I need outside of the SELECT clause means I have to add a table using CROSS JOIN (which the AA SQL Genie builders do not support) or add a FULL OUTER JOIN where I relate the key field in the joined table to a hard-coded constant, like 'primary_key_field > 0' (which really seems like improper SQL) and add my argument into the WHERE clause.

[mikeallenbrown]

Re: Why does AA not support arguments directly in field SELECT statement?

I'm by no means a SQL expert so, for what it's worth, could you use CASE to accomplish what you want?

SELECT CustomerID, CASE WHEN Region = :gRegion THEN 'Y' ELSE 'N' END AS Expr1, CompanyName, ContactName, ContactTitle, Address, City, Region
FROM Customers

[Davidk]

Re: Why does AA not support arguments directly in field SELECT statement?

It's a bit of a complex statement. Easily addressed in MySQL or SQL Server... but not everywhere in Alpha builders. Instead, just fake out the builder to get the ViewBox built... and then put back your if statement. Also... it's not a matter of a good SQL Statement or not... or whether you use IF or CASE... but what the builder understands and can parse.

Use this statement for the builder...

SELECT CustomerID, 'Y' AS Expr1, CompanyName, ContactName, ContactTitle, Address, City, Region 
FROM Customers

You'll now have your fields. Then build out the ViewBox. When you have your layout and other stuff set... add back the if statement.

You didn't specify what how your argument was put together... may not matter.

[Steve Wood]

Re: Why does AA not support arguments directly in field SELECT statement?

Backing up Mike's comment, here is an update statement using CASE.

vsql = <<%txt%
UPDATE project_requests SET
status = CASE WHEN :new_status <> 'Open' THEN 'Closed' ELSE 'Open' END,
status_detail = CASE WHEN :new_status <> 'Open' THEN :new_status ELSE NULL END,
closed_date = CASE WHEN :new_status <> 'Open' THEN :closed_date ELSE NULL END
WHERE id = :id
%txt%

[RichCPT]

Re: Why does AA not support arguments directly in field SELECT statement?

Switching the SQL to native syntax in the SQL Genie Builder does not help; parts the of the builders still refuse to handle arguments in the column list section of the SQL SELECT statement.

What is strange, to me, is that the inner levels of the SQL Genie Builder know how to execute the SQL and prompt for any arguments that are used. But, when you get to the outer levels of the builders (like the ViewBox Builder "Data Source" tab) the IDE totally forgets to prompt for and build arguments and the query apparently fails with an internal error that is not shown and it does not repopulate the list of fields to display on the "Fields" tab. If the "Fields" tab was previously built when the SQL did not contain arguments then you will see the fields from the previous SQL that worked.

[Davidk]

Re: Why does AA not support arguments directly in field SELECT statement?

Not really sure which part you're working in... but in the ViewBox, creating a CustomerSQLStatement without your "if" clause... in order to build out the ViewBox, and then adding back the "if" clause... works.