Solved

SQL Query Syntax Error

Posted on 2016-11-01
9
47 Views
Last Modified: 2016-11-16
I am trying to perform the following calculation

( ( RD.TOTAL_QTY * ( ( RD.PRICE * RD.TOTAL_QTY )
                          - ( ROUND(( COALESCE(D.disc_perc, 0) * 1.0
                                      / 100 ) * RD.PRICE * RD.TOTAL_QTY, 2) ) )
            / CASE WHEN RD.TOTAL_QTY = 0 THEN 1
                   ELSE RD.TOTAL_QTY
              END ) * RH.CO_RATE ) AS ExtPrice
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016' AND '09/30/2016' )
               ), 0) AS ReturnAmtTest ,


However I receive the following
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 77
Incorrect syntax near the keyword 'AS'.
0
Comment
Question by:mburk1968
  • 4
  • 4
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41868498
Please do this...
  • Put your above code in a code block
  • In your SSMS run this again, and for both error messages double-click on the error message, watch the cursor jump to the offending line, and then tell us which line that is in the code block.  
  • After you've done that edit your code and remove all unnecessary parentheses marks ( ).  I'm guessing you used some kind of UI to generate this code?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41868513
Could you please paste the entire query in the code block ?
0
 

Author Comment

by:mburk1968
ID: 41868542
Query 1 -- This is code that our internal Reporting tool generates for a report the includes a ReturnAmt = SFC_CR_EXT_NET_AMT
 I have commented the code area in question.

Query 1
SELECT  CASE WHEN D.DIVISION = '' THEN SPACE(3)
             ELSE ' Division: ' + RTRIM(CAST(D.DIVISION AS CHAR(3)))
        END + CASE WHEN H.CUSTOMER = '' THEN SPACE(8)
                   ELSE ' Customer: ' + RTRIM(CAST(H.CUSTOMER AS CHAR(8)))
              END
        + CASE WHEN COALESCE(zzxcustr.cust_name, '') = '' THEN SPACE(40)
               ELSE ' Customer Name: '
                    + RTRIM(CAST(COALESCE(zzxcustr.cust_name, '') AS CHAR(40)))
          END AS cSKU ,
        H.DIVISION ,
        D.STYLE ,
        SZ.size_code AS SIZCODE ,
        ( (D.CMEM_NUM) ) AS CMEM_NUM ,
        ( (D.COLOR_CODE) ) AS COLOR_CODE ,
        ( (H.CO_RATE) ) AS CO_RATE ,
        ( (H.CUSTOMER) ) AS CUSTOMER ,
        ( (D.DIMENSION) ) AS DIMENSION ,
        ( ( H.DISC_AMT ) * H.CO_RATE ) AS DISC_AMT ,
        ( (D.DIVISION) ) AS DIVISION ,
        ( ( H.FRGT_AMT ) * H.CO_RATE ) AS FRGT_AMT ,
        ( ( H.INSU_AMT ) * H.CO_RATE ) AS INSU_AMT ,
        ( (D.LBL_CODE) ) AS LBL_CODE ,
        ( ( H.MISC_AMT ) * H.CO_RATE ) AS MISC_AMT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.RETN_DATE AS VARCHAR), 1, 12))) ) AS RETN_DATE ,
        ( (D.ROYALTY) ) AS ROYALTY ,
        ( (D.ROY_CLS) ) AS ROY_CLS ,
        ( ( SCOL.std_cost ) * H.CO_RATE ) AS SFCCOST ,
        ( ( SC.std_cost * D.TOTAL_QTY ) * H.CO_RATE ) AS SFCDETEXTCOST2 ,

--This is the formula that I am attempting to duplicate in Query 2 for the ReturnAmt
        ( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                          - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,
--------------------------------------------------------------------------------------

        ( ( 1 ) * H.CO_RATE ) AS SFC_CUR_COUNT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12))) ) AS SFDCRDM_DATE ,
        ( (COALESCE(zzxcustr.cust_name, '')) ) AS SFSCUST_NAME ,
        ( (D.STYLE) ) AS STYLE ,
        ( (D.TOTAL_QTY) ) AS TOTAL_QTY ,
        H.CO_CURR AS SFC_CURR_CODE ,
        H.CO_RATE AS SFC_RPTRATE
