Nested Selects

[Former Member]

Hi, how could i use nested selects in Crystal reports like this:

SELECT *

FROM table

WHERE number=1

AND prize IS NOT NULL

AND number NOT IN (SELECT number FROM table WHERE number=1 AND prize IS NULL)

I want the items with prize in a data and that doesnt have null prizes in other datas.

Thanks

[Former Member · Accepted solution]

Hi Antonio ,

You can use the the command object, i.e. While creating connection from Database Expert you can

use the Add Command to add required query.

Please try and let me know the results.

Thanks,

Neeraj

[Former Member · Accepted solution]

Excuse me, can you tell me where the command object is?? I create a connection in the Database Explorer, i don't know where the Database Expert is. Sorry but im a little lost. Thanks

[Former Member · Accepted solution]

You want to execute the query and create the report ?

Or you want to apply this query on report data?

Thanks

Tej

[Former Member · Accepted solution]

I want to apply this query in a report data. I have a blank report in eclipse and i use to put the query in the Record Filter formula. But i have never put nested selects and i dont know how to put it in crystal. Hope i explain myself, im not too good in english.

[Former Member · Accepted solution]

You can create an SQL command object and add it to a Crystal report as a database table.

1.Click File > New > Other.

The "New" wizard appears.

2.Expand SQL Development, select SQL File, and click Next.

3.In the File name box, type the name of the SQL file.

For example, type MySQLCommand.

4.In the Database server type list, select the database server.

For example, select Derby_10.x.

5.In the Connection profile name list, select the name of the connection profile.

For example, select Xtreme Sample Database.

6.In the Database name list, select the name of the database.

For example, select Xtreme.

7.Click Finish.

The "SQL File editor" opens.

8.Type the SQL command in the "SQL File editor", and click Save.

For example, type the following SQL Statement.

SELECT

Customer.`Customer ID`,

Customer.`Customer Name`,

Customer.`Last Year's Sales`,

Customer.`Region`,

Customer.`Country`,

Orders.`Order Amount`,

Orders.`Customer ID`,

Orders.`Order Date`

FROM

Customer Customer INNER JOIN Orders Orders ON

Customer.`Customer ID` = Orders.`Customer ID`

WHERE

(Customer.`Country` = 'USA' OR

Customer.`Country` = 'Canada') AND

Customer.`Last Year's Sales` < 10000.

ORDER BY

Customer.`Country` ASC,

Customer.`Region` ASC

9.Click File > Save.

10.Right-click anywhere in the "SQL File editor" page, and select one of the following two options:

Click Crystal Reports > Add To New Report to create a new report. This report uses the SQL command result as a database table.

Click Crystal Reports > Add To Existing Report to add the SQL command result to an existing report.

Hope this helps!!!

Thanks,

Tej

[Former Member · Accepted solution]

Thank you very much! You solved me the problem!!