Nested SQL Query in CR10 not working

[Former Member]

Hi

I have a SQL query See below but crashing CR

SELECT ax_Staff.ax_Name, ax_Staff.ax_Reference_Number, ax_Licence.Rn_Descriptor, ax_Licence.ax_Expiry_Date, ax_Department.ax_Name AS Department

FROM ax_Staff INNER JOIN

ax_Department ON ax_Staff.ax_Department_Id = ax_Department.ax_Department_Id LEFT OUTER JOIN

ax_Licence ON ax_Staff.ax_Staff_Id = ax_Licence.ax_Staff_Id

Where ax_Staff.ax_Reference_Number not in (SELECT ax_Staff.ax_Reference_Number

FROM ax_Staff INNER JOIN

ax_Licence ON ax_Staff.ax_Staff_Id = ax_Licence.ax_Staff_Id

WHERE (ax_Licence.Rn_Descriptor LIKE 'Driving Licence - Rider (R)'))

ORDER BY Department, ax_Staff.ax_Name

The query is working on MS SQL server 2005 and giving results as I want, but I have problem to recreate this in CR.Can anybody help how to creat the report???? where Looking for list of Staff who has no Particular Licence

Sample of the report:

GH1: Administarion

GH2: Peter Brown StaffID 4442 --- THIS Group IS OK to be on the list

GH2: John Smith StaffID 4444 -

---

THIS whole GROUP NEEDS TO BE SUPPRESED or EXCLUDED

Details: Driving Licence Car (C)

Details: Driving Licence Rider (R) --this record passed condition

Details: Driving Licence HR (HR)

GH2: Julia Ng StaffID 4446 --- THIS Group IS OK to be on the list

Details: Driving Licence C

I have 3 tables

Department

Staff

Licence

[Former Member]

Hi Stefan,

I think the driver which you are using to connect to your database is unable to process the SQL statement . Try to change the datasource (ODBC, OLE DB or Native) and run the report.

Thanks,

Sastry

[Former Member]

Hi Sastry,

Connection to the database is trough Pivotal, I have tried ODBC connection as well but same error

The instruction at "0x005d53e0" refereced memory at "0x00000014'. The memory could not be "read".

With ODBC I have always compilation error saying

Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL server] Incorrect syntax near the keyword 'SELECT'. '.

but even the simple select statment is showing the error.

Do you think the error is with Drivers???

Regards

Stefan

[Former Member]

Hi

MS SQL Server 2005 is not a tested Database with Crystal Reports 10 (please refer to the supported platforms document for CR10).

However you can have a look for general troubleshooting steps in this [PDF|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/40838c3f-6d1e-2b10-baa7-923cb79094a9]

Hope this helps!!!

Regards

Sourashree

[former_member11696]

Check your DSN porperties and check Case insensitive. CR typically requires the "DB"."DBO"."Table" to be wrapped in single or double quotes.

Try creating a new report off the dabase directly with one or two tables and see what CR expects the SQL to be like. If you are using Command Objects though CR simply passes the SQL directly.

When testing in SQL Server you are not using their ODBC driver but using the native or OLE DB driver so it will work without double quotes. ODBC however requires them, if enabled.

Thank you

Don

[Former Member]

Hi

Writing SQL query in Crystal Reports is different from what we write for databases.

Please refer to this [PDF|http://www.informit.com/content/images/0789734176/downloads/AppendixA.pdf] for a understanding on how to write SQL query for Crystal Reports.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Thank you Sourashree,

It is just strange that once saving script using SQL expression Editor on CR did not detected any errors.

I will look at the PDF and let you know if fixed

Thank you again

Regards

Stefan