halifaxdal (4/12/2014)
Thank you guys so much and especially to Jeff for the analysis.The one with function was accepted by the Dundas and the other one with DECLARE was rejected as DECLARE is not allowed in building Virtual Table in Dundas Dashboard.
And, HUGE THANKS TO YOUR FINDING OF THE TARGETCLOSUREDATE AND FISCAL YEAR
Good thing is the original sp is not in production yet.
Yes I know CTE although I don't use it often, in Dundas I get this error for script:
WITH test (ID, Ref, Title )
AS
-- Define the CTE query.
(
SELECT ID, Ref, Title
FROM Document
WHERE DocStatus = 'Active' and DocType = 1
)
-- Define the outer query referencing the CTE name.
SELECT *
FROM test
Incorrect syntax near the keyword 'WITH'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ')'.
The CTE is wrong for the problem we're working on. That's not what's causing the error though. Look at the error. I there code before the CTE? Such code would be known as the "previous statement". That's a hint.
And what the heck is "Dundas"??? If it's an application, I can't believe that such a thing wouldn't accept a DECLARE in a stored procedure (and we haven't yet finished converting this to a stored procedure).
--Jeff Moden
Change is inevitable... Change for the better is not.