Thank you for the sample. I don't think that we'll implement support of such queries in the near future, but may be it's a good idea for the future versions.
SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.FULLNAME AS Benutzer, COUNT(DISTINCT TBL_CONTACT_HISTORY.CONTACTID) AS right_Kontaktanzahl, TBL_CONTACT.LASTNAME AS hide_Nachname FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_USER ON TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID INNER JOIN TBL_CONTACT ON TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_CONTACT.FULLNAME, TBL_CONTACT.LASTNAME ORDER BY TBL_CONTACT.LASTNAME} AS Null_Benutzer APPEND (( SHAPE {SELECT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS Protokolltyp, COUNT(DISTINCT TBL_HISTORY.HISTORYID) AS [right_Anzahl Protokolltyp], MAX(TBL_HISTORY.STARTTIME) AS right_formatlocaldate_Zuletzt FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_ACTIVITYTYPE.NAME ORDER BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige')} AS Eins_Protokolltypen APPEND (( SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS hide_ActivityTypeName, TBL_CONTACT_HISTORY.CONTACTID AS hide_ContactID, TBL_CONTACT.FULLNAME AS Kontaktname, TBL_CONTACT.CONTACTID AS showactcontact_Kontakt, TBL_CONTACT.COMPANYNAME AS Firmenname, TBL_CONTACT.CATEGORY AS Status1, TBL_CONTACT.DEPARTMENT AS Status2 FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) ORDER BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige'), TBL_CONTACT.COMPANYNAME} AS Zwei_Kontakte APPEND (( SHAPE {SELECT TBL_HISTORY.STARTTIME AS hide_Datum, TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.CONTACTID AS hide_ContactID, TBL_HISTORY.STARTTIME AS formatlocaldate_Datumseintrag, '<b>' + ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') + '</b>' AS Typ, TBL_HISTORYTYPE.NAME AS Ergebnis, TBL_HISTORY.REGARDING AS Betreff, TBL_HISTORY.HISTORYID AS hide_HistoryID FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) ORDER BY TBL_HISTORY.STARTTIME DESC} AS Drei_Protokolle APPEND ({SELECT DISTINCT TBL_HISTORY.STARTTIME AS hide_Starttime, CONVERT(nvarchar(MAX),TBL_HISTORY.DETAILS) AS rtf2text_Details, TBL_HISTORY.HISTORYID AS hide_HistoryID FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_COMPANY_HISTORY ON TBL_HISTORY.HISTORYID = TBL_COMPANY_HISTORY.HISTORYID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.ISPRIVATE = 0) AND (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (DATALENGTH(TBL_HISTORY.DETAILS) > 0) AND (TBL_HISTORY.STARTTIME <= GETDATE()) ORDER BY TBL_HISTORY.STARTTIME DESC} AS Vier_Details RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS Vier_Details) AS Drei_Protokolle RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','hide_ContactID' TO 'hide_ContactID') AS Drei_Protokolle, MAX(Drei_Protokolle.'hide_Datum') AS right_formatlocaldate_Zuletzt, COUNT(Drei_Protokolle.'hide_HistoryID') AS right_ProtokolleGesamt) AS Zwei_Kontakte RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','Protokolltyp' TO 'hide_ActivityTypeName') AS Zwei_Kontakte, COUNT(Zwei_Kontakte.'hide_ManageUserID') AS right_Betroffene_Kontakte) AS Eins_Protokolltypen RELATE 'hide_ManageUserID' TO 'hide_ManageUserID') AS Eins_Protokolltypen, SUM(Eins_Protokolltypen.'right_Anzahl Protokolltyp') AS right_format0_Protokollanzahl, MAX(Eins_Protokolltypen.'right_formatlocaldate_Zuletzt') AS right_formatlocaldate_Letztes_Protokoll
Hereis an attemptby meto decomposethe querywithan regular expression (.NET, ignore case):
here is an example of an shape query:
SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.FULLNAME AS Benutzer, COUNT(DISTINCT TBL_CONTACT_HISTORY.CONTACTID) AS right_Kontaktanzahl, TBL_CONTACT.LASTNAME AS hide_Nachname FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_USER ON TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID INNER JOIN TBL_CONTACT ON TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_CONTACT.FULLNAME, TBL_CONTACT.LASTNAME ORDER BY TBL_CONTACT.LASTNAME} AS Null_Benutzer APPEND (( SHAPE {SELECT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS Protokolltyp, COUNT(DISTINCT TBL_HISTORY.HISTORYID) AS [right_Anzahl Protokolltyp], MAX(TBL_HISTORY.STARTTIME) AS right_formatlocaldate_Zuletzt FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_ACTIVITYTYPE.NAME ORDER BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige')} AS Eins_Protokolltypen APPEND (( SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS hide_ActivityTypeName, TBL_CONTACT_HISTORY.CONTACTID AS hide_ContactID, TBL_CONTACT.FULLNAME AS Kontaktname, TBL_CONTACT.CONTACTID AS showactcontact_Kontakt, TBL_CONTACT.COMPANYNAME AS Firmenname, TBL_CONTACT.CATEGORY AS Status1, TBL_CONTACT.DEPARTMENT AS Status2 FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) ORDER BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige'), TBL_CONTACT.COMPANYNAME} AS Zwei_Kontakte APPEND (( SHAPE {SELECT TBL_HISTORY.STARTTIME AS hide_Datum, TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.CONTACTID AS hide_ContactID, TBL_HISTORY.STARTTIME AS formatlocaldate_Datumseintrag, '<b>' + ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') + '</b>' AS Typ, TBL_HISTORYTYPE.NAME AS Ergebnis, TBL_HISTORY.REGARDING AS Betreff, TBL_HISTORY.HISTORYID AS hide_HistoryID FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (TBL_HISTORY.STARTTIME <= GETDATE()) AND (TBL_HISTORY.ISPRIVATE = 0) ORDER BY TBL_HISTORY.STARTTIME DESC} AS Drei_Protokolle APPEND ({SELECT DISTINCT TBL_HISTORY.STARTTIME AS hide_Starttime, CONVERT(nvarchar(MAX),TBL_HISTORY.DETAILS) AS rtf2text_Details, TBL_HISTORY.HISTORYID AS hide_HistoryID FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_COMPANY_HISTORY ON TBL_HISTORY.HISTORYID = TBL_COMPANY_HISTORY.HISTORYID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.ISPRIVATE = 0) AND (TBL_HISTORY.STARTTIME >= GETDATE() - 30) AND (DATALENGTH(TBL_HISTORY.DETAILS) > 0) AND (TBL_HISTORY.STARTTIME <= GETDATE()) ORDER BY TBL_HISTORY.STARTTIME DESC} AS Vier_Details RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS Vier_Details) AS Drei_Protokolle RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','hide_ContactID' TO 'hide_ContactID') AS Drei_Protokolle, MAX(Drei_Protokolle.'hide_Datum') AS right_formatlocaldate_Zuletzt, COUNT(Drei_Protokolle.'hide_HistoryID') AS right_ProtokolleGesamt) AS Zwei_Kontakte RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','Protokolltyp' TO 'hide_ActivityTypeName') AS Zwei_Kontakte, COUNT(Zwei_Kontakte.'hide_ManageUserID') AS right_Betroffene_Kontakte) AS Eins_Protokolltypen RELATE 'hide_ManageUserID' TO 'hide_ManageUserID') AS Eins_Protokolltypen, SUM(Eins_Protokolltypen.'right_Anzahl Protokolltyp') AS right_format0_Protokollanzahl, MAX(Eins_Protokolltypen.'right_formatlocaldate_Zuletzt') AS right_formatlocaldate_Letztes_Protokoll
Here is an attempt by me to decompose the query with an regular expression (.NET, ignore case):
shape\s*\{\s*(?<Query>select\s+[^\}]+?)\s*}\s*as\s+(?<AliasQuery>\w+)\s+append\s*\(\s*(?<SubQuery>.*)\s+Relate\s+(?<Relations>[^\)]*)\)\s+as\s+(?<AliasSubQuery>\w+)(,(?<Aggregates>[^,]*))*
May be this can help you.
Best regards
Robert