I was using this sql

SELECT
C._ID,
Site._SiteID,
C._Name,
Site._Switchboard,
CAST(RCF(_PostCode,_Town,', ')  AS VARCHAR(50))AS _Where,
CAST(RCF('T/O: ' +  T._Turnover,'Staff: ' + S._Staff,', ') AS VARCHAR(75)) AS _Size,
CAST(RCF(IFNULL(M._MktDesc,IFNULL(SC._Description,SG._Description,SC._Description),M._MktDesc),O._Description,', ') AS VARCHAR(102)) AS _CompanyType ,
(SELECT COUNT(_fkContacts) FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID) AS _EiP,
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies AND Calls._fkContacts IS NULL AS _CallID
FROM
Sites Site
JOIN Companies C ON Site._fkCompanies = C._ID
LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
WHERE
_SiteID IN (SELECT _fkSites FROM Calls WHERE _fkProjects = :ProjectID)

as the code to create a temporary table.

I realised that _CallID was being set as NULL where there was a contact linked to the call. What I want is to give the _CallID of the call without a contact linked but if there isn't a call without a contact linked return the _CallID of the any of the calls for the company. I altered a line of the SQL from

(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies AND Calls._fkContacts IS NULL AS _CallID

to

(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies ORDER BY _fkContacts RANGE 1 TO 1) AS _CallID

Works great as a query but bombs as a CREATE TEMPORARY TABLE statement with

ElevateDB Error #700 An error was found in the statement at line 12 and column 130 (Expected ) but instead found ORDER)

Can anyone (yes John I'm looking at you) suggest an alternative?

Roy Lambert

Quick update - I tried


IFNULL
(
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies AND Calls._fkContacts IS NULL)
THEN
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies RANGE 1 TO 1)
ELSE
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies = Sites._fkCompanies AND Calls._fkContacts IS NULL)

)
AS _CallID

But I'm not allowed to use RANGE either

Roy Lambert

Solution so far - major lateral thinking - switch the main table


SELECT
C._ID,
_CallID,
_fkSites,
C._Name,
B._Switchboard,
CAST(RCF(B._PostCode,B._Town,', ')  AS VARCHAR(50))AS _Where,
CAST(RCF('T/O: ' +  T._Turnover,'Staff: ' + S._Staff,', ') AS VARCHAR(75)) AS _Size,
CAST(RCF(IFNULL(M._MktDesc,IFNULL(SC._Description,SG._Description,SC._Description),M._MktDesc),O._Description,', ') AS VARCHAR(102)) AS _CompanyType ,
(SELECT COUNT(_fkContacts) FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID) AS _EiP,
_fkContacts
FROM
Calls
JOIN Companies C ON Calls._fkCompanies = C._ID
LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
LEFT OUTER JOIN Sites B ON Calls._fkSites = B._SiteID
WHERE
_CallID IN (SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND _fkCompanies IS NOT NULL GROUP BY _fkCompanies)


My concern is that I'm relying on the grouping of the table to sort null _fkContacts above those with a value. I shall email Tim directly on that.

Roy Lambert

Roy

I see you have found an alternative solution but does the following produce the (a?) correct result.
IFNULL
(
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies =
Sites._fkCompanies AND Calls._fkContacts IS NULL)
THEN
(SELECT MIN(_CallID) FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies
= Sites._fkCompanies)
ELSE
(SELECT _CallID FROM Calls WHERE _fkProjects = :ProjectID AND Calls._fkSites = Sites._SiteID AND Calls._fkCompanies =
Sites._fkCompanies AND Calls._fkContacts IS NULL)
)
AS _CallID

John

Roy

You could use your original query WITH NO DATA and use the mofified version (order by/range) as an INSERT INTO

John

John


The query produces a result, and it can be used to generate a temporary table and the results are the same as my new solution. The only difference is my new solution is about 6 times faster (and probably a bit easier to read

I like the use of MIN where I was using ORDER BY and RANGE to achieve the same result.

All I need to do now is await Tim's verdict as to how safe my new approach is.

Roy Lambert

John


>You could use your original query WITH NO DATA and use the mofified version (order by/range) as an INSERT INTO

You have a sneaky mind - I love it.

Roy Lambert [Team Elevate]