The query below results in the correct resultset in ADMIN SQL execution window.

But when I add the CREATE VIEW to it OLEDB does not execute. Failed to parse sql message is displayed.

I am trying to list the last completed date an account was contacted for a SalesCall category.Also list the accounts that were never contact for a SalesCall category. So I am doing an UNION. Create VIEW does not seem to like UNION in the query.

Any other ways of getting this done???

CREATE VIEW [sysdba].[C_V_LASTSALESCALL]
AS

SELECT DISTINCT
                      sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE, sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE, MAX(sysdba.HISTORY.COMPLETEDDATE)
                      AS LASTCALLDATE
FROM         sysdba.ACCOUNT  INNER JOIN
                      sysdba.C_ACCOUNT_EXT  ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT.ACCOUNTID INNER JOIN
                      sysdba.HISTORY ON sysdba.ACCOUNT.ACCOUNTID = sysdba.HISTORY.ACCOUNTID INNER JOIN
                      sysdba.CONTACT ON sysdba.HISTORY.ACCOUNTID = sysdba.CONTACT.ACCOUNTID
WHERE     (sysdba.HISTORY.COMPLETEDUSER <> 'ADMIN') AND (sysdba.HISTORY.CATEGORY LIKE '%sales%')
GROUP BY sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE,sysdba.ACCOUNT.REVENUE, sysdba.C_ACCOUNT_EXT.COIPERCENT, sysdba.ACCOUNT.TYPE,sysdba.ACCOUNT.SUBTYPE

UNION ALL

SELECT  sysdba.ACCOUNT.ACCOUNTID,sysdba.ACCOUNT.ACCOUNT,sysdba.ACCOUNT.MAINPHONE,
sysdba.ACCOUNT.REVENUE,sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE, NULL AS LASTCALLDATE
FROM         sysdba.ACCOUNT
WHERE     (sysdba.ACCOUNT.ACCOUNTID NOT IN
                          (SELECT DISTINCT sysdba.ACCOUNT.ACCOUNTID
                            FROM          sysdba.ACCOUNT  INNER JOIN
                                                   sysdba.C_ACCOUNT_EXT ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT.ACCOUNTID INNER JOIN
                                                   sysdba.HISTORY AS HISTORY_1 ON sysdba.ACCOUNT.ACCOUNTID = HISTORY_1.ACCOUNTID INNER JOIN
                                                   sysdba.CONTACT AS CONTACT_1 ON HISTORY_1.ACCOUNTID = CONTACT_1.ACCOUNTID
                            WHERE      (HISTORY_1.COMPLETEDUSER <> 'ADMIN') AND (HISTORY_1.CATEGORY LIKE '%sales%')
                            GROUP BY sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT,
                sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE, sysdba.C_ACCOUNT_EXT.COIPERCENT,
                           sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE))

