SQL Query to Crystal Report

Question

Need the results from this query:

SELECT  CAST(a.InvoiceDate AS DATE) InvoiceDate,
        COUNT(*) RecordCount
  FROM SalesOrder a
  JOIN IMSSalesExtractHistory b
    ON a.SlsOrdNbr = b.SlsOrdNbr
   AND a.OrdLnNbr = b.OrdLnNbr
   AND a.OrdLnSeqNbr = b.OrdLnSeqNbr
 WHERE b.SAFInsertDate > GETDATE()-2
GROUP BY CAST(a.InvoiceDate AS DATE)
ORDER BY CAST(a.InvoiceDate AS DATE)

to be replicated into my Crystal Report.

I have started this formula in the Formula Workshop window within Crystal Reports 2013 but it keeps giving me this error:

(
  SELECT DISTINCT CAST("SalesOrder"."InvoiceDate" AS DATE) InvoiceDate
    FROM "SalesOrder"
    JOIN "IMSSalesExtractHistory"
      ON "SalesOrder"."SlsOrdNbr" = "IMSSalesExtractHistory"."SlsOrdNbr"
     AND "SalesOrder"."OrdLnNbr" = "IMSSalesExtractHistory"."OrdLnNbr"
     AND "SalesOrder"."OrdLnSeqNbr" = "IMSSalesExtractHistory"."OrdLnSeqNbr"
   WHERE "IMSSalesExtractHistory"."FileDate" > {fn CURDATE()}
)

Error:

SAP Crystal Reports

Error in compiling SQL Expression : Failed to retrieve data from the database. Details: ADO Error Code: 0x80040e07 Source: Microsoft OLE DB Provider for SQL Server Description: Conversion failed when converting the varchar value '2016-05-25' to data type int. SQL State: 22018

Native Error: 245 [Database Vendor Code: 245 ].

OK

I removed the COUNT(*) from the CS query because I was going to use the same code when I got it working and find the count for a new column within my report.

Looking for someone to help convert the initial SQL query so that it has no errors in Crystal Reports.

EDIT: For reference, this is the result set I need in Crystal Report that I generated from the SQL script above.

 InvoiceDate    RecordCount
 2016-05-13     16074
 2016-05-14     2
 2016-05-15     4
 2016-05-16     27495
 2016-05-17     20023
 2016-05-18     18923
 2016-05-19     18944

Are you passed parameter? please check stackoverflow.com/questions/8600435/database-connector-error — Ajay2707
Have you tried to replace DISTINCT CAST("SalesOrder"."InvoiceDate" AS DATE) with DateValue("SalesOrder"."InvoiceDate")? — KuKeC

Siva Siva · Accepted Answer · 2016-05-25T08:22:38

Start crystal reports and make database connection.

instead of selecting tables select add command and paste this query

SELECT  CAST(a.InvoiceDate AS DATE) InvoiceDate,
        COUNT(*) RecordCount
  FROM SalesOrder a
  JOIN IMSSalesExtractHistory b
    ON a.SlsOrdNbr = b.SlsOrdNbr
   AND a.OrdLnNbr = b.OrdLnNbr
   AND a.OrdLnSeqNbr = b.OrdLnSeqNbr
 WHERE b.SAFInsertDate > GETDATE()-2
GROUP BY CAST(a.InvoiceDate AS DATE)
ORDER BY CAST(a.InvoiceDate AS DATE)

Now got to design and place the required columns in detail sectio.

SQL Query to Crystal Report

SAP Crystal Reports

Native Error: 245 [Database Vendor Code: 245 ].

OK

1 Answers