Loading...

EZWriter Merge Data Source - DECLARE @local_variable (v19)

Comments

7 comments

  • Kris Corrigan

    Can EZwriter not handle a 'Select case' after a select query, I have created a EZwriter query, this runs fine in SQL but not within EZwriter.

    I am getting an error message of 'Incorrect syntax near the keyword 'WITH'

    I have no keyword with within the query

    Thanks

    Kris

  • Are you able to paste your query for us to have a look?

     

    It's possible EBMS is adding the WITH (NOLOCK) query hint....

  • Kris Corrigan

    SELECT Category, ISNULL(JAN,0) AS JAN, ISNULL(FEB,0) AS FEB, ISNULL(MAR,0) AS MAR, ISNULL(APR,0) AS APR, ISNULL(MAY,0) AS MAY, ISNULL(JUN,0) AS JUN, ISNULL(JUL,0) AS JUL, ISNULL(AUG,0) AS AUG, ISNULL(SEP,0) AS SEP, ISNULL(OCT,0) AS OCT, ISNULL(NOV,0) AS NOV, ISNULL(DEC,0) AS [DEC] FROM ( SELECT CASE when ev200_evt_category = 'exht' then 'Exhibition' when ev200_evt_category = 'exhc' then 'Exhibition' when ev200_evt_category = 'CONFA' then 'Conference' when ev200_evt_category = 'CONFC' then 'Conference' when ev200_evt_category = 'CONFN' then 'Conference' when ev200_evt_category = 'CONFG' then 'Conference' when ev200_evt_category = 'Ban' then 'Conference' when ev200_evt_category = 'SPTG' then 'Sports & Entertainment' when ev200_evt_category = 'sptc' then 'Sports & Entertainment' when ev200_evt_category = 'ent' then 'Sports & Entertainment' when ev200_evt_category = 'intev' then 'Internal Meetings' else 'No Data' End as Category, LEFT(DATENAME(MONTH,EV200_EVT_START_DATE),3) AS [MONTH], COUNT(1) AS NUMBEROFEVENTS FROM EV200_EVENT_MASTER WHERE YEAR(EV200_EVT_START_DATE) = 2016 AND ev200_evt_category in ('EXHT', 'EXHC', 'CONFA', 'CONFC', 'confn', 'confg', 'ban', 'sptg', 'sptc', 'ent', 'intev') AND EV200_ORG_CODE='10'and EV200_EVT_STATUS in ('08', '24', '28', '30','31') GROUP BY ev200_evt_category, EV200_EVT_START_DATE ) DATATABLE PIVOT ( SUM(NUMBEROFEVENTS) FOR MONTH IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]) ) PIVOTTABLE ORDER BY 'Category'

  • Kris Corrigan

    I have no 'WITH' in the query like I am receiving in the error message,  from reading the 1st paragraph above...

    Within an EZWriter Merge Data Source, only the first SELECT statement will be used to return results.  If you try to create a Data Source that is returning a complex set of results, and you only use a single SQL statement, the corresponding SQL code can also become quite complex.

    I am presuming it in not taking into the Select case, just strange as its runs fine in SQL

     

  • Sean Tame

    You may consider creating it as a Table-valued Function in your Database using the Year as a variable, then your EZwriter could be simplified to 

    SELECT * FROM EVT_CAT_BY_MONTH(2016)

    You take a lot of the work out of EZWriter and leave it to the SQL server which is more likely to handle these exotic syntaxes and as a bonus, you get to use the code where ever you want.

  • Rob Diamant

    Insert this on the first line of the SQL (making sure to include the semicolon) and it will resolve your issue:

    DECLARE @DUMMY bit;

    Even though you do not have an explicit CTE defined, the SQL is parsed and validated prior to passing it to the server engine that is resulting in the error message.

  • Kris Corrigan

    Thanks Rob

    That was also what I got from support and this worked.

Please sign in to leave a comment.