DBFit jdbc.SQLServerException: Incorrect syntax near the keyword 'as'

Question

I'm a stackoverflow newbie and also new to DBFit. I have already done a successful DBFit test, however in one of my new tests, I'm getting an exception error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'as'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
    at dbfit.fixture.Query.getDataTable(Query.java:40)

Here is part of the fit page I'm testing:

!3 Define ODS Query
!define ODSQuery { SELECT b.BrokerID
          ,b.BrokerCode as BC 
      FROM dbo.Broker b
      }

!3 Define ODMart Query
!define ODMartQuery {select BrokerID
          ,BrokerCode as BC 
from DSE_ODMART_TST_SIT.dbo.d1_Broker}

!3 Compare ODS vs. ODMart
| query |(${ODSQuery} EXCEPT ${ODMartQuery}) UNION (${ODMartQuery} EXCEPT ${ODSQuery})|
|BrokerID|BrokerCode|

I tried without the 'AS', just 'BrokerCode BC' to alias the column and it still has the incorrect syntax exception error. If I remove the alias, just 'BrokerCode' then the Fit Test passes. If I run the compare query in MSSSMS, all 3 versions work.

Note that the example I have has the same column name BrokerCode in the second table just to make the union work without aliasing. But I do have other columns that have different column names, thus I need to make aliasing work.

I have just recently downloaded the latest DBFit version and I'm using miscrosoft's sqljdbc4.jar as jdbc driver.

What is wrong with the query and how can column alias be specified in DBFit with MSSQL?

Thanks in advance!

Answer 1

The issue is the queries were not bound by !- ... -! which was required for multi-line queries in DBFit.