TABLE_SUFFIX not recognize the field big Query

Question

what I am doing I just query to table after updating my schema of the table my query is

SELECT
questionAnalysisUnnest.normalisedId AS normalisedId,
FIRST_VALUE(questionAnalysisUnnest.questionText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_quesText,
FIRST_VALUE(quesOptionsUnnest.displayText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId, quesOptionsUnnest.answerNo ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_optionText,
quesOptionsUnnest.answerNo AS ansNo
  FROM
`xxxxxxxxxxxxx.xxxxxxxxxx.1982701_*`
CROSS JOIN
UNNEST(questionAnalysis) AS questionAnalysisUnnest,
UNNEST(questionAnalysisUnnest.options) AS quesOptionsUnnest
 WHERE
_TABLE_SUFFIX IN ('2021_12')
AND formFilled.creationTime.milliSeconds BETWEEN 1616351400000
AND 1616956199999
AND formFilled.form.series.dbInfo.id IN ('HkDt!@ipfeedbackform')
AND IFNULL(questionAnalysisUnnest.questionType,
  'UNKNOWN_QUESTION_TYPE') IN ('CUSTOM_OPTIONS_TYPE'))

error is : Field name options does not exist in STRUCT

but if i dont use table suffix and the table suffix is added in from section then it will work fine

SELECT
questionAnalysisUnnest.normalisedId AS normalisedId,
FIRST_VALUE(questionAnalysisUnnest.questionText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_quesText,
FIRST_VALUE(quesOptionsUnnest.displayText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId, quesOptionsUnnest.answerNo ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_optionText,
quesOptionsUnnest.answerNo AS ansNo
FROM
`xxxxxxxxxxx.xxxxxxxxxxx.1982701_2021_12`
CROSS JOIN
UNNEST(questionAnalysis) AS questionAnalysisUnnest,
UNNEST(questionAnalysisUnnest.options) AS quesOptionsUnnest
WHERE
formFilled.creationTime.milliSeconds BETWEEN 1616351400000
AND 1616956199999
AND formFilled.form.series.dbInfo.id IN ('HkDt!@ipfeedbackform')
AND IFNULL(questionAnalysisUnnest.questionType,
  'UNKNOWN_QUESTION_TYPE') IN ('CUSTOM_OPTIONS_TYPE'))

I did not understand what is happening in this and also please provide some solution to counter this because I want to use table suffix in query

TABLE_SUFFIX not recognize the field big Query

0 Answers