I have a query in SAP HANA like below. I am calculating check_sum based on some column in the table Now I have created the same table in Big Query. I want to calculate check_sum in Big Query and get the same result as in SAP HANA I have tried like below It says TO_BINARY function is not available. I
Tag: google-bigquery
Problem Bigquery stored procedure not filtering
I’m trying to use a stored procedure to get some data from my clients filtering a table by date and channel of purchase in a query that looks something like this: After running the procedure, I use the following call: The call executes and returns a table, but the result is never filtered. The dates don…
How to create a Temporary table in Bigquery
I’ve been trying to build up a Temporary Table with both CREATE TEMPORARY TABLE and WITH statements in the following query. So far I couldn’t get any positive result and I don’t know what’s going on. In last line, the error alert says: “Syntax error: Expected “(” or “…
How to convert an array of key values to columns in BigQuery / GoogleSQL?
I have an array in BigQuery that looks like the following: SELECT params FROM mySource; Which looks like this: params [{ key: “name”, value: “apple” },{ key: “color”, value: “red” },{ key: “delicious”, value: “yes” }] How do I change my q…
Count actions that occur only after a certain time from the previous action
I have a dataset in BigQuery where I want to count all non-duplicate actions. A duplicate action is one that occurs within a certain timeframe or ‘countdown’ from prior actions. Once the countdown reaches 0, the timer resets and the very next action is no longer considered to be duplicate. In my e…
Bigquery error when powering negative number with decimal(float64)
I’m trying to calculate the CAGR using the formula in BigQuery When running the query, somewhere in middle the values become POW(-0.0310044, 0.333333) and I’m getting error I tried calculating the same value in a calculator and I get the result. What could be the problem and how do I solve it? Ans…
Calling a native function within a UDF in bigquery
I am trying to convert our legacy functions into standard SQL and so by using UDFs as a gig to reuse the old function names with the native functions. Here I have my UDF to_char and inside use the parameters to run FORMAT_DATE but when I execute it even though a routine is created I get errors with no additio…
Selecting values from a nested column based on a condition applied to another nested column in BigQuery
How can I use the index of a “special” value from a nested column (ex: the index of the max value in that nested column) to select a value from another nested column using that index? As an example, consider a table with the following schema: Field name Type Mode id STRING NULLABLE username STRING…
Group elements by month three by three
I have this Standard SQL query: With the following output: You can simulate the output with: I am retreiving the entries of the last 12 months. I have already grouped and ordered them by month, but I need to also group them three by three according to the month. Moreover, as you can see in the output there ar…
SELECT list expression references column xxx which is neither grouped nor aggregated at, why need to use subquery?
So I’m kinda new to SQL and been following some tutorial courses online. I want to compare the num_bikes_available at a station to the average num_bikes_available. My question is why cant it just show the average using the OUTER SELECT clause? Why do it need to be done using SUBQUERY? My Answer. Tutoria…