Domo Sql not populating all columns in the results view, How does one solve this?

Otieno_Onyango

jaeW_at_Onyx

'Domo SQL' is not a thing.

Your query is running in a MySQL 5.6 database.

just type SELECT VERSION()

Your preview is doing exactly what your Query says... SELECT 3 columns. What are you expecting?

Otieno_Onyango

@jaeW_at_Onyx I'm expecting 3 columns but as you can see, only 2 are being populated and the order by also is not being applied as expected.

the MySQL version is 5.6.37-82.2-82.2, my issue is with the output not the version of MySQL

Otieno_Onyango

@jaeW_at_Onyx I'm expecting 3 columns but as you can see, only 2 are being populated and the order by also is not being applied as expected.

the MySQL version is 5.6.37-82.2-82.2, my issue is with the output not the version of MySQL

jaeW_at_Onyx

does your WHERE clause say debtor is null?

Otieno_Onyango

@jaeW_at_Onyx yes

jaeW_at_Onyx

:P

That's why you're not getting results. // try filtering on IS Not NULL just to confirm.

Otieno_Onyango

@jaeW_at_Onyx this is the same query giving me the desirable output on a DB client.

jaeW_at_Onyx

When you run the dataflow do you get the desired results?

Also, fundamentally your result / query doesn't make sense that you posted.

in your JOIN clause you're saying a.debtor = b.debtor. If that's the case a.debtor can never be populated IF b.debtor is null.

also if you expected b.debtor to be null (WHERE clause) then it doesn't make sense to ORDER BY b.debtor = null because it is NULL.

What are you trying to accomplish?

Otieno_Onyango

@jaeW_at_Onyx , as you can see clearly the order by, is on a.debitor_id on the image above FYI this is an alternative way of writing a query that would need windows function to achieve the same results and since DOMO uses a MySQL that doesn't support windows function thus the query, also please understand the query before you respond.

GrantSmith

Hi @Otieno_Onyango

Do you have NULL debtor ids in your transaction table? What happens if you filter also for a.debitor_id is not null?

You query appears correct and should get you the first transaction for a debitor (Yay for lack of window functions in MySQL 5.6).

Otieno_Onyango

@GrantSmith the query works perfectly as an alternative for window function, my issue is when I write the same in DOMO, it doesn't populate the debitor_id column, and also the result is not ordered by the debtor_id.

GrantSmith

@Otieno_Onyango

Right, that's what I was saying. Your query looks like it should work just fine. I've run many similar queries. I was just trying see if you possibly had some NULL debitor_ids in your table to help explain why you're seeing nulls in that field.

I'm also curious if you've run your MySQL Dataflow all the way though to see if the debitor_ids appear in the output dataset and if it's a possible issue with the preview only or an actual issue with the data processing.

Otieno_Onyango

@GrantSmith what made me raise a red flag on this is the fact that in DOMO I get a different result than the one on my DB client, my expectation was to have the same error in the two instances if there was one but it only occurs in DOMO, also having nulls should affect both instances, not one.

You have a point, I haven't tried completing the MySQL dataflow since it feels weird completing the MySQL dataflow with the glare of an error. let me see if completing the MySQL dataflow works.

ST_Superman

@Otieno_Onyango please let the dataflow run to completion to see if you still get different results. The preview engine in Domo does not always load the entire datasets. It just loads a small portion of them to minimize processing time. See if you still have the issue after running the complete dataflow.