FROM    zznretnh H
        JOIN zznretnd D ON D.FKEY = H.PKEY
                           AND H.DOC_TYPE = 'CM'
        JOIN zzxscolr SC ON D.DIVISION = SC.division
                            AND D.STYLE = SC.style
                            AND D.COLOR_CODE = SC.color_code
                            AND D.LBL_CODE = SC.lbl_code
                            AND D.DIMENSION = SC.dimension
        JOIN zzxstylr SZ ON SC.fkey = SZ.pkey
        LEFT OUTER JOIN zzxcustr ON H.CUSTOMER = zzxcustr.customer
        LEFT OUTER JOIN zzxscolr SCOL ON D.DIVISION = SCOL.division
                                         AND D.STYLE = SCOL.style
                                         AND D.COLOR_CODE = SCOL.color_code
                                         AND D.LBL_CODE = SCOL.lbl_code
                                         AND D.DIMENSION = SCOL.dimension
        LEFT OUTER JOIN zzxdiscr ON D.DISCOUNT = zzxdiscr.discount
        LEFT JOIN zzordad2 AD ON AD.INV_NUM = D.INV_NUM
                                 AND AD.CUSTOMER = H.CUSTOMER
                                 AND AD.INV_NUM > 0
WHERE   ( (H.CUSTOMER = 'ALE9010') )
        AND ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12)) BETWEEN {TS '2016-10-01 00:00:00' }
                                                              AND
                                                              {TS '2016-10-31 00:00:00' }) );

Open in new window



I want to apply that same logic from Query 1 for the ReturnAmt in Query 2

SELECT  ORD.ship_date ,
        ORD.style ,
        ORD.style_name ,
        ORD.color_code ,
        ORD.lbl_code ,
        SUM(ORD.total_qty) AS total_qty ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END AS IsFOB ,
        SUM(ORD.total_qty * ORD.price) AS GrossAmt ,
        SUM(ORD.total_qty * ORD.price
            - CASE WHEN ( ISNULL(D.disc_perc, 0) <> 0 )
                   THEN ( ORD.total_qty * ORD.price * ( D.disc_perc / 100 ) )
                   ELSE ( 0 )
              END) AS NetAmt ,
-- I need to apply the formula from query 1 using the proper syntax to calculate the ReturnAmt correctly

        ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) AS ExtPrice
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016' AND '09/30/2016' )
               ), 0) AS ReturnAmt ,

-----------------------------------------------------------------------------------------------------------
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls AS Sty_RoyClass ,
        ORD.Dtl_royalty AS Sty_Royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota AS Rights_Code ,
        Q.quota_name AS Rights_Code_Desc ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost AS ExtCost
FROM    [KLL All Order Detail With Style Data] AS ORD
        INNER JOIN zzxcustr AS C ON ORD.customer = C.customer
        INNER JOIN zzxroych AS Roy ON ORD.Dtl_roycls = Roy.ROY_CLS
                                      AND ORD.Dtl_royalty = Roy.ROYALTY
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    style ,
                                    dimension ,
                                    division ,
                                    color_code ,
                                    lbl_code ,
                                    template
                          FROM      zzdcosth
                        ) AS COST ON ORD.dimension = COST.dimension
                                     AND ORD.division = COST.division
                                     AND ORD.style = COST.style
                                     AND ORD.color_code = COST.color_code
                                     AND ORD.lbl_code = COST.lbl_code
        LEFT OUTER JOIN zzxdutyr AS Q ON ORD.Hdr_quota = Q.quota
        LEFT OUTER JOIN zzxdiscr AS D ON ORD.discount = D.discount
WHERE   ( ORD.ship_date BETWEEN '07/01/2016' AND '09/30/2016' )
        AND ( ORD.division = 'ADC' )
GROUP BY ORD.style ,
        ORD.style_name ,
        ORD.ship_date ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls ,
        ORD.Dtl_royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota ,
        Q.quota_name ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost
