Databases
StackOverflow2013
Postsdbo
PORemoving subquery of select statement used multiple times to optimize the performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORemoving subquery of select statement used multiple times to optimize the performance
    primarykey
    data
    text
    <p>I have a following query, this query uses a subquery in the select clause multiple times as</p> <pre><code>SELECT DISTINCT CAST(decCostFactor as decimal(9,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) </code></pre> <p>I want to remove this subquery. I tried using group by CAST(decCostFactor as decimal(9,4)), but now it ask to include other columns as well in the group by cluase. </p> <p>Any help is really appreciated.</p> <p>The main query is </p> <pre><code>DECLARE @Region int=10006 SELECT st.strRegion, st.intStore, st.strStoreName, tblSticker.intStickerNo , tblSticker.strTeamNo AS strTeam , tblSticker.dtmFixtureStartDate AS dtmStartDate , tblSticker.intAreaNo , SUBSTRING ( tblSticker.strMiscText , 8 , 30 ) AS strSection , tblDetail.intLineNum , tblDetail.strBarcode , tblDetail.intBarcodeLength , tblDetail.intBarcodeType , tblDetail.strBarcodeEntrySw AS strKeyBarcode , tblDetail.fltPrice AS fltPrice , tblDetail.fltQty AS fltQty , tblDetail.strPTCCode4 AS strKeyPrice , tblDetail.strPTCCode5 AS strKeyQty , SUBSTRING ( tblDetail.strMiscText , 1 , 1 ) AS strNOF , SUBSTRING ( tblDetail.strClientText , 1 , 30 ) AS strDesc , SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) AS strSubTeam , SUBSTRING ( tblDetail.strMiscText , 16 , 25 ) AS strSubTeamDesc , SUBSTRING ( tblDetail.strClientText , 34 , 3 ) AS strSubDept , SUBSTRING ( tblDetail.strClientText , 37 , 3 ) AS strClass , SUBSTRING ( tblDetail.strClientText , 40 , 3 ) AS strSubClass , SUBSTRING ( tblDetail.strMiscText , 41 , 7 ) AS strCostFactor , SUBSTRING ( tblDetail.strMiscText , 41 , 1 ) AS strPerishableFlag , tblSubTeam.blnBreakoutCost, fltPriceLb = tblDetail.fltClientMisc1, fltCostLb = CASE WHEN tblDetail.decCost &gt; 0 THEN tblDetail.decCost ELSE tblDetail.fltClientMisc1 * ( SELECT DISTINCT CAST(decCostFactor as decimal(6,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) ) END, strVendor = CASE WHEN tblDetail.strVendor IS NULL THEN 'NA' ELSE tblDetail.strVendor END, fltWeightSouth = CASE WHEN tblDetail.fltClientMisc1 &gt; 0 THEN (tblDetail.fltPrice / tblDetail.fltClientMisc1) WHEN SUBSTRING(tblDetail.strMiscText,49,1) &lt;&gt; 'R' AND tblDetail.fltClientMisc1 = 0 THEN cast(SUBSTRING(tblDetail.strClientText,43,6) as float) ELSE 0 END, fltCostSouth = ISNULL(CASE WHEN SUBSTRING(tblDetail.strMiscText,50,1) = 'C' THEN (tblDetail.fltPrice ) ELSE -- To calculate cost for Subteam 3100 in the south region - Thomas - 2/19/2008 CASE WHEN (SUBSTRING(tblDetail.strMiscText,12,4) = '3100') AND (Substring(tblDetail.strMiscText,1,1) &lt;&gt; 'N') THEN fltprice * ( SELECT DISTINCT CAST(decCostFactor as decimal(6,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText, 12, 4) ) ELSE CASE WHEN tblDetail.fltClientMisc1 &gt; 0 and tblDetail.decCost &gt; 0 -- modified by TA to remedy Extended Retail for weighted items THEN (tblDetail.fltPrice / tblDetail.fltClientMisc1) * tblDetail.decCost ELSE CASE WHEN (Substring(tblDetail.strMiscText,1,1) = 'N') Or (tblDetail.strBarcode = '00000000000000') THEN CASE WHEN (fltprice = 0 and tblDetail.decCost &gt; 0) THEN CASE WHEN SUBSTRING(tblDetail.strClientText,43,6) &lt;&gt; '' THEN CASE WHEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as float) &gt; 0 THEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as float)*tblDetail.decCost ELSE fltTotalUnits*tblDetail.decCost END ELSE 0 * tblDetail.decCost END ELSE CASE WHEN (Substring(tblDetail.strMiscText,41,7)) = '' THEN fltprice * 0 ELSE fltprice * Cast(Substring(tblDetail.strMiscText,41,7) as decimal(6,4)) END END ELSE CASE WHEN (fltprice &gt; 0 and tblDetail.decCost = 0) THEN fltprice * ( SELECT DISTINCT CAST(decCostFactor as decimal(6,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) ) ELSE CASE WHEN (fltprice = 0 and tblDetail.decCost &gt; 0) THEN CASE WHEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as float) &gt; 0 THEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as float) * tblDetail.decCost ELSE tblDetail.decCost END ELSE tblDetail.decCost END END END END END END,0), fltItemCost = ISNULL(CASE WHEN ((((Substring(tblDetail.strMiscText,41,7) = '00.0000') or (tblDetail.fltClientMisc1 = 0)) or (tblDetail.decCost = 0)) and Substring(tblDetail.strMiscText,1,1) &lt;&gt; 'N') THEN CASE WHEN (fltprice &gt; 0 and tblDetail.decCost = 0) THEN fltprice * ( SELECT DISTINCT CAST(decCostFactor as decimal(9,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) ) ELSE CASE WHEN (fltprice = 0 and tblDetail.decCost &gt; 0) THEN tblDetail.decCost ELSE CASE WHEN (Substring(tblDetail.strMiscText,41,7)) = '' THEN fltprice * 0 ELSE fltprice * Cast(Substring(tblDetail.strMiscText,41,7) as decimal(9,4)) END END END ELSE CASE WHEN (Substring(tblDetail.strMiscText,1,1) = 'N') Or (tblDetail.strBarcode = '00000000000000') THEN fltprice * Cast(Substring(tblDetail.strMiscText,41,7) as decimal(9,4)) ELSE CASE WHEN (fltprice &gt; 0 and tblDetail.decCost = 0) THEN fltprice * ( SELECT DISTINCT CAST(decCostFactor as decimal(9,4)) FROM tblsubteam WITH (NOLOCK) WHERE intstore = st.intStore AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4) ) ELSE CASE WHEN (Substring(tblDetail.strMiscText,41,7)) = '' THEN fltprice * 0 ELSE fltprice * Cast(Substring(tblDetail.strMiscText,41,7) as decimal(9,4)) END END END END, 0), strWeight = SUBSTRING(tblDetail.strClientText, 43, 6) FROM tblSticker WITH ( NOLOCK ) INNER JOIN tblDetail WITH ( NOLOCK ) ON tblSticker.intStore = tblDetail.intStore AND tblSticker.intStickerNo = tblDetail.intStickerNo AND tblSticker.dtmStickerDate = tblDetail.dtmStickerDate LEFT OUTER JOIN tblsubteam ON tblDetail.intStore = tblSubTeam.intStore AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam inner join tblStore st on st.intStore=tblDetail.intStore --fix add join to store. Kevin 2/8/07 -- AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam --fix fix move join to store outside of compound conditional. Eddie 9/2/11 WHERE ((@Region is not null and st.intRegion=@Region) or @Region is null) AND st.intStore &lt; 90000 AND ( tblSticker.intStickerNo NOT BETWEEN 334717100 AND 334717299 ) AND tblSticker.strRescanSW = 'N' AND ( tblSticker.strEmptyStatus = ' ' OR tblSticker.strEmptyStatus = '*' ) ORDER BY st.strStoreName, tblSticker.intStickerNo , tblDetail.intLineNum </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTechDo
    1. USazim
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload