-
How could we implement a SQL Flow that would split lines based on certain logic
Hello, I have an ask where I need to split lines when there are multiple Users working at the same time in order to accurately count how many users are working together during a certain interval. If I have 3 users working each one during different periods I want to split the Initial rows as per below picture. If I am able…
-
MySQL Workflow
Can Domo's mysql handle more than 500k records? It shows the import can only go to 500k but I'm assuming that is for previewing data not actual processing. I attempted to output only five rows from a million record dataset and it ran for 30 minutes before I cancelled the process.
-
Is there a roadmap for updating to MySQL v8?
Hi, A client has asked if we will be updating to MySQL v8 soon as we currently only support MySQL version 5.6.37? Thanks!
-
Trying to use a case statement with a When/Then clause inside it formula is validated but not workin
Hi I am trying to use a case statement with a When and Then clause inside it. The formula I am putting in is basic and has worked for other tables but not working for the one I am currently working with. I have tried these formulas: 1. CASE `SeasonalDescription` WHEN 'Easter' THEN 'Spring and Summer' ELSE 'Other' END 2.…
-
3 month moving with current month for a date column
I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR. I know the current month function to get only the current month moving but having trouble…
-
Dataset query fails when not using a where clause
I'm trying to get all the data from my dataset by executing a simple query: Here's what my code looks like: rows = cnxn.execute(sqlcmd)for row in rows: dataset_id = '609f128a-ac54-4f4f-a71e-81362f9fa154' table_name = 'orders' query_url = "https://api.domo.com/v1/datasets/query/execute/" + dataset_id myobj = {"sql": "SELECT…
-
Is there a way to run ad-hoc SQL queries against arbitrary PostgreSQL tables?
My company is migrating from Mode Analytics to Domo, and I'm trying to understand whether and how I can run ad-hoc SQL queries against arbitrary tables. As a software engineer, I regularly use Mode's [query editor](https://mode.com/help/articles/querying-data/) to gain insights into our PostgreSQL database data in…
-
SQL DataFlow vs API access
Hi DOJO Community, Good morning. Do any of you know why when I am querying the API the runtime between two dates is about 2 minutes (querying 130k rows from a 16MM row dataset). But when I query it from SQL given a query using the SQL ETL DataFlow, it takes a very long time to run ( I terminated it each time)
-
Calculating hits per minute by timestamp and Parts ran
I am trying to calculate a live hits per minute by machine based on total parts ran per the difference between the start time of the day and the most recent timestamp. I am having trouble piecing together the correct formula to go about this. Does anyone have any suggestions? Thanks
-
Help with Converting Time Zone - 'DateTimeOffset' to 'DateTime' in SQL Connector
Hey everyone, I'm having trouble with a conversion from DateTimeOffset to Datetime in a SQL Query (inside a SQL connector). I am trying to convert my date columns when they are imported in DOMO as UTC so they are coming in as the right time (Db is UTC, Domo is Pacific) and convert them to DateTime. When I run…
-
Connect SQL Server Management Studio to Domo
Hi everyone, I have database in Microsoft Sql Server Management Studio. Is it possible to connect it with Domo instance using Microsoft Sql Server Connector ? https://domohelp.domo.com/hc/en-us/articles/360043436173-Microsoft-SQL-Server-Connector
-
Syntax for null
Hi all, I am trying to create a formula in ETL that deals with the null values in multiple columns. So far, no matter what kind of formula I use, I am unable to get the blanks filled in Here is what I am trying right now based off of all of the other posts I have read Again, nothing is turning up XYX like I would expect. I…
-
SQL Writeback - Importing to Existing Schema
Is there a way to specify a specific SQL schema the SQL Writeback connector uses? The documentation mentions it's possible, but doesn't mention the syntax. I can easily import to dbo, but would like to import existing schema.
-
MySQL Join Optimisation - 2 hours, for what should take seconds!!
Hi, I have been trying to optimise a MySQL script, which for some reason is taking over 2 hours to run! I've been unable to figure out why the script takes so long to run, so any help and tips for identifying the cause of the problem would be appreciated. The script is very simple, essentially: SELECT x.*, y.field1,…
-
Is there a better way to perform a variable-length recursive query using MySQL ver. 5.6?
I'm looking to perform a recursive query on a data hierarchy with an unknown amount of levels. I've found that making use of stored procedures (or simple self-joins if the there are very few levels) works well enough most of the time, but it can be messy and time-consuming. From what I understand, Common Table Expressions…
-
How can I use the MATCH() function with a declared variable string in a MySQL dataflow?
I am looking for assistance in determining how to use the value of a string variable to DECLARE a persistent variable AS a STRING that can be passed into the MATCH() function in MySQL transform.
-
Adrenaline version of SQL
Hi All, Can you please let me know what version of SQL does adrenaline use.. Thanks, Arun
-
Troubleshooting on Dynamic SQL in MySQL
Hello, I am having trouble on running below Dynamic SQL query. Can someone have a look and troubleshoot what am I doing incorrectly? Also, is there any way I can able to print Dynamic SQL query? SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'sum(case when x.question_text = ''', question_text, ''' then x.answer_text…
-
Need help reviewing sql query used to create domo sql dataflow
Hello, I am working on moving one of our SQL Queries to Domo. This query looks for users who have made a purchase in a month and no purchases in any month before or after. I was able to move this query but I am getting slightly different results. Since dates are used differently in both queries I wonder if I made a mistake…
-
Using a View as a input dataset to mysql dataflow
Hi All, I am trying to have (a view) as one of the inputs to the mysql dataflow...But its not being accepted ...Can you please help me with this... Thanks, Arun
-
Creating a join from a lookup table
I have created a dataflow that joins two sets of data together, however when i join them together it's dropping data because it doesn't have anything to match with. How would i create a dataflow that would match all the data and any of the unmatched records would also stay?
-
LISTAGG or equivalent during data import from NetSuite
What SQL should I be using during an import to collapse data from a column into ',' delimited strings? when I use: listagg(item.NAME,',') "ITEM(S)", I get the error: [NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# l27jpdhw1857xj1xsmvi0[400] with just item.NAME…
-
Calculate rank so that the smaller number is higher rank
I need to calculate the rank on a metric where the lower value is higher in rank, and the higher values are lower in rank. This is how I've been calculating standard rank (where higher values are high in rank) SELECT `Metric` ,`TimeFrame` ,`VALUE` ,`Name` ,`Grouping` ,`ProcessorName` ,CASE WHEN @priorMetric = `MetricID`…
-
has anyone had any luck creating dynamic columns in mysql
what I'm trying to do is for sql to check and see if a column missing, if it is then i want sql to create the column. I've done several attempts with no luck. the alter table add column sql works but it fails when i add it to the if condition. any thoughts? CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT * FROM…
-
Table vs SQL Data Flows and which functions does Domo not recognize? (I'm confused)
Hi, My CSM informed us that window functions are enabled. I would like to write the following via ETL, or a My SQL dataflow (either way is fine), but I don't know whether to pick a Table or SQL transform, and either one I pick will not accept the following statement. SELECT *, sum(a) over (partition by b order by a range…
-
Can I write SQL directly to my data sources?
I know I can click on "SQL" in the top ribbon to write SQL against my data sources using a data flow. However, does Domo provide a direct option? Many of my users just want to open an interface to write SQL and then preview and download results.
-
MYSQL - Trouble adding new column via LEFT JOIN
Hello! I am having trouble adding a new column, "Dummy", to my output dataset. Although the query is valid, the output does not produce the "Dummy" column when ran. I am trying to identify rows to remove based on the criteria defined in the LEFT JOIN. See below. SELECT A.`Posting Title`, A.`Weekly Reporting Date`,…
-
Schema not keeping changes, but no error given - have you run into this?
Hello, So I have set up a job using MySQL ODBC - executed, all good. Job T Then set up another job with a local CSV file - executed all good. Job A So then I want to update Job T to do a lookup transform of the results from Job A. This seems to run well. But I also need to update the schema. I need to remove a column and I…
-
'Incorrect column name' doing a Dynamic Pivot
Hi there! I've been following this tutorial for dynamic pivot and so far it has worked: https://domohelp.domo.com/hc/en-us/articles/360042923454-Dynamic-Pivot. However, I get this error: I thought it was because the name was too long, but the character limit is 1024 and even performing a substring I got the same error. 😓…
-
Problem with SQL for WordCloud Chart
Hi all! So, I am trying to run SQL code using these instructions https://domohelp.domo.com/hc/en-us/articles/360042925094-Word-Cloud-Chart When I am at step 9 and trying to run this code CALL word_cloud('nuvaira_patientlogreport_recursive', '`Notes`' , 'y','y' ); It gives me an error 'Invalid. Whoops! Something went…