HAVING  ( ORD.Dtl_roycls IN ( 'CRA' ) )
ORDER BY Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.style ,
        C.cust_name;

Open in new window

0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41868817
Try... updated..

--

SELECT  ORD.ship_date ,
        ORD.style ,
        ORD.style_name ,
        ORD.color_code ,
        ORD.lbl_code ,
        SUM(ORD.total_qty) AS total_qty ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END AS IsFOB ,
        SUM(ORD.total_qty * ORD.price) AS GrossAmt ,
        SUM(ORD.total_qty * ORD.price
            - CASE WHEN ( ISNULL(D.disc_perc, 0) <> 0 )
                   THEN ( ORD.total_qty * ORD.price * ( D.disc_perc / 100 ) )
                   ELSE ( 0 )
              END) AS NetAmt ,
        ISNULL((
		
				SELECT SUM(RD.TOTAL_QTY * RD.PRICE)
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016' AND '09/30/2016' )

               ), 0) ReturnAmt ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls AS Sty_RoyClass ,
        ORD.Dtl_royalty AS Sty_Royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota AS Rights_Code ,
        Q.quota_name AS Rights_Code_Desc ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost AS ExtCost
FROM    [KLL All Order Detail With Style Data] AS ORD
        INNER JOIN zzxcustr AS C ON ORD.customer = C.customer
        INNER JOIN zzxroych AS Roy ON ORD.Dtl_roycls = Roy.ROY_CLS
                                      AND ORD.Dtl_royalty = Roy.ROYALTY
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    style ,
                                    dimension ,
                                    division ,
                                    color_code ,
                                    lbl_code ,
                                    template
                          FROM      zzdcosth
                        ) AS COST ON ORD.dimension = COST.dimension
                                     AND ORD.division = COST.division
                                     AND ORD.style = COST.style
                                     AND ORD.color_code = COST.color_code
                                     AND ORD.lbl_code = COST.lbl_code
        LEFT OUTER JOIN zzxdutyr AS Q ON ORD.Hdr_quota = Q.quota
        LEFT OUTER JOIN zzxdiscr AS D ON ORD.discount = D.discount
WHERE   ( ORD.ship_date BETWEEN '07/01/2016' AND '09/30/2016' )
        AND ( ORD.division = 'ADC' )
GROUP BY ORD.style ,
        ORD.style_name ,
        ORD.ship_date ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls ,
        ORD.Dtl_royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota ,
        Q.quota_name ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost
HAVING  ( ORD.Dtl_roycls IN ( 'CRA' ) )
ORDER BY Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.style ,
        C.cust_name;


--

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41868879
Hi mburk1968,

Have you tried the code above?

Thanks!
0
 

Author Comment

by:mburk1968
ID: 41868909
Pawan,

I have and it provides me with the same output of the original syntax. It's not factoring in the calculation on disc_perc or CO_RATE.

Original System Query

SELECT  CASE WHEN D.DIVISION = '' THEN SPACE(3)
             ELSE ' Division: ' + RTRIM(CAST(D.DIVISION AS CHAR(3)))
        END + CASE WHEN H.CUSTOMER = '' THEN SPACE(8)
                   ELSE ' Customer: ' + RTRIM(CAST(H.CUSTOMER AS CHAR(8)))
              END
        + CASE WHEN COALESCE(zzxcustr.cust_name, '') = '' THEN SPACE(40)
               ELSE ' Customer Name: '
                    + RTRIM(CAST(COALESCE(zzxcustr.cust_name, '') AS CHAR(40)))
          END AS cSKU ,
        H.DIVISION ,
        D.STYLE ,
        SZ.size_code AS SIZCODE ,
        ( (D.CMEM_NUM) ) AS CMEM_NUM ,
        ( (D.COLOR_CODE) ) AS COLOR_CODE ,
        ( (H.CO_RATE) ) AS CO_RATE ,
        ( (H.CUSTOMER) ) AS CUSTOMER ,
        ( (D.DIMENSION) ) AS DIMENSION ,
        ( ( H.DISC_AMT ) * H.CO_RATE ) AS DISC_AMT ,
        ( (D.DIVISION) ) AS DIVISION ,
        ( ( H.FRGT_AMT ) * H.CO_RATE ) AS FRGT_AMT ,
        ( ( H.INSU_AMT ) * H.CO_RATE ) AS INSU_AMT ,
        ( (D.LBL_CODE) ) AS LBL_CODE ,
        ( ( H.MISC_AMT ) * H.CO_RATE ) AS MISC_AMT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.RETN_DATE AS VARCHAR), 1, 12))) ) AS RETN_DATE ,
        ( (D.ROYALTY) ) AS ROYALTY ,
        ( (D.ROY_CLS) ) AS ROY_CLS ,
        ( ( SCOL.std_cost ) * H.CO_RATE ) AS SFCCOST ,
        ( ( SC.std_cost * D.TOTAL_QTY ) * H.CO_RATE ) AS SFCDETEXTCOST2 ,
