SQLTeam.com | Weblogs | Forums

Error SQL and Crystal Reports


#1

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.


#2

I am not sure about the error. But could you please try by creating a View and use in Crystal report


#3

I am getting following error while previewing Crystal report.

Logon failed.
Details: ADO Error Code: 0x80040e4d
Source: Microsoft OLE DB Provider for SQL Server
Description: Login failed for user 'sa'.
SQL State: 42000
Native Error: 18456

The report can be viewed for say 10 Sales Order Entries except one entry. Data looks ok for all entries.
Can I get help on this?


#4

Specify the crystal report database login details in the application code

https://msdn.microsoft.com/en-us/library/ms226065(v=vs.80).aspx