Quote: Originally posted by Meena The query below results in the correct resultset in ADMIN SQL execution window. But when I add the CREATE VIEW to it OLEDB does not execute. Failed to parse sql message is displayed. I am trying to list the last completed date an account was contacted for a SalesCall category.Also list the accounts that were never contact for a SalesCall category. So I am doing an UNION. Create VIEW does not seem to like UNION in the query. Any other ways of getting this done??? CREATE VIEW [sysdba].[C_V_LASTSALESCALL] AS SELECT DISTINCT                       sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE, sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE, MAX(sysdba.HISTORY.COMPLETEDDATE)                       AS LASTCALLDATE FROM         sysdba.ACCOUNT  INNER JOIN                       sysdba.C_ACCOUNT_EXT  ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT.ACCOUNTID INNER JOIN                       sysdba.HISTORY ON sysdba.ACCOUNT.ACCOUNTID = sysdba.HISTORY.ACCOUNTID INNER JOIN                       sysdba.CONTACT ON sysdba.HISTORY.ACCOUNTID = sysdba.CONTACT.ACCOUNTID WHERE     (sysdba.HISTORY.COMPLETEDUSER <> 'ADMIN') AND (sysdba.HISTORY.CATEGORY LIKE '%sales%') GROUP BY sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE,sysdba.ACCOUNT.REVENUE, sysdba.C_ACCOUNT_EXT.COIPERCENT, sysdba.ACCOUNT.TYPE,sysdba.ACCOUNT.SUBTYPE UNION ALL SELECT  sysdba.ACCOUNT.ACCOUNTID,sysdba.ACCOUNT.ACCOUNT,sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE,sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE, NULL AS LASTCALLDATE FROM         sysdba.ACCOUNT WHERE     (sysdba.ACCOUNT.ACCOUNTID NOT IN                           (SELECT DISTINCT sysdba.ACCOUNT.ACCOUNTID                             FROM          sysdba.ACCOUNT  INNER JOIN                                                    sysdba.C_ACCOUNT_EXT ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT.ACCOUNTID INNER JOIN                                                    sysdba.HISTORY AS HISTORY_1 ON sysdba.ACCOUNT.ACCOUNTID = HISTORY_1.ACCOUNTID INNER JOIN                                                    sysdba.CONTACT AS CONTACT_1 ON HISTORY_1.ACCOUNTID = CONTACT_1.ACCOUNTID                             WHERE      (HISTORY_1.COMPLETEDUSER <> 'ADMIN') AND (HISTORY_1.CATEGORY LIKE '%sales%')                             GROUP BY sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT,                 sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE, sysdba.C_ACCOUNT_EXT.COIPERCENT,                            sysdba.ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE))    Further modified the query to look like this, but get fsailed to parse query in the admin exec sql window.                               SELECT   sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE, sysdba.ACCOUNT.REVENUE, sysdba .ACCOUNT.TYPE, sysdba.ACCOUNT.SUBTYPE ,(SELECT top 1 H.COMPLETEDDATE FROM sysdba.HISTORY H LEFT JOIN sysdba.ACCOUNT ON sysdba.ACCOUNT.ACCOUNTID=H.accountid LEFT JOIN sysdba.C_ACCOUNT_EXT ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT. ACCOUNTID LEFT JOIN sysdba.CONTACT ON sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT. ACCOUNTID   WHERE (H.CoMPLETEDUSER <> 'ADMIN') AND (H.CATEGORY LIKE '%sales%')   ORDER BY H.COMPLETEDDATE DESC) FROM  sysdba.ACCOUNT LEFT JOIN sysdba .C_ACCOUNT_EXT ON sysdba.ACCOUNT.ACCOUNTID = sysdba.C_ACCOUNT_EXT.ACCOUNTID LEFT JOIN sysdba .CONTACT ON sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT. ACCOUNTID LEFT JOIN sysdba.HISTORY ON sysdba.CONTACT.CONTACTID = sysdba.HISTORY. CONTACTID GROUP BY sysdba.ACCOUNT.ACCOUNTID, sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.MAINPHONE,sysdba.ACCOUNT.REVENUE, sysdba .C_ACCOUNT_EXT.COIPERCENT, sysdba.ACCOUNT.TYPE,sysdba.ACCOUNT. SUBTYPE ORDER BY ACCOUNT.ACCOUNT ASC   GO

Typically one would add the view via sql and enable the view in db manager. However in the case where you are trying to push the view out to remotes via sync, use exec(' sql stmt ') to force the code through the slx provider. You would still need to enable it via db manager, then update the resynctabledefs to add a primary key so it can be used in grids etc.

The problem is exec('sql stmt') fails with 'Failed to parse sql' message.

the old method probably work best then

1) create a vbs script that generated a direct sql connection to the active db, and push the if exist drop/create code. add a customsetting to the database that is checked first so you can control it, then add it as the db startup sctipt. now you can push a null to your field and every remote and the first network user will force the regeneration/creation of the view. And you can add as many as you want. Including sp's for reporting.

2) use sync to push a .cmd file to the remote, execute the file and delete it. teh dile should call oSQL and subsequent parameters to generate the sql you need. I temd to place the sql in a separate file under the slx folder and reference the file. This allows me to control multiple sql stmts, including retrieving specific data from the user via sync and the timing of the process.

There was a sql injection issue that allowed security to be bypassed, tables dropped etc and this was close a while ago. The blocking of UNION was definately part of that but I thought the Pushing of an exec() script from the administrator execute sql function as the admin user, by passed that security layer. It is possible that web changes have caused that bridge to be closed.