Going mad can I get some help please.

[Former Member]

I have installed Crystal Reports on my computer and now im trying to get my head round it but I have hit a wall.

I cant seem to be able to get the below statement to work does it not like any sql statements?

FROM Computer_List_All

WHERE Computer_List_All.Asset_Tag NOT IN
     (SELECT Discovered_Software_Product_List.Computer_Asset_Tag 

FROM Discovered_Software_Product_List

     WHERE Product_Name LIKE 'NOD32 antivirus system')

ORDER BY Computer_List_All.Asset_Tag

Thanks in advance for any help.

Tony

Edited by: Tony Vincent on Sep 18, 2008 4:48 PM

Edited by: Tony Vincent on Sep 18, 2008 4:49 PM

[Former Member · Accepted solution]

Tony,

CR loves SQL. It's just a crappy place to write and test SQL.

If you can get the SQL to run in the native application (SQL Management Studio for MS SQL Server) you should be able to paste it directly into a SQL Command in CR and have it work just fine.

Just make sure you are using the correct driver for your database.

Note: You may want to change WHERE Product_Name LIKE 'NOD32 antivirus system'

to

WHERE Discovered_Software_Product_List.Product_Name LIKE 'NOD32 antivirus system' , just to stick with convention.

Jason

[Former Member]

Thanks for the help the command worked fine.

Tony

[Former Member]

I have not been trying to add the sql its self but trying to convert, thats where im having the problems and the where and not in commands dont seem to work.

Is there a place to add sql code in to it? on a report

[Former Member]

I don't think you are going to be able to replicate the NOT IN part of your SQL using the Crystal interface.

You can however add your complex SQL to the report as a datasource. From the Datasource menu, under your datasource connection you should see Add Command. Click on it and a new dialog will apprear. Paste your working SQL here and then click OK. You can now use this as your data source instead of the tables themselves.

[Former Member]

Brian,

As long as the code is place in a Command, the NOT in will work just fine. Like I said, If it will run in the SQL Management Studio, it will run in CR.

Jason

[Former Member]

Jason:

I know it will work in a SQL Command, you just can't replicate it in Crystal without using a Command, which is what I was trying to say.

Brian

[Former Member]

Gotcha. I read read and see what you were saying.

Jason

[Former Member]

Tony,

What exactly are you trying to do? Where are you trying to add your SQL?

Jason

[Former Member]

Where are you trying to add this SQL?