Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Converting to script |
Mon, Sep 29 2008 2:24 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Sep 29 2008 3:48 PM | Permanent Link |
"David Cornelius" | 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 |
Tue, Sep 30 2008 2:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 30 2008 5:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 30 2008 11:20 AM | Permanent Link |
"David Cornelius" | > 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 |
Wed, Oct 1 2008 9:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |