formatting sql for table transformer

[Adam Petrie]

I'm out of ideas and looking for someone that can help me by checking my formatting and helping me find why this sql isn't being accepted by Table Transformer.

First a short explanation of what's going on. 

Explanation

1. Common Table Expressions (CTEs):

   • aa_notdones: Counts the number of tasks not marked as "Done" for each project.
   • bb_dones: Counts the number of tasks marked as "Done" for each project.

2. Regular Expressions:

   • REGEXP_SUBSTR is used to extract the Project ID from the Epic Link.

3. Join Operation:

   • The main SELECT statement joins the two CTEs on the Project ID to merge counts of "Not Done" and "Done" tasks per project.

Here's my SQL

WITH aa_notdones AS ( SELECT COUNT(T1.'Key') AS 'Not Done', MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href="(.?)"")->1, "OPIF-\d{2,}","g") AS 'Project ID' FROM T1 WHERE T1.'Status' <> "Done" GROUP BY MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href="(.?)"")->1, "OPIF-\d{2,}","g") ), bb_dones AS ( SELECT COUNT(T1.'Key') AS 'Done', MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href="(.?)"")->1, "OPIF-\d{2,}","g") AS 'Project ID' FROM T1 WHERE T1.'Status' = "Done" GROUP BY MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href="(.?)"")->1, "OPIF-\d{2,}","g")) SELECT aa_notdones.*, bb_dones.* FROM aa_notdones JOIN bb_dones ON aa_notdones.'Project ID' = bb_dones.'Project ID';

I think the error is indicating a missing or extra comma. I've checked and re-checked and tried removing and adding commas and semi-colon in places all without success. Hoping someone out there has a fresh set of eyes and the experience to sort me out.

Thanks in advance.

[Stiltsoft support]

Hi @Adam Petrie ,

Seems that you are using our Table Filter, Charts & Spreadsheets for Confluence app that provides the Table Transformer macro mentioned above.

If this is the case, you may try the following variant of the SQL query:

WITH aa_notdones AS ( SELECT COUNT(T1.'Key') AS 'Not Done',
MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{2,}","g") AS 'Project ID'
FROM T1
WHERE T1.'Status' <> "Done"
GROUP BY MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{2,}","g") ),
bb_dones AS ( SELECT COUNT(T1.'Key') AS 'Done',
MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{2,}","g") AS 'Project ID'
FROM T1 WHERE T1.'Status' = "Done"
GROUP BY MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{2,}","g"))
SELECT *
FROM aa_notdones JOIN bb_dones ON aa_notdones.'Project ID' + " " = bb_dones.'Project ID' + " ";

[Stiltsoft support]

The last query string after the FROM aa_notdones JOIN bb_dones ON... may be also changed as:

• (aa_notdones.'Project ID'::string) = (bb_dones.'Project ID'::string);
• TEXT(aa_notdones.'Project ID') = TEXT(bb_dones.'Project ID');

These variants should be working fine as well.