Solved

SQL 2012 Syntax Error

Posted on 2016-11-02
5
24 Views
Last Modified: 2016-11-16
I am receiving a syntax error on the commented block of code.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ','.

When I double click the error it highlights the following.

                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )

Open in new window



Full 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) 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 ,
        --ISNULL( (SELECT SUM(RD.TOTAL_QTY * RD.PRICE) - ( ( COALESCE(D.disc_perc, 0) * 1.0
        --                              / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
        --    / 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 ,
        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 ,
		D.disc_perc
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
Comment
Question by:mburk1968
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:neehar gollapudi
ID: 41870245
does this column D.disc_perc  have any 0 ? Whats the data type?
0
 

Author Comment

by:mburk1968
ID: 41870329
It's numeric 5, 2 contains 65 rows and one of them is = 0.00
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41870671
can you explain me below , What is this 2.

 , 2)

ISNULL( (SELECT SUM(RD.TOTAL_QTY * RD.PRICE) - ( ( COALESCE(D.disc_perc, 0) * 1.0
                                      / 100 ) * D.PRICE * TOTAL_QTY, 2) ) )
0
 

Author Comment

by:mburk1968
ID: 41870709
I'm not fully certain. I'm trying to apply this calculation to my query from the one below which is a UI query. I commented out the code that I'm attempting to use.

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

0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41870716
Could you please post some sample data and the expected output ?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

20 Experts available now in Live!

Get 1:1 Help Now