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.
One way to make your SQL code easier to understand is to use SQL Comments (see https://supportcenter.ungerboeck.com/entries/33351353), while another way is to break your SQL code up into manageable chunks. One way to achieve this is to use DECLARE statements to create local variables.
The following example returns a list of events for this year, and for each event it displays the percentage of the total number of (actual) attendance within that year. At the top of the code are a few DECLARE statements that temporarily store some values which are used later within the SQL code. It also demonstrates that you can use the DECLARE statement to create a table to temporarily store information.
DECLARE
@THIS_YEAR AS INT,
@YEAR_START_DATE AS DATETIME,
@YEAR_END_DATE AS DATETIME,
@TOTAL_ATTENDANCE_THIS_YEAR INT
DECLARE
@PERCENTAGE_OF_TOTAL_ATTENDANCE_PER_EVENT AS TABLE
(
EV200_EVT_DESC VARCHAR(150),
EV200_ACT_ATTEND INT,
PERCENTAGE NUMERIC(12,2)
)
/* DETERMINE THE CURRENT YEAR */
SET @THIS_YEAR = YEAR(GETDATE())
/* DETERMINE THE START DATE - MIDNIGHT OF JAN 1ST OF THIS YEAR */
SET @YEAR_START_DATE = CONVERT(DATETIME, CONVERT(CHAR(4), @THIS_YEAR)+'-01-01')
/* DETERMINE THE END DATE - MIDNIGHT OF JAN 1ST OF NEXT YEAR */
SET @YEAR_END_DATE = CONVERT(DATETIME, CONVERT(CHAR(4), @THIS_YEAR + 1)+'-01-01')
/* DETERMINE THE TOTAL ATTENDANCE FOR ALL EVENTS THIS YEAR */
SET @TOTAL_ATTENDANCE_THIS_YEAR =
(
SELECT SUM(EV200_ACT_ATTEND)
FROM EV200_EVENT_MASTER
WHERE EV200_EVT_START_DATE < @YEAR_END_DATE
AND EV200_EVT_END_DATE >= @YEAR_START_DATE
)
/* GET ALL EVENTS THIS YEAR, THEIR ATTENDANCE, AND CALCULATE THE PERCENTAGE OF TOTAL ATTENDANCE */
INSERT INTO @PERCENTAGE_OF_TOTAL_ATTENDANCE_PER_EVENT (EV200_EVT_DESC, EV200_ACT_ATTEND, PERCENTAGE)
SELECT
EV200_EVT_DESC,
EV200_ACT_ATTEND,
CONVERT(NUMERIC(12,2), EV200_ACT_ATTEND) / CONVERT(NUMERIC(12,2), @TOTAL_ATTENDANCE_THIS_YEAR) * 100 AS PERCENTAGE
FROM EV200_EVENT_MASTER
WHERE EV200_EVT_START_DATE < @YEAR_END_DATE
AND EV200_EVT_END_DATE >= @YEAR_START_DATE
/* RETURN THE RESULTS, ORDERED BY THE HIGHEST PERCENTAGE FIRST, AND ADD A '%' SYMBOL TO THE PERCENTAGE */
SELECT
EV200_EVT_DESC,
EV200_ACT_ATTEND,
CONVERT(VARCHAR(6), PERCENTAGE) + '%' AS PERCENTAGE
FROM @PERCENTAGE_OF_TOTAL_ATTENDANCE_PER_EVENT
ORDER BY PERCENTAGE DESC
The above example could also be achieved using more concise SQL code, however it does illustrate that the DECLARE statement can be useful to break larger SQL code up into manageable chunks.
One thing to be aware of is that any local variables that you create using the DECLARE statement cannot start with, or equal, one of the standard EZWriter Data Source Parameters. To see a list of the standard EZWriter Data Source Parameters, open the Add/Edit EZWriter Merge Data Source window (v19 Main Menu > Query & Reporting > EZWriter Merge Data Sources > Options menu > Add), click the 'Add Data Source Parameter' icon in the toolbar (the icon with three little red tick marks), right-click and select 'Insert Standard Parameter'.
As an example, @EVENT is a standard EZWriter Data Source Parameter, and therefore you cannot use this as your local variable (DECLARE @EVENT VARCHAR(150) will not work), and you cannot use it at the start of any local variables either (DECLARE @EVENT_ATTENDANCE INT will not work).
If you do try to declare one of the standard EZWriter Data Source Parameters, upon clicking Apply or OK you will receive an error that states "Incorrect syntax near '999999999'".
To avoid this situation, you can use an abbreviation (DECLARE @EVT_ATTENDANCE INT), or use a prefix before each of your local variables (DECLARE @MY_EVENT_ATTENDANCE INT).
Further information for SQL DECLARE @local_variable can be found here: http://technet.microsoft.com/en-us/library/ms188927.aspx
Comments
7 comments
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
0 upvotes
Are you able to paste your query for us to have a look?
It's possible EBMS is adding the WITH (NOLOCK) query hint....
0 upvotes
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'
0 upvotes
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
0 upvotes
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.
0 upvotes
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.
0 upvotes
Thanks Rob
That was also what I got from support and this worked.
0 upvotes
Please sign in to leave a comment.