BigQuery Creating View Collation Error

[ken-slabs]

Hello, I am trying to recreate a view in BigQuery but is receiving the below error after changing the dataset to be case insensitive "und:ci" and recreating the source table schemas.

Error: Creating VIEW with collation on the output column is not supported

[anjelab]

Can you share your query on creating VIEW?

[ken-slabs]

Hi @anjelab, here is a sample view creation:

create or replace view prod_mssql_dbo.vw_testview

as

(

  select   

    pages.companyid,  

    pages.pageid,

    companies.companyname,

    pages.pagename

  from prod_mssql_dbo.pages

    join prod_mssql_dbo.companies on companies.companyid = pages.companyid

)

[anjelab]

This seems to be a limitation. Quoting this Bigquery documentation:

A view with collated output columns is not supported.

---

You may file a feature request for this. But as a workaround, you may revert back your dataset with no collation.

[ken-slabs]

Just did some more tests and Looks like there is a workaround by using the Collate function and changing the collation back to blank.  I was able to create the view now.

create or replace view prod_mssql_dbo.vw_testview

as

(

  select   

    pages.companyid,  

    pages.pageid,

    collate(companies.companyname, '') companyname,

    collate(pages.pagename, '') pagename

  from prod_mssql_dbo.pages

    join prod_mssql_dbo.companies on companies.companyid = pages.companyid

)