SQL Query Form

KnightWare

Where is this SQL QUery Form tab? User Guide, Page 430. Desinging a Query on a BO connected to external database, (MS SQL 2012). Using Native "Find" statement - FIND PAY_PERIODS WHERE PAY_PERIODS.COMPANY=103 AND PAY_PERIODS.PPED >= DATE_ADD(CURRENT_DATE,-60) Reults in error - (DATE_ADD.....) cannot be converted to this version SQL.

SO can I use native SQL? If I try on TEXT tab, it doesn't like the first "." of the Select PP.*.

Select PP.*
From PAY_PERIODS AS PP
WHERE (PP.COMPANY=103)
and (PP.PPED >= DateAdd(wk,-12,Getdate()))

JonP

I'm a little rusty, but shouldn't it be "Select * From..."?

KnightWare

I used an Alias as per the example. And even If I don't, it complains about the '*', when setting up the SQL Query on the Textual view tab.

Jaymer

In current Aware, you can't write your own SQL statements anymore.
I think that was removed in v7, but for sure its not in v8.

PointsWell

Jaymer wrote
In current Aware, you can't write your own SQL statements anymore.
I think that was removed in v7, but for sure its not in v8.

It was definitely in 7 which didn't have individual databases which made it very hard to write an SQL query that would work in test and prod due to the prefacing of table names with BDTEST and BD (or whatever). Now that these limitations have gone it would be super useful to have back.

aware_support

SQL tab is obsolete (documentation hasn't been corrected).

If you want to user SQL for your queries use stored procedures and EXEC_SP

KnightWare

So then using native query against BO external to MS SQL, The functions don't work. Trying to select (FIND) data with a date range within the last two months.
FIND PAY_PERIODS WHERE PAY_PERIODS.PPED >= DATE_ADD(CURRENT_DATE, -60)

Fails, stating it (DATE_ADD) can't be converted to current version of SQL (2012).