0

I have been trying to run a query that will return a count of how many times each unique word appears in a column of titles. I tried the option in THIS POST but consistently got an error message:

"Table "word_count" must be qualified with a dataset (e.g. dataset.table)."

word_count is a CTE (example below) so I am not sure what the issue is. Any help is appreciated.

WITH word_count AS (
  SELECT Title,
         1,
         INSTR(Title, ' ', 1 )
  FROM   `circulation2023.top_food`
UNION ALL
  SELECT Title,
         end_pos + 1,
         INSTR(Title, ' ', end_pos + 1 )
  FROM   word_count
  WHERE  end_pos > 0
),
words AS (
SELECT CASE end_pos
       WHEN 0
       THEN SUBSTR(Title, start_pos )
       ELSE SUBSTR(Title, start_pos, end_pos - start_pos )
       END
FROM   word_count
)
SELECT word,
       COUNT(*) AS frequency
FROM   words
GROUP BY
       word
ORDER BY
       frequency desc, word;
DJKok
  • 1

1 Answers1

0

As the error suggests you are missing the dataset part of the table reference. In the case of. FROM `circulation2023.top_food this would be the circulation2023 part.

Should look like this

SELECT Title,
         1,
         INSTR(Title, ' ', 1 )
  FROM   `circulation2023.top_food`
UNION ALL
  SELECT Title,
         end_pos + 1,
         INSTR(Title, ' ', end_pos + 1 )
  FROM   `dataset.word_count`
  WHERE  end_pos > 0

Also, it is recommended to avoid circular references to tables, eg if the source table is word_count do not also name the CTE word_count. At the very least it makes it unclear what you are trying to achieve.

jimiclapton
  • 775
  • 3
  • 14
  • 42
  • But there is no word_count in the source table. word_count is only a CTE. There is no dataset to reference. – DJKok Aug 17 '23 at 16:59
  • The intention is for `word_count` to be a recursive CTE but the error leads me to believe that the query is treating that reference as through it is a regular table and therefore needs to be qualified with a dataset. – DJKok Aug 17 '23 at 17:11