Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
chairman
Starting Member
5 Posts |
Posted - 2014-07-27 : 23:38:23
|
i am a newbie in mysql workbench ,is der anyone can help me to fasten and shorten dis query.thanksSELECT DISTINCT IF(Isnull(overallstatus), 'Submitted', IF(overallstatus = "pending", "submitted" , overallstatus)) AS rem, IF(Isnull(mapprostatusid), 1, mapprostatusid) AS ID, vwproposalret.proposalid, vwproposalret.probatchno, Month(validfrom) AS strMonth, Year(validfrom) AS strYear, Concat(Month(validfrom), "/1/", Year(validfrom)) AS finalDate, vwproposalret.proposedcoastaldiscount * vwproposalret.committedvolltrs AS ActualCoastal, vwmapsitedetails.arnoc, vwmapsitedetails.sitename, vwmapsitedetails.type, vwmapsitedetails.nhpdealer, vwmapsitedetails.plant, vwproposalret.productid, map_product.rsop_prod, vwmapsitedetails.cluster, vwmapsitedetails.zonecode, vwmapsitedetails.districtcode, vwmapsitedetails.pa_code, vwproposalret.soldto, vwproposalret.shiptocode, vwproposalret.proposedcoastaldiscount, vwproposalret.committedvolltrs, vwproposalret.maxvol, vwproposalret.fleetcustomer, vwcustomer.accountid, vwcustomer.customername AS accountName, IF(parentid = proposalid OR Isnull(parentid) OR parentid = 0, lastupdate, validfrom) AS vFrom, vwproposalret.validto, vwproposalret.retjustification, vwproposalret.retsubmissiondate, tblproposalstatus.overallremarks AS rtmComment, tblmapsplit.origproid, IF(parentid = proposalid OR Isnull(parentid) OR parentid = 0, "no", "yes") AS previouslyApproved, tblmapsplit.parentid, tblproposalstatus.overallremarksFROM (((((vwproposalret INNER JOIN vwcustomer ON vwproposalret.customerid = vwcustomer.accountid) INNER JOIN map_product ON vwproposalret.productid = map_product.mapprodid) INNER JOIN vwmapsitedetails ON ( vwproposalret.productid = vwmapsitedetails.productid ) AND ( vwproposalret.shiptocode = vwmapsitedetails.shiptocode )) LEFT JOIN tblproposalstatus ON vwproposalret.proposalid = tblproposalstatus.proid) LEFT JOIN map_proposalstatus ON tblproposalstatus.overallstatus = map_proposalstatus.proposalstatus) LEFT JOIN tblmapsplit ON vwproposalret.proposalid = tblmapsplit.newproidWHERE ( ( ( 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 ) );thanks for the help! |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-07-28 : 06:52:10
|
This forum is for Microsoft SQL Server users, so you will probably get better answers in a MySQL forum.I have tried writing the query, the way I would normally write queries, and came up with a few comments:- Always format your query so it's easier to read later on (or for another person to read)
- With the use of aliases you can avoid typing the long tablenames.
- When referencing a field, always include the tablename (or its alias) - the red text references fields with unknown table (at least for me).
- I normally avoid if statements as this is not used in most db-engines. Case statements is implemented in most db-engines and have the same functionality.
- When testing if a values is null, I use "fieldname is null", as this also goes wih most db-engines.
- When evaluating fields and you don't want null values, coalesche function works for most db-engines (on MySQL you could use ifnull function, but this is specific to MySQL)
- When joining tables, I always specify inner/outer to imply that I have decided the join method. That way there can be no doubt as to what I intended.
- The where statement can be shortened a bit. Logically this should be the same as the one you wrote.
- As you only want overallstatus from table tblproposalstatus to be 'resubmission', we can change left [outer] join to inner join.
- In this case, you have a whole lot of unneeded brackes, which makes the query harder to read.
select distinct case when ps.overallstatus is null or ps.overallstatus='pending' then 'submitted' else ps.overallstatus end as rem ,coalesche(mps.mapprostatusid,1) as ID ,pr.proposalid ,pr.probatchno ,month(validfrom) as strMonth ,year(validfrom) as strYear ,concat(month(validfrom),'/1/',year(validfrom)) as finalDate ,pr.proposedcoastaldiscount*pr.committedvolltrs as ActualCoastal ,msd.arnoc ,msd.sitename ,msd.type ,msd.nhpdealer ,msd.plant ,pr.productid ,mp.rsop_prod ,msd.cluster ,msd.zonecode ,msd.districtcode ,msd.pa_code ,pr.soldto ,pr.shiptocode ,pr.proposedcoastaldiscount ,pr.committedvolltrs ,pr.maxvol ,pr.fleetcustomer ,c.accountid ,c.customername as accountName ,case when ms.parentid=pr.proposalid or ms.parentid is null or ms.parentid=0 then lastupdate else validfrom end as vFrom ,pr.validto ,pr.retjustification ,pr.retsubmissiondate ,ps.overallremarks as rtmComment ,ms.origproid ,case when ms.parentid=pr.proposalid or ms.parentid is null or ms.parentid=0 then 'no' else 'yes' end as previouslyApproved ,ms.parentid ,ps.overallremarks from vwproposalret as pr inner join vwcustomer as c on c.accountid=pr.customerid inner join map_product as mp on mp.mapprodid=pr.productid inner join vwmapsitedetails as msd on msd.productid=pr.productid and msd.shiptocode=pr.shiptocode inner join tblproposalstatus as ps on ps.proid=pr.proposalid left outer join map_proposalstatus as mps on mps.proposalstatus=ps.overallstatus left outer join tblmapsplit as ms on ms.newproid=pr.proposalid where ps.overallstatus='resubmission' and pr.isdeleted=0 and mp.isdeleted=0 and ms.category<>'renew' |
|
|
chairman
Starting Member
5 Posts |
Posted - 2014-07-30 : 04:36:29
|
thanks a lot sr.!chairman |
|
|
|
|
|
|
|