Hi. My query is working on SQL Server but in Crystal Reports has 3 errors:
"Failed to retrieve data from the database.
Details: ADo Error Code: 0x80040e14
Source: Microsoft SQl Server Native Client 11.0
Description: Incorrect Syntax near ') as '.
SQL Sate: 42000
Native Error: 1021 [Database Vendor Code: 102]"
"Failed to retrieve data from the database.
Details: ADo Error Code: 0x80040e14
Source: Microsoft SQl Server Native Client 11.0
Description: Unclosed quotation mark after the character string ''.
SQL Sate: 42000
Native Error: 1021 [Database Vendor Code: 102]"
"Failed to retrieve data from the database."
My query is:
-- FATURAS
SELECT T100.DOCNUM, T100.INVTYPE, T100.[DOC. TYPE], T100.DOCTOTAL, T100.[PAID ON TIME], T100.[PAID BEFORE], T100.DOCTOTALFC, T100.SELLER,
T100.CARDCODE, T100.CARDNAME, T100.DOCCUR, T100.COUNTRY, T100.DOCDATE, T100.PAIDSUM, T100.BRAND, T100.COMISSION, T100.COLLECTION
FROM (SELECT
OV.DOCNUM AS 'DOCNUM',
IR.SRCOBJTYP AS 'INVTYPE',
CASE IR.SRCOBJTYP WHEN '13' THEN 'INVOICE' ELSE 'CREDIT NOTE' END AS 'DOC. TYPE',
(OV.DOCTOTAL-OV.VATSUM-OV.TOTALEXPNS) AS 'DOCTOTAL',
(OV.DOCTOTALFC-OV.VATSUMFC-OV.TOTALEXPFC) AS 'DOCTOTALFC',
S.SLPNAME AS 'SELLER',
OT.RECONDATE AS 'DOC. DATE',
OV.CARDCODE AS 'CARDCODE',
OV.CARDNAME AS 'CARDNAME',
OT.ReconCurr as 'DOCCUR',
OC.Country,
CONVERT(DATE, OT.RECONDATE) AS 'DOCDATE',
OV.paidsum,
(SELECT COALESCE (SUM(OI.TOTAL),0) FROM OITR OI
INNER JOIN ITR1 IR ON IR.RECONNUM = OI.RECONNUM
WHERE IR.SrcObjAbs = OV.DOCENTRY AND OT.RECONDATE < {?@DateStart}) as 'PAID BEFORE',
(SELECT SUM(OI.TOTAL) FROM OITR OI
INNER JOIN ITR1 IR ON IR.RECONNUM=OI.RECONNUM
WHERE IR.SrcObjAbs = OV.DOCENTRY) AS 'PAID ON TIME',
(select top 1 U_ARGNS_Brand from OITM
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) as 'Brand',
(select top 1 U_ARGNS_Coll from OITM
INNER JOIN inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) as 'Collection',
(Select L.U_Comission
from [@SBSS_COMISS_VEND_I] L Inner Join [@SBSS_COMISS_VEND_H] C on L.DocEntry = C.DocEntry
Where U_Brand = (select top 1 U_ARGNS_Brand from OITM inner join inv1 on inv1.itemcode = oitm.ItemCode where inv1.docentry = OV.DOCENTRY) and U_Collection =
(select top 1 U_ARGNS_Coll from OITM inner join inv1 on inv1.itemcode = oitm.ItemCode
where inv1.docentry = OV.DOCENTRY) and U_Seller = '{?DocKey@SELECT SLPNAME FROM OSLP}') as 'Comission'
FROM ITR1 IR
INNER JOIN OITR OT ON OT.RECONNUM = IR.RECONNUM
INNER JOIN OINV OV ON OV.DOCENTRY = IR.SRCOBJABS
INNER JOIN OSLP S ON S.SLPCODE = OV.SLPCODE
INNER JOIN OCRD OC ON OC.CARDCODE = OV.CARDCODE
WHERE
OT.RECONDATE >= {?@DateStart} AND OT.RECONDATE <= {?@DateEnd} AND ir.SRCOBJTYP <> 203 -- ADC
AND OV.CARDCODE IN {?@DocKey@FROM OCRD} AND IR.ISCREDIT = 'D' AND S.SLPNAME IN ('{?DocKey@SELECT SLPNAME FROM OSLP}')
group by OV.DOCNUM, IR.SRCOBJTYP, OV.DOCTOTAL, OV.DOCTOTALFC,
S.SLPNAME,
OV.DOCDATE,
OV.CARDCODE,
OV.CARDNAME,
OV.VATSUM,
OV.VATSUMFC,
OV.DISCSUM,
OV.TOTALEXPNS,
OV.TOTALEXPFC,
OT.ReconCurr,
OC.Country,
OV.paidsum, ot.recondate, ov.docentry, ir.srcobjabs) as T100
WHERE T100.DOCTOTAL = T100.[PAID ON TIME]+T100.[PAID BEFORE]
UNION ALL
-- NOTAS DE CRÉDITO
SELECT T100.DOCNUM, T100.INVTYPE, T100.[DOC. TYPE], T100.DOCTOTAL, T100.[PAID ON TIME], T100.[PAID BEFORE], T100.DOCTOTALFC, T100.SELLER,
T100.CARDCODE, T100.CARDNAME, T100.DOCCUR, T100.COUNTRY, T100.DOCDATE, T100.PAIDSUM, T100.BRAND, T100.COMISSION, T100.COLLECTION
FROM (SELECT
OV.DOCNUM AS 'DOCNUM',
IR.SRCOBJTYP AS 'INVTYPE',
CASE IR.SRCOBJTYP WHEN '14' THEN 'INVOICE' ELSE 'CREDIT NOTE' END AS 'DOC. TYPE',
((OV.DOCTOTAL-OV.VATSUM-OV.TOTALEXPNS)*(-1)) AS 'DOCTOTAL',
((OV.DOCTOTALFC-OV.VATSUMFC-OV.TOTALEXPFC)*(-1)) AS 'DOCTOTALFC',
S.SLPNAME AS 'SELLER',
OT.RECONDATE AS 'DOC. DATE',
OV.CARDCODE AS 'CARDCODE',
OV.CARDNAME AS 'CARDNAME',
OT.ReconCurr as 'DOCCUR',
OC.Country,
CONVERT(DATE, OT.RECONDATE) AS 'DOCDATE',
OV.paidsum,
(SELECT COALESCE (SUM(OI.TOTAL),0) FROM OITR OI
INNER JOIN ITR1 IR ON IR.RECONNUM=OI.RECONNUM
WHERE IR.SrcObjAbs = OV.DOCENTRY AND OT.RECONDATE < {?@DateStart}') as 'PAID BEFORE',
((OV.DOCTOTAL-OV.VATSUM-OV.TOTALEXPNS)*(-1)) AS 'PAID ON TIME',
(select top 1 U_ARGNS_Brand from OITM
INNER JOIN RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = OV.DOCENTRY) as 'Brand',
(select top 1 U_ARGNS_Coll from OITM
INNER JOIN RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = OV.DOCENTRY) as 'Collection',
(Select L.U_Comission
from [@SBSS_COMISS_VEND_I] L Inner Join [@SBSS_COMISS_VEND_H] C on L.DocEntry = C.DocEntry
Where U_Brand = (select top 1 U_ARGNS_Brand from OITM inner join RIN1 on RIN1.itemcode = oitm.ItemCode where RIN1.docentry = OV.DOCENTRY)
and U_Collection =
(select top 1 U_ARGNS_Coll from OITM inner join RIN1 on RIN1.itemcode = oitm.ItemCode
where RIN1.docentry = OV.DOCENTRY) and U_Seller = '{?DocKey@SELECT SLPNAME FROM OSLP}') as 'Comission'
FROM ITR1 IR
INNER JOIN OITR OT ON OT.RECONNUM = IR.RECONNUM
INNER JOIN ORIN OV ON OV.DOCENTRY = IR.SRCOBJABS
INNER JOIN OSLP S ON S.SLPCODE = OV.SLPCODE
INNER JOIN OCRD OC ON OC.CARDCODE = OV.CARDCODE
WHERE
OT.RECONDATE >= {?@DateStart} AND OT.RECONDATE <= {?@DateEnd} AND ir.SRCOBJTYP = 14 -- CREDIT NOTE
AND OV.CARDCODE IN {?@DocKey@FROM OCRD} AND IR.ISCREDIT = 'C' AND S.SLPNAME IN ('{?DocKey@SELECT SLPNAME FROM OSLP}')
group by OV.DOCNUM, IR.SRCOBJTYP, OV.DOCTOTAL, OV.DOCTOTALFC,
S.SLPNAME,
OV.DOCDATE,
OV.CARDCODE,
OV.CARDNAME,
OV.VATSUM,
OV.VATSUMFC,
OV.DISCSUM,
OV.TOTALEXPNS,
OV.TOTALEXPFC,
OT.ReconCurr,
OC.Country,
OV.paidsum, ot.recondate, ov.docentry, ir.srcobjabs) as 'T100'
WHERE T100.DOCNUM > '0'
Can someone help me?
Thanks.