-------------------------------------------------------------------------------------
        ( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                          - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,
--------------------------------------------------------------------------------------

        ( ( 1 ) * H.CO_RATE ) AS SFC_CUR_COUNT ,
        ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12))) ) AS SFDCRDM_DATE ,
        ( (COALESCE(zzxcustr.cust_name, '')) ) AS SFSCUST_NAME ,
        ( (D.STYLE) ) AS STYLE ,
        ( (D.TOTAL_QTY) ) AS TOTAL_QTY ,
        H.CO_CURR AS SFC_CURR_CODE ,
        H.CO_RATE AS SFC_RPTRATE
FROM    zznretnh H
        JOIN zznretnd D ON D.FKEY = H.PKEY
                           AND H.DOC_TYPE = 'CM'
        JOIN zzxscolr SC ON D.DIVISION = SC.division
                            AND D.STYLE = SC.style
                            AND D.COLOR_CODE = SC.color_code
                            AND D.LBL_CODE = SC.lbl_code
                            AND D.DIMENSION = SC.dimension
        JOIN zzxstylr SZ ON SC.fkey = SZ.pkey
        LEFT OUTER JOIN zzxcustr ON H.CUSTOMER = zzxcustr.customer
        LEFT OUTER JOIN zzxscolr SCOL ON D.DIVISION = SCOL.division
                                         AND D.STYLE = SCOL.style
                                         AND D.COLOR_CODE = SCOL.color_code
                                         AND D.LBL_CODE = SCOL.lbl_code
                                         AND D.DIMENSION = SCOL.dimension
        LEFT OUTER JOIN zzxdiscr ON D.DISCOUNT = zzxdiscr.discount
        LEFT JOIN zzordad2 AD ON AD.INV_NUM = D.INV_NUM
                                 AND AD.CUSTOMER = H.CUSTOMER
                                 AND AD.INV_NUM > 0
WHERE   ( (H.CUSTOMER = 'ALE9010') )
        AND ( (CONVERT(DATETIME, SUBSTRING(CAST(H.CMEM_DATE AS VARCHAR), 1, 12)) BETWEEN {TS '2016-07-01 00:00:00' }
                                                              AND
                                                              {TS '2016-09-30 00:00:00' }) );

Open in new window


I need to apply this formula from the above


