Its beaten me - I get

ElevateDB Error #700 An error was found in the statement at line 103 and column 19 (Expected end of expression but instead found WITH)

or with a slightly different variant

ElevateDB Error #700 An error was found in the statement at line 102 and column 19 (Expected ( but instead found SELECT)

Both of them on EXECUTE IMMEDIATE Creator;

Can someone tell me what I'm doing wrong (sorry its so long)

SCRIPT
BEGIN    
DECLARE tblname VARCHAR;
DECLARE ProjectID INTEGER;
DECLARE UserID INTEGER;
DECLARE Mode VARCHAR;

DECLARE Creator VARCHAR;


SET tblname = 'Junk';
SET ProjectID = 3;
SET UserID = 1;
SET Mode = 'Project';

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE "'+tblName+'"';
EXCEPTION
END;

SET Creator = 'CREATE TABLE '+tblName+ 'AS ';
SET Creator = 'SELECT
                       _CallID,
                       _fkContacts,
                       _fkCompanies,
                       _fkSites,
                       _Priority,
                       _LastResult,
                       RAND (RANGE 999999) AS _Random,
                       _DateAdded,
                       _Done,
                       _IsCandidate,
                       J._Secretary,
                       J._UnlistedCompany,
                       J._JobTitle,
                       C._Name AS _ListedCompany,
                       M._MktDesc ,
                       O._Description AS _OrgDesc,
                       SG._Description AS _SICGroupDesc,
                       SC._Description AS _SICCodeDesc,
                       COALESCE(J._MainPhone, Site._Switchboard) AS _MainPhone,
                       COALESCE(W._Forename,'''')+'' ''+COALESCE(W._Surname,'''') AS _FullName,
                       W._Title,
                       W._Forename,
                       W._Surname,
                       W._HomePhone,
                       W._Mobile,
                       W._HomeEMail,
                       W._Status,
                       IFNULL(W._LatestCV THEN FALSE ELSE TRUE) AS _HasCV,
                       J._EMail,
                       J._fkJobCodes,
                       IFNULL(J._Left THEN FALSE ELSE NULL) AS _StillEmployed,
                       COALESCE(C._Name,J._UnlistedCompany) AS _CompanyName,
                       Site._Town,
                       Site._County,
                       Site._Country,
                       Site._SiteName,
                       C._fkMarkets,
                       C._fkOrgType,
                       Site._Switchboard,
                       S._Staff AS _Staffing,
                       T._Turnover AS _Turnover,
                       C._fkSICGroup,
                       C._fkSICCode,
                       C._Status AS _CompanyStatus,
                       C._Website,
                       IFNULL(P._Name THEN '''' ELSE ''Parent: ''+ P._Name) AS _Parent';

SET Creator = Creator + ' FROM Calls Calls';
SET Creator = Creator + ' LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID';
SET Creator = Creator + ' LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID';
SET Creator = Creator + ' LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID';
SET Creator = Creator + ' LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band';
SET Creator = Creator + ' LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band';
SET Creator = Creator + ' LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID';
SET Creator = Creator + ' LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID';
SET Creator = Creator + ' LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID';
SET Creator = Creator + ' LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID';
SET Creator = Creator + ' LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID';
SET Creator = Creator + ' LEFT OUTER JOIN Sites Site ON  _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID';

SET Creator = Creator + ' WHERE ';


IF Mode = 'Callbacks' THEN
SET Creator = Creator + ' _fkUsers = '+ CAST(UserID AS VARCHAR) + '_CallBack';
END IF;
IF Mode = 'Alarms' THEN
SET Creator = Creator + ' _fkUsers = '+ CAST(UserID AS VARCHAR) + ' _HideAlarm IS NOT NULL';
END IF;
IF Mode = 'Project' THEN
SET Creator = Creator + ' _fkProjects = '+ CAST(ProjectID AS VARCHAR);
SET Creator = Creator +  ' AND ';
SET Creator = Creator +  ' (_HideSet = FALSE OR _HideAlarm IS NULL OR _HideAlarm <= CURRENT_DATE)';
SET Creator = Creator +  ' AND ';
SET Creator = Creator +  ' (C._Name IS NOT NULL OR W._Surname IS NOT NULL)';
END IF;


SET Creator = Creator + ' WITH DATA';

EXECUTE IMMEDIATE Creator;

END


Roy Lambert

Line 19 and 20 (starting after the SCRIPT/BEGIN), you have two
 SET Creator = ...
statements.  I imagine the second one should be
 SET Creator = Creator + ...

Currently, the resulting Creator string is just a select statement, so
the clause " WITH DATA" doesn't make sense.

--
David Cornelius
CorneliusConcepts.com

David

>Line 19 and 20 (starting after the SCRIPT/BEGIN), you have two
> SET Creator = ...
>statements. I imagine the second one should be
> SET Creator = Creator + ...
>
>Currently, the resulting Creator string is just a select statement, so
>the clause " WITH DATA" doesn't make sense.

Good try - my cock-up. I forgot that I'd altered that bit. With the Creator + reinstated I get the Expected ( but instead found SELECT error.

The maddening part is that held in a stringlist container using StringReplace  rather than parameters and executing a single statement at a time it works. Trying to convert to a script isn't working.

Roy Lambert

Got there in the end. The script debugger is sort of handy. The error messages, as I've noted elsewhere, can be misleading rather than helpful when executing a long sql statement.


Maybe if Tim can't pin it down to the level of "you've left a comma out after column name xxx" it should be altered to "somewhere in this statement you've got something wrong. I think it is xxxxx but please review the whole statement" at least then I might look at the whole statement rather than looking in the wrong place.

This is not a serious criticism of Tim - trying to identify automatically the missing comma or whatever in an sql statement must be near to impossible; I know how long it took me but I would have thought these modern chips were far better than my aging brain.

Roy Lambert

> Got there in the end. The script debugger is sort of handy. The error
> messages, as I've noted elsewhere, can be misleading rather than
> helpful when executing a long sql statement.

"Sort of handy"  LOL!  They're down-right VERY COOL!

> Maybe if Tim can't pin it down to the level of "you've left a comma
> out after column name xxx" it should be altered to "somewhere in this
> statement you've got something wrong. I think it is xxxxx but please
> review the whole statement" at least then I might look at the
> whole statement rather than looking in the wrong place.

This is the bane of the programmer's existance.  Every programming
language, script, and application I've ever run has obtuse error
messages.  Yes, even the thoughtful messages I put in my own programs
can sometimes be misleading.  They point to the correct symptom, but
it's up to us to be just a little smarter than the computer and take it
as a challenge to see where the root of the problem might be coming
from.

In this case, a whole bunch of statements were being sent to the
execution engine at once.  How was it supposed to know what kind of
message would be most helpful to you?  ALWAYS look at the whole
statement!  

Glad you figured it out.

--
David Cornelius
CorneliusConcepts.com

David

>In this case, a whole bunch of statements were being sent to the
>execution engine at once. How was it supposed to know what kind of
>message would be most helpful to you?

Its easy - its the one that says "I know what you typed but I also know what you want so type this instead, on second thoughts let me type it for you so you get it right" What's so difficult about that?

>ALWAYS look at the whole
>statement!

I am learning - a couple more like this and I should have the new habits engraved into my brain.

Roy Lambert