Nested SQL Query in CR10 not working

25 Views

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