Discussions

Expand all | Collapse all

how query with two filters

  • 1.  how query with two filters

    Posted 24 days ago
    SELECT distinct("m5d636e78_customer".m_name) AS "label_customer.name" FROM "m5d636e78_SO_ITEM" JOIN m5d636e78_SO ON "m5d636e78_SO_ITEM".m_soid = "m5d636e78_SO".m_so_id JOIN m5d636e78_customer ON "m5d636e78_SO".m_customerid = "m5d636e78_customer".m_id GROUP BY "label_customer.name" HAVING CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) >  5000 AND CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) < 5000 ORDER BY "label_customer.name" LIMIT 1001;

    if i run above query mapd is crashing and its restarting 
    but if i run 
    SELECT distinct("m5d636e78_customer".m_name) AS "label_customer.name" FROM "m5d636e78_SO_ITEM" JOIN m5d636e78_SO ON "m5d636e78_SO_ITEM".m_soid = "m5d636e78_SO".m_so_id JOIN m5d636e78_customer ON "m5d636e78_SO".m_customerid = "m5d636e78_customer".m_id GROUP BY "label_customer.name" HAVING CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) > 5000 AND CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) > 6000 ORDER BY "label_customer.name" LIMIT 1001;

    its running fine 

    i want to use 
     HAVING CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) >  5000 AND CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) <  1000

    how to use this condition why its breaking
    #Core
    #Immerse


  • 2.  RE: how query with two filters

    Posted 24 days ago
    Hi @Nagulan Santhi,

    the crashes is obiviously a bug caused by filtering condition (>5000 and <5000, or >5000 and <3000) on the same expression would return 0 records​, I'm going to submit a ticket to fix this beaviour.

    In the meanwhile you should be sure the values used in the having clause are a positive range, so the one used in greather than have to be lower than the one in lower then, as in your second query. You can also remove the first distinct, because is useless (it dosn't change anything).

    A negative range on the same expression will return 0 rows, why you want to use it? with an or it makes more sense to me it works

    HAVING CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) >  5000 OR CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) <  1000



  • 3.  RE: how query with two filters

    Posted 24 days ago
    Hi Candido Dessanti  

    if i give this range in having clause ideally it should return 0 rows but now mapd itself crashing (>5000 and <5000) ,or (>5000 and <3000)
    once this is fixed please let us know or a timeline to fix this 
    Thanks



  • 4.  RE: how query with two filters

    Posted 24 days ago
    Yes Nagulan,

    it should return 0 rows, I wanted just to be sure there wasn't a mistype ;)

    Thanks to help up to discover this bug, I'll post something as soon it's fixed


  • 5.  RE: how query with two filters

    Posted 24 days ago
    Hi @Nagulan Santhi,

    If you can change the query I can suggest a workaround

    rewriting the query this way it should work


    select distinct label_customer.name from (
    SELECT distinct("m5d636e78_customer".m_name) AS "label_customer.name" 
    CAST(sum(coalesce(round("m5d636e78_SO_ITEM".m_totalprice, 2), 0)) as sum_totalprice
    FROM "m5d636e78_SO_ITEM" JOIN m5d636e78_SO ON "m5d636e78_SO_ITEM".m_soid = "m5d636e78_SO".m_so_id 
    JOIN m5d636e78_customer ON "m5d636e78_SO".m_customerid = "m5d636e78_customer".m_id 
    GROUP BY "label_customer.name" )
    WHERE sum_totalprice >5000 and sum_totalprice < 1000
    LIMIT 10 ​​