Confluence Gantt chart problem with SQL

Question

The data was being extracted and structured for the use of Gantt chart. Everything was working well and, it happened many times that the confluence SQL in Table transformator starts to show errors out of no where suddenly.

Mostly "y.substr error". (but before it was working properly!!!)

Some time the code works and some time it doesn't!!!!

Thanks

Katerina Kovriga {Stiltsoft} · Answer

Please create a support ticket with the screenshots of the source tables and repeat the SQL queries there as well.

And add some words what exactly do you want to achieve with the queries: for example, to return a specific part of the string from the specific column. You have "xxxxyyy", the desired result is "xxxx". Because it seems that the first query could be simplified and used without cycles and conditions.

The same concerns the comparison of the dates from the second query.

Gohil Jay · Answer

Hi @Katerina Kovriga _Stiltsoft_ Yes the data comes from confluence dynamic pages (they are continuously updated by employees). Also Idk sometimes it works and sometimes it shows error, unfortunately I can not upload a screenshot but I can add the SQL query as a reference, --> First Code who has error every time, SELECT T1.'Overview',T1.'App-Status',T1.'ECU-SW', T1.'Finales PP', CASE WHEN T1.'ECU-SW' THEN SUBSTR(T1.'ECU-SW',1,11) END AS 'ECU-SW', case WHEN T1.'Finales PP' THEN SUBSTR(T1.'Finales PP',1,11) END AS 'Finales PP' FROM T --> Second code, (Sometimes not comparing the dates) SELECT *, CASE WHEN FORMATDATE(DATE(FORMATDATE(T1.'ECU-SW'))) LIKE FORMATDATE(DATE(FORMATDATE("01.01.2021"))) THEN NULL WHEN T1.'ECU-SW' < FORMATDATE("12.06.2021") then FORMATDATE("12.06.2021") Else FORMATDATE(T1.'ECU-SW') END AS 'ECU IN', CASE WHEN FORMATDATE(DATE(FORMATDATE(T1.'ECU-SW'))) = FORMATDATE(DATE(FORMATDATE("01.01.2021"))) THEN NULL WHEN T1.'ECU-SW' < FORMATDATE("12.06.2021") then FORMATDATE(ADDDATE("12.06.2021", INTERVAL 13 DAY)) Else FORMATDATE(DATE(ADDDATE(T1.'ECU-SW', INTERVAL 13 DAY))) END as 'ECU NEW' FROM T1

Katerina Kovriga {Stiltsoft} · Answer

Hi @Gohil Jay ,

I assume that you are talking about our Table Filter and Charts for Confluence app.

Please clarify which data you are using for the Table Transformer macro - it seems that you try to pull it from the dynamic Jira Issues or Page Properties Report macros and something is wrong with the incoming table (empty field, wrong format or smth).

Maybe it will be better to create a support ticket: it will be confidential and you will be able to attach the screenshots of the source tables and the SQL query that you use to modify them.

By the way, we are happy to introduce our new macro – Table Spreadsheet. This macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.