formatting sql for table transformer

Adam Petrie_chbc0n

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' + " ";