SQL 2012 Syntax Error
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.
Full query
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) ) )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;
neehar gollapudi
does this column D.disc_perc have any 0 ? Whats the data type?
ASKER
It's numeric 5, 2 contains 65 rows and one of them is = 0.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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' }) );
Could you please post some sample data and the expected output ?