MySQL Forums
Forum List  »  General

Re: how can i shorten this query
Posted by: Rick James
Date: August 27, 2014 10:39PM

    WHERE  ( ( ( IF(Isnull(overallstatus), 'Submitted', IF(overallstatus = "pending",
                                                "submitted" , overallstatus)) ) = "resubmission" )
              AND  ( ( vwproposalret.isdeleted ) = 0 )
              AND  ( ( map_product.isdeleted ) = 0 )
              AND  ( ( tblmapsplit.category ) <> "renew" ) 
           )
      OR  ( ( ( IF(Isnull(overallstatus), 'Submitted', IF(overallstatus = "pending",
                                                "submitted" , overallstatus)) ) = "resubmission" )
              AND  ( ( vwproposalret.isdeleted ) = 0 )
              AND  ( ( map_product.isdeleted ) = 0 )
              AND  ( ( tblmapsplit.category ) IS NULL )
-->
    WHERE  IF(Isnull(overallstatus),
                     'Submitted',
                     IF(overallstatus = "pending", "submitted" , overallstatus
                 ) = "resubmission"
      AND  vwproposalret.isdeleted = 0
      AND  map_product.isdeleted = 0
      AND  (  tblmapsplit.category <> "renew"  OR 
              tblmapsplit.category IS NULL
           )
* That included boolean transformations to shorten it.
* I got rid of redundant parentheses.
* It would also help to use aliases instead of full table names.

    FROM  ( ( ( ( (        vwproposalret
               INNER JOIN  vwcustomer ON vwproposalret.customerid = vwcustomer.accountid )
             INNER JOIN  map_product ON vwproposalret.productid = map_product.mapprodid )
          ...
* You can probably get rid of the parens there, too.

You are using NULL (and ISNULL) a lot; do you really need such? Rethink whether NULL is a good design approach.
Furthermore, you seem to mix 0 and NULL:
Isnull(parentid) OR parentid = 0

> Concat(Month(validfrom), "/1/", Year(validfrom)) AS finalDate,

Consider using DATE_FORMAT().

What is the output of EXPLAIN SELECT ... ?

Am I approaching your question?

Options: ReplyQuote


Subject
Written By
Posted
Re: how can i shorten this query
August 27, 2014 10:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.