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

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    '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?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Otieno_Onyango
    Otieno_Onyango Member

    @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
    Otieno_Onyango Member

    @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
    jaeW_at_Onyx Coach

    does your WHERE clause say debtor is null?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Otieno_Onyango
    Otieno_Onyango Member

    @jaeW_at_Onyx yes

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    :P

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Otieno_Onyango
    Otieno_Onyango Member

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

    javexx.PNG


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Otieno_Onyango
    Otieno_Onyango Member

    @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
    GrantSmith Coach
    edited March 2021

    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).

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Otieno_Onyango
    Otieno_Onyango Member

    @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
    GrantSmith Coach

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Otieno_Onyango
    Otieno_Onyango Member

    @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
    ST_Superman Domo Employee

    @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.

Categories