WHERE IN (SELECT) statement breaks query, when there is a WITH statement present-CodePudding

This works perfectly:

SELECT qaer_name, 
       AVG(Minutes(QA_Time)) AS avg_minutes, 
       COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
WHERE qaer_name IN (SELECT Name FROM qaers)
AND tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name

But this breaks with the error: TypeError: Cannot read property '0' of undefined

WITH cosmetic AS (
  SELECT qaer_name, 
         AVG(Minutes(QA_Time)) AS avg_minutes, 
         COUNT(Zendesk_URL) AS num_tickets
  FROM zendeskData
  WHERE qaer_name IN (SELECT Name FROM qaers)
  AND tags LIKE '%cosmetic%'                    
  AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
  GROUP BY qaer_name
)
SELECT * FROM cosmetic

This one doesn't give an error (but doesn't do the filtering I want):

WITH cosmetic AS (
  SELECT qaer_name, 
         AVG(Minutes(QA_Time)) AS avg_minutes, 
         COUNT(Zendesk_URL) AS num_tickets
  FROM zendeskData
  --WHERE qaer_name IN (SELECT Name FROM qaers)
  WHERE tags LIKE '%cosmetic%'                    
  AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
  GROUP BY qaer_name
)
SELECT * FROM cosmetic

But, this one does give the same error: TypeError: Cannot read property '0' of undefined

WITH cosmetic AS (
  SELECT qaer_name, 
         AVG(Minutes(QA_Time)) AS avg_minutes, 
         COUNT(Zendesk_URL) AS num_tickets
  FROM zendeskData
  WHERE tags LIKE '%cosmetic%'                    
  AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
  GROUP BY qaer_name
)
SELECT * 
FROM cosmetic
WHERE qaer_name IN (SELECT Name FROM qaers)

Why does the WHERE IN (SELECT) statement break it only when there is a WITH statement present?

Edit: This also gives the same error.

let data = Database.alasql(`
  ;WITH cosmetic AS (
    SELECT qaer_name, 
          AVG(Minutes(QA_Time)) AS avg_minutes, 
          COUNT(Zendesk_URL) AS num_tickets
    FROM zendeskData z
    WHERE EXISTS (SELECT 1 FROM qaers q WHERE z.qaer_name = q.Name) 
    AND tags LIKE '%cosmetic%'                    
    AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
    GROUP BY qaer_name
  )
  SELECT * 
  FROM cosmetic
`);
Logger.log(data);

CodePudding user response：

I do not know why my original method didn't work, but I have devised a work around.

My alternative to making a qaers table, and then selecting that table in a future query, is to make the table a JS array and then pass that into the query formatting it as a list.

This gives the proper result I am looking for, because it allows me to use the list of names and a WITH statement, which will be necessary as I make the query more complicated.

let qaers = Database.alasql(`
  SELECT MATRIX Name 
  FROM annotatorData 
  WHERE Pod_Number = 'QA'
`);

let data = Database.alasql(`
  WITH cosmetic AS (
    SELECT qaer_name, 
          AVG(Minutes(QA_Time)) AS avg_minutes, 
          COUNT(Zendesk_URL) AS num_tickets
    FROM zendeskData
    WHERE qaer_name IN ('${qaers.join("','")}')
    AND tags LIKE '%cosmetic%'                    
    AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
    GROUP BY qaer_name
  )
  SELECT * 
  FROM cosmetic
`)