( ( TOTAL_QTY * ( ( PRICE * TOTAL_QTY )
                          - ( ROUND(( COALESCE(zzxdiscr.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * H.CO_RATE ) AS SFC_CR_EXT_NET_AMT ,

Open in new window


To work in this Query

SELECT  ORD.ship_date ,
        ORD.style ,
        ORD.style_name ,
        ORD.color_code ,
        ORD.lbl_code ,
        SUM(ORD.total_qty) AS total_qty ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END AS IsFOB ,
        SUM(ORD.total_qty * ORD.price) AS GrossAmt ,
        SUM(ORD.total_qty * ORD.price
            - CASE WHEN ( ISNULL(D.disc_perc, 0) <> 0 )
                   THEN ( ORD.total_qty * ORD.price * ( D.disc_perc / 100 ) )
                   ELSE ( 0 )
              END) AS NetAmt ,
        ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE)
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016'
                                           AND     '09/30/2016' )
               ), 0) ReturnAmt ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls AS Sty_RoyClass ,
        ORD.Dtl_royalty AS Sty_Royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota AS Rights_Code ,
        Q.quota_name AS Rights_Code_Desc ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost AS ExtCost
FROM    [KLL All Order Detail With Style Data] AS ORD
        INNER JOIN zzxcustr AS C ON ORD.customer = C.customer
        INNER JOIN zzxroych AS Roy ON ORD.Dtl_roycls = Roy.ROY_CLS
                                      AND ORD.Dtl_royalty = Roy.ROYALTY
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    style ,
                                    dimension ,
                                    division ,
                                    color_code ,
                                    lbl_code ,
                                    template
                          FROM      zzdcosth
                        ) AS COST ON ORD.dimension = COST.dimension
                                     AND ORD.division = COST.division
                                     AND ORD.style = COST.style
                                     AND ORD.color_code = COST.color_code
                                     AND ORD.lbl_code = COST.lbl_code
        LEFT OUTER JOIN zzxdutyr AS Q ON ORD.Hdr_quota = Q.quota
        LEFT OUTER JOIN zzxdiscr AS D ON ORD.discount = D.discount
WHERE   ( ORD.ship_date BETWEEN '07/01/2016'
                        AND     '09/30/2016' )
        AND ( ORD.division = 'ADC' )
GROUP BY ORD.style ,
        ORD.style_name ,
        ORD.ship_date ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.price ,
        ORD.discount ,
        C.cust_name ,
        CASE WHEN ( COST.template LIKE '%FOB' ) THEN ( 'FOB' )
             WHEN ( COST.template IS NULL ) THEN ( 'NONE' )
             ELSE ( 'STD' )
        END ,
        ORD.customer ,
        ORD.division ,
        ORD.Dtl_roycls ,
        ORD.Dtl_royalty ,
        Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.Hdr_quota ,
        Q.quota_name ,
        ORD.Style_StdCost ,
        ORD.total_qty * ORD.Style_StdCost
HAVING  ( ORD.Dtl_roycls IN ( 'CRA' ) )
ORDER BY Roy.ROY_CLS_NAME ,
        Roy.ROY_NAME ,
        ORD.style ,
        C.cust_name;

Open in new window


Specifically to replace this.

ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE)
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016'
                                           AND     '09/30/2016' )
               ), 0) ReturnAmt ,

Open in new window

0
 

Author Comment

by:mburk1968
ID: 41868974
I'm trying to do something like this...

ISNULL(( SELECT SUM(RD.TOTAL_QTY * RD.PRICE) - ( COALESCE(D.disc_perc, 0) * 1.0
                                      / 100 ) * ) ) )
            / CASE WHEN TOTAL_QTY = 0 THEN 1
                   ELSE TOTAL_QTY
              END ) * RH.CO_RATE ) 
                 FROM   zznretnd AS RD
                        INNER JOIN zznretnh AS RH ON RD.FKEY = RH.PKEY
                 WHERE  ( RD.DIVISION = ORD.division )
                        AND ( RD.STYLE = ORD.style )
                        AND ( RD.COLOR_CODE = ORD.color_code )
                        AND ( RD.CUSTOMER_CASCADED = ORD.customer )
                        AND ( RH.CMEM_DATE BETWEEN '07/01/2016'
                                           AND     '09/30/2016' )
               ), 0) ReturnAmtTest ,

Open in new window

0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41871590
Hi mburk1968,

Could you please explain this more so that we can move forward with this?
0
 

Author Comment

by:mburk1968
ID: 41874039
I'm not certain what else I can write to make it more clear. Query 1 from above is from a report that runs in our software application. I was able to extract the SQL code using SQL profiler. In query 1 it calculates the Return Amount for merchandise AS SFC_CR_EXT_NET_AMT. I am creating a report in SSRS and want to alter the code for ReturnAmount to factor in items that had a disc_perc. I simply cannot seem to get the syntax right.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now