Retrieve data in Crystal Reports using SQL
Posted on 2002-04-08
I am using Crystal Reports 8.5 Developer, pdsoledb.dll ODBC database driver
I need to find out if it is possible to retrieve specific values from a database using SQL or equivalent, separate from the main query / Select Expert.
Here's the situation:
I have an invoice with line items in the details section. The report receives the customer number and invoice number as parameters.
I have 3 tables:
The Customer Master contains 2 separate FK columns (primary billto address, primary shipto address) which each reference a primary key column in the Address Master table. In the Report Header, I need to display both the shipto and billto addresses for this customer.
Now it would make perfect sense to be able to use the SQL expression:
'Select address1, address2, address3, city
Where AddressMaster.AddressCode = CustomerMaster.PrimaryBilltoAddress
somewhere in Crystal Reports and magically be able to format the results, but in all my searches I have yet to come across a way to do this.
However, in the Crystal Reports Help there is a section describing that you can use SQL Expression Fields to enter SQL expressions, but whenever I try to enter SQL, I get a syntax error stating that there is an error near 'Select'. Is there some mysterious syntax involved here, and if so, where do I find help on this?
thank you in advance