SQL

I'm trying to connect to a MySQL Account. When I enter in my credentials and CA Cert I get the error "Failed to authenticate. Verify the credentials and try again. Error setting up SQL connection. Could not connect to: Received fatal alert: protocol_version" I've verified the user is using TSL v 1.2. I'm also able to…

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`,…

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…

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. 😓…

Hi All, I'm in the process of building an attrition model in Domo in the form: Total Terms / Average headcount Terms is easy, that's just a count of everyone with a term status for that time period. Headcount is where I'm getting stuck. My headcount numbers are currently aggregated based on headcount at the end of each…

Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation. Table a looks like this: Date All Revenue 09/01/21 100 09/02/21 200 Table b looks like this: Date HDM Revenue 09/01/21 90 09/02/21 170 I need to build a table that would calculate the percentage of HDM Revenue,…

How can I connect MS SQL server with Windows authentication mode only? Should I ask the server owner to change the selection from Windows Authentication mode to SQL Server and Windows Authentication mode?

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…

I'm checking out some of our options for speeding up data ingestion in Domo. A few of our Postgres pulls are taking longer than we would like, and I'm exploring creating a materialized view to cut the PG processing time down to 0, from Domo's perspective. That still leaves the data transfer time, and the Domo processing…

Hello! I am looking for suggestions on how to improve the performance of my mysql dataflow. Currently I have a recursive dataflow built with a Google Analytics connector, and I'm using a concatenated key created by joining multiple columns together to delete and upsert new data from the historical dataset. I'm using the…

I have a large table that takes a while to sync over to Domo through the MySQL connector. I currently schedule the sync to occur overnight because a table lock prevents other users from reading the source table. I would like to sync the table on demand during working hours though. Is there a way to instruct the MySQL…

Hi, I have a MySQL dataflow that is built from API reports from a vendors application. The idea is that we did a one time historical pull of the API as dataset 1. Then, the same API that only pulls 1 day of data and appends to the historical dataset as dataset 2. But, every once and a while it seems as if data is dropping…

Hi. I have two input datasets in SQL Magic Transformation. One table called daily the other one estimated . Both tables have the same columns. I need to fetch the data from estimated table but only for the date that is later than max(`Date`) in table dail Data looks like this. Table daily Date Name Revenue 2021-08-01 A 10…

When we are establishing a new job in Domo Workbench of the type ODBC, we write the query in the box of Database Query Editor as in the image below. However the query we want to do should not be static and we would like to modify that query based on external triggers. For example, in the image, is it possible to pass a…

We are using a MySQL connector to load data from our Google Cloud SQL servers into DOMO. From what I can tell, it is only possible to connect to a Database, and all the tables within that database become accessible via the connector. Any of the tables in our database can be chosen in this window (see the highlighted…

Hi, can anyone tell me why If my "A" code works, and my "B" CASE WHEN... THEN 0 ELSE 2 END code works, why can't I substitute code "A" for "2" in code B? I'm trying to kill divide by zero errors in a beast mode. --A SUM(IFNULL(`qty sold instock`,0))/SUM(IFNULL(`qty found`,0)) --B (CASE WHEN (IFNULL(`qty found`,0)) = 0 THEN…

I'm trying to just work out the basic syntax / method in DOMO SQL transform of: Create Stored Procedure (creates a result table after some manipulation of the data) Execute Stored Procedure (to get result table) Output Result table into DOMO for use in charts etc.. I'm trying to get this to work but I can't get the results…

Hey guys, I will try to explain my issue, I'm really loosing hair on this one, hope wil get feedback from you

Hello, Domosapiens! I have an interesting quandry. I have a data flow (using MagicETL - should I be using something else?) that I am trying to build and convert a date that my MySQL data connector is bringing over. The datetime column comes from an orders table and is called posted_date and it brings the data over as UTC…

Hello, I cannot for the life of me figure out why this "where" clause is giving me troubles, I have used this logic a thousand times with no problems. The problem is coming on my join of c to a. Specifically, it seems to happen with my "where c.result_code is greater or equal to 9.5 and c.result_code is less than or equal…

Table A (Completed Evaluations w/ dates & locations) Table B (Locations Universe, all available locations) Table A has all the same location columns as Table B. Table A also has completed dates and evaluation data, We want to show locations with completed evaluations for a user specified date period (i.e. using a date…

I have data that has varying rows of data for each identifier, from one row to 26. For each identifier, I have four different columns that change with the various rows. For each new identifier, I want to loop through the rows that follow that have the same identifier and build the row out with the unique values in these…

I use the datediff for business days function in a number of mysql data flows and it works great. However, I'm getting some very strange results on the latest flow that I'm working on and I can't figure out what's going wrong. I have a start date which I'm looking to compare to the current date. However, the code I'm using…

How do i trim out an email address from a field with a string? Example of Field: Yes, we accept this current pricing and would like to proceed with this order. Thank you, Bob Dole Lab Research Analyst Poss Lab—Dole University bob.dole@some.edu Thanks

I currently have this code: categorizing some leads. Select `Year-Month` ,`Lead Source` ,`Product Line` ,`Product Models` ,`Country` ,`Early Stage No Movement` ,`Stage 0 to Early` ,`Early to Lost` ,`Early to Won` ,`Early to Late` ,`Late to Lost` ,`Late to Won` ,Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total…

I have a table with thousands of rows in which there is a relationship of child/parent between the rows. For each record I would like to have all the hierarchy related with that record. As an example, I have the following table and for the id=19 I would like to return all its related children, which are [20, 21, 22] I have…

I've neglected MySQL Data Flows because I'd passingly assumed that they were for MySQL-derived DataSets. It seems not, it looks like a MySQL DataFlow is a DataFlow that uses an embedded version of MySQL. Is this correct? I've seen mention of Window Functions in passing, and assumed the MySQL must be 8.0+, as that's when…

hello, hope someone could help on my SQL: I just created a simple SQL under DOMO MySQL to select some fields and the MIN and MAX of a date, like: SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date' FROM`data_1` GROUP BY `id`, `name` but nothing is returned in the 2 date fields, original…

I have a column called 'description' which can contain multiple elements. One of them is 'Order #1234567890' (or other digits). I want to create a new column that just extracts the numbers in SQL or Magic ETL (so that I can do Beast Modes etc. on it afterwards). How can I create this column? Numbers may exist in other…