Table Transformer SQL issue with CASE WHEN and condition

[renaud staessens]

Hi community i struggled a lot to try to find a root cause with no success. I try to count number of test failed , number of test succees for requirements.

I m using table transfomer to do some sl request on a table 

I have the following request :SELECT T1.'Requirement',

SELECT T1.'Requirement',

 COUNT(DISTINCT 'Tests') AS 'Number of Tests',

 COUNT(DISTINCT('Bugs')) AS 'NbBugs',

 COUNT(DISTINCT CASE WHEN COALESCE('T1.TestStatut','' ) AND INSTR('T1.TestStatut', "FAIL")>0 then 'T1.TestStatut' END) AS 'Failed' // i m searching for (FAIL TEST-ID string) to count number of distinct failed tests    

FROM T1 

GROUP BY T1.'Requirement'

If i have the and clause then i 'm facing athis error : TypeError: Cannot read properties of undefined (reading 'indexOf').

So i thought it was due to the fact that sometimes TestStatus can we empty (or null)  reason why i added the COALESCE('T1.TestStatut','' )  clause to have always something not empty in th teststaus .

Now if i remove the INSTR() function i don't have the error ...

original table looks like this 

Requirement, Bug-ID, Tests,Bugs ,TestStatus (FAIL/PASS/TODO)

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hi @renaud staessens,

Please use the auto complete hints given by the Table Transformer macro while typing the SQL query. The first thing that I saw were the 'T1.TestStatut' parts. It is not correct in general: T1.’TestStatus’ is a correct variant (the quotes shouldn’t include the T1. part and the column name should be identical to the one that you really have in your table).
Now please try the following SQL query for the case:



SELECT
'Requirement',
COUNT(DISTINCT 'Tests') AS 'Number of Tests',
COUNT(DISTINCT('Bugs')) AS 'Number of Bugs',
COUNT(DISTINCT 'Failed Tests') AS 'Number of Failed Tests'
FROM(
SELECT *,
CASE WHEN 'TestStatus' IS "FAIL"
THEN 'Tests'
END
AS 'Failed Tests'
FROM T1)
GROUP BY 'Requirement'


Hope it helps your case.

[renaud staessens]

thank you so much it does work !