BigQuery query failed with error: "Unexpected. Please try again."
The following query with the error message: "Unexpected. Please try again."
SELECT sapId as SAP_ID, accessionId as ACCESSION_ID, diagnosticSetId as DIAGNOSTIC_SET_ID
, if( pimsSoftwareCode is null,
if((feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')) OR (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'))
, 'MOBILE', 'ONLINE'
)
,'PIMS') as MODE
, if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
'iOS'
,if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')
, 'Android', null)) as MOBILE_OS
, date(usec_to_timestamp(createAuditDate)) as DATE
, time(usec_to_timestamp(createAuditDate)) as TIME
FROM [20141104Android_backup.EventLog]
where month(USEC_TO_TIMESTAMP(createAuditDate)) = 10
and year(USEC_TO_TIMESTAMP(createAuditDate)) = 2014
and xUserAgent != 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)'
and ( (feature = 'Result_View' and appUrl contains '/app/viewCumulative') or (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')) or (feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS')) );
When I change the following line:
if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
'iOS'
,if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'Android')
, 'Android', null))
:
if(feature = 'MOBILE_JOB' and action = 'VIEW_JOB' and REGEXP_MATCH(xUserAgent, 'iOS'),
'iOS'
,if(feature = 'Result_View' and REGEXP_MATCH(xUserAgent, 'Android')
, 'Android', null))
the request works fine. If necessary, I can specify the ID of the failed job. I fully understand why the first option fails while the second works fine. Thank you for any help you can provide.
source to share
There was an internal error for your work Field 'MOBILE_OS' is incompatible with the table schema: Type mismatch: actual 'TYPE_BOOL' vs. expected 'TYPE_STRING'
. We need to better understand this error instead of giving "Unexpected. Try again." message.
I've seen this previously when using the literal null, as the query engine usually interprets it as a boolean type. Try using STRING(null)
instead null
in IF statement.
source to share