Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Nested Selects

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
replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question