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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how can i shorten this query

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

SELECT 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.overallremarks
FROM (((((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.newproid
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 ) );

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'
Go to Top of Page

chairman
Starting Member

5 Posts

Posted - 2014-07-30 : 04:36:29
thanks a lot sr.!

chairman
Go to Top of Page
   

- Advertisement -