Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
Looker development
SQL

SQL

For all of your SQL errors, troubleshooting questions, and cool queries

363 Topics
812 Replies

When you subscribe we will email you when there is a new topic in this category

363 Topics

Recently active

Most replies Most views Newest first

zckymcMember

asked in SQL

PratiNew Member

asked in SQL

connection issue

Cannot connect: time zone tables don't appear to be loaded JDBC string: jdbc:mysql://14.97.218.174:2300/uc_data?zeroDateTimeBehavior=convertToNull&tcpKeepAlive=true&allowLocalInfile=falseSomeone please help in resolving this issue..

2 days ago

ryoichirooka.kijibiziNew Member

asked in SQL

Date Range Control compatibility with AWS Redshift: to_timestamp(numeric) does not exist

There’s likely a compatibility issue on `to_timestamp()` with AWS Redshift and the following is the error that I get when Date range control tries to query Redshift: Redshift’s SQL language doesn’t have a `to_timestamp()` function that takes in a numeric value. What would be a workaround? Thanks,

2 days ago

mohapatrad-1661497139New Member

asked in SQL

Looker explore issues in custom filter

Hi All, In custom filter, I want to give the measure to take YTD data along with some specific id.My YTD condition is working fine.When I give AND condition to put all those ids using IN condition, error is shown as Expression incomplete. My code snippet is as below. extract_months(${booking_date}) <= extract_months(now())ANDextract_days(${booking_date}) <= extract_days(now())ANDextract_years(${booking_date}) = extract_years(now())AND(${id}) IN ("AB123”) -----after adding this line error comes

5 days ago

Sean BarbaraNew Member

asked in SQL

Unsupported operator: OFFSET

Hello, I try to calculate the variation of a metric that change everyday, I would like to display the percentage of change one day to another day to day.I saw that I need to use the offset() function but I don’t understand why I can not use it and it returns the error: Unsupported operator: OFFSET Can you help me with this? I can’t find any help about this error. Thanks a lot

21 days ago

GaganBNew Member

asked in SQL

Show unecrypted data from DRUID in looker

We are planning to use Looker on DRUID for reporting. PII data elements in DRUID are encrypted but we need to show the data in un-encrypted format in Looker. Can anyone suggest how this can be accomplished?

25 days ago

jairopinzoncNew Member

asked in SQL

Creating a custom Date dimension in Looker Studio

Hello, I’ve been dealing with an issue when blending data from two different Google Analytics Universal views in Looker Studio. This is the scenario. I have to create a new data source by blending data from two Google Analytics Universal views and I need to use the Default Channel Grouping and Date dimensions. Also, I need the Sessions metric field from both views to then build a graph. Here is the problem, I noticed that when using the Date dimension as part of the blending the data is coming in too way different from what I currently have in my dashboard. Therefore, I was wondering what could be the best approach to fix this. One of my thoughts was to avoid blending the Date dimension from the views and create a custom dimension for the date, but so far I couldn’t figure out how to create this custom field. I’ll appreciate your help here!

27 days ago

suhabsharaNew Member

asked in SQL

Issue Calculating Median

Hi, I am trying to calculate median for differences between dates in Looker using datediff function and median in measure as shown in the following screenshot:Seems like the median is not calculated well, for example for the following date differences:The median calculated is 103, and not the average between 103 and 64.The sql used to calculated the median is as shown bellow: What is the problem?

1 month ago

reda123New Member

asked in SQL

Looker visualization mysql issue

I connect to mysql server to visualize data into looker. I have column which has dates in text format. The table is as follow:- property_id year_month 2 2021_01 33 2021_01 356 2021_03 32 2022_04 I would like to convert this year month column to date, so that I can add date filter in my visual ... I am left with the impression that I should concat 01 as a day for that column and then parse date as followPARSE_DATE("%Y-%m-%d", REPLACE(CONCAT(year_month_extra, "_01"), "_", "-"))I recieve error from the visual "inproper configuration"Error with SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'year_month AS CHAR), CAST('01' AS CHAR)), '', '-'), '%Y-%m-%d') AS DATE) AS qt' at line 1 Could you please help? Thank you very much

1 month ago

MaiaNew Member

asked in SQL

Connect Multiple Dashboards to a Unique Data Connection

Hello Everyone, I would like to know if there is an way to create a global connection to a database and connect multiple dashboards to it. I got a database performance problem because i have creating the same connection of each dashboard I have.

1 month ago

detox92New Member

asked in SQL

Counting unique id's in one column

Hi, I am new to Looker and am having various success and can see its a very powerful tool but i have hit a point which i cannot seem to get around. I have searched for similar issues but not really found a concrete way to do what i want. So i have a MYSQL datasoure table and in the table i have a column staff_id In this column i have various id’s of which i could like to group and then display the quantity of each unique id. So at the end i would like to see staff_id qty1 82 24 2 It seems simple in my head but for some reason every time i put it as the metric i get 0 for each. I should mention that i have blended two tables with the headers staff_id as they are the common factors, and am displaying the username which is related to the staff_id correctly (i believe). Any one any ideas where i am going wrong? Sorry if im missing something right in front of my face but i have limited time to get this up and running to prove its worth to

1 month ago

John GonzalezNew Member

asked in SQL

Code not saving CSV

I have the following code, what it does is it uses the client id and client secret to get an access token, then uses the token to fetch a csv from our sql url, only issue is when I run the code I get an HTML pages code saved as a CSV: Any clue why this is happening and how I can fix it? const express = require("express");const routes = require("./routes");const bodyParser = require('body-parser');const qs = require('querystring')const cron = require('node-cron');const path = require('path');const fs = require('fs');const clientId = 'Removed for Security'; const clientSecret = 'Removed for Security'; const querySlug = 'ty9skkkgc4qk2b'; // Replace with your query slug// Set the authentication API request URL and parametersconst authUrl = 'https://hawxservices.cloud.looker.com:19999/api/3.1/login';const authParams = new URLSearchParams({ client_id: clientId, client_secret: clientSecret,});// Authenticate with the Looker API and retrieve an access tokenfetch(authUrl, { method: 'P

1 month ago

moebeMember

posted in SQL

Complex issue with the way Looker calculates totals

Looker offers to calculate the total in addition to the details in an Explore: This has always been a bit tricky, and in my opinion a suboptimal way to go.Today, however, I encountered a problem that actually makes this path become ridiculous, and I don't know how to get around it either. Here I now have the case where I only want to see the market value where the OP <> 0.How does looker do that? SELECT SUM ( sum_op ) AS sum_op , SUM ( marketvalue ) AS marketvalue WHERE bla = blaHAVING sum_op <> 0Of course, this approach cannot work at all, because the sum over everything, i.e. without the elements (see screenshot 1) is not <> 0. Therefore, the total that looker calculates does not match the correct total.The bad thing is: I don't see any model-technical way to get around this.This is clearly a BUG for me as a customer, as the totals do not match the displayed values and my customer cannot trust the displayed totals.With a dummy example like the on

1 month ago

darth pixelNew Member

asked in SQL

malformed SQL for specific filter char

(i use snowflake)sequser sets filter as "contains" and puts 'test$%^23' (without quotes) then sql contains a string ... LIKE '%test$`%^23%' ESCAPE '`' (which is fine and expected) but when filter is set to "is equal to" and the same string then sql contains ... = 'test$%23' (which is wrong as ^ sign is lost)

1 month ago

kameshNew Member

asked in SQL

Dynamic table where field names are in name_ column and values are in text_ column how to query for visualization

I have a table data source like shown in the image Where the name_ field will store the field names and the text_ field will have the value for the field - this is so because the fields are dynamically created - What is the best way to query this table for visualizations.

1 month ago

sahilMember

asked in SQL

Selecting column dynamically in a dimension

I am trying to select a column in my view dimension based on what the field parameter is. dimension: status { type: string sql: Case when ‘{% parameter tablename%}’=‘mparticle.event_usercontent_callbackstatusonpurchasetransaction’ then ${TABLE}.ea_status else Null end ;; } However, the query executes the Then condition even if the condition is false. I tried the LookML case statement but the label parameter does not accept a ${table} argument. Is there a way my dimension will have values populated from a column dynamically else the dimension does not exist or at least gets populated with Null values?

1 month ago

spentastic32New Member

asked in SQL

Syntax error when trying to JOIN from 2 tables

Hi, i am trying to run a query to select information from two tables. i am getting syntax error near ‘join’The main grid has the bulk of the info (LOADS). There is a second table below this (LOAD_PAY_INFO_DETAIL) which has rate and payment information. both tables have the column LOAD ID. I have tried a few different ways to get this to work, but i am stuck. you can see the jist of the query below (please help): select * from loads where ProductID='LOG' and Date_In> '2021-05-31'JOIN Quantity, Rate, Uom ON LOAD_PAY_INFO_DETAIL

1 month ago

yoshida0531aNew Member

asked in SQL

グラフの並び替え

少し気になることとして、並び替えが２つまでしか設定できない。ソートするフィールドを新たに追加しても、SQLのORDER BYが使えない。何かいい方法はありますか？

1 month ago

flo_zapNew Member

asked in SQL

Looker explore question

Hi, I’m building a dashboard on looker which calculates previous period which is displayed on a tile just below the metric, the issue I have is, when I proceed to edit the tile, the backend sql errors but the tile previous period front end works. The error I’m getting is “Operands of >= cannot be literal NULL”, the sql that’s failing “WHEN SAFE_CAST(columnname.date_date AS TIMESTAMP) >= NULL”I assuming it’s working based on a date filter on the dashboard when it’s selected and it updates the null value but shouldn’t the NULL be a variable instead of NULL? I’m trying to replicate this on another tile but it’s not working at all. Thanks.

1 month ago

bolivarNew Member

asked in SQL

Dividing between two values in a table

Hello,I have a table with two columns (with data pulled from Google Analytics): event action and total events. I’d like to divide the total events in the submit row by the total events in the pageview row to calculate submit rate automatically (so, 19,796 / 663,177). Have been stuck on this, would appreciate your help. Thanks!

2 months ago

bri_cNew Member

asked in SQL

Help using Case/when/then in custom field (dimension) with conditions relating to null values

We are using looker through Chrome River (Emburse Analytics) and trying to combine 3 fields into one. We have a home department field, an override department field and a sponsoring department field. I way trying to write a condition that would show the override or sponsoring values if present, otherwise default to home department. Case(when({sponsoring dept} != null, {sponsoring dept}), {home department}) This is where I started to see if it would populate the correct values before adding all the conditions, it did not. it's definitely on the null part of the statement, because if I change that to look for a specific value, it populates as expected. In this statement, how to you write conditions to look for nulls and insert a dimension instead?

2 months ago

yiyinglaura.huang-1665078996New Member

asked in SQL

Clickhouse stable view same as normal view?

When testing in SQL runner, I found that I cannot query data from normal views in Clickhouse.I found the doc saying Clickhouse “stable views” is not supported.https://cloud.google.com/looker/docs/db-config-clickhouse#feature_supportIs “ stable views” the same as “normal views”?

3 months ago

Matthew.DarwinMember

asked in SQL

Allow ability to add query hints and join hints, e.g. HASH join etc

It would be really useful to have a mechanism to add query hints to the generated SQL from LookML. I have some queries where the optimiser for SQL Server is struggling due to the casting of date columns to character and back to facilitate month date ranges, this is resulting in a cardinality estimate issue, which in turn is resulting in a nested loop join instead of a hash join. Simply specifiying the HASH hint in the join clause sorts the problem out completely; so having the ability to ensure that these are added via LookML would be very beneficial.

3 months ago

Page 1 / 15

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Sign up

Already have an account? Login

Social Login

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.