Are Multiple SQL Statements in SQL Designer Possible?

[hawg]

Using: Crystal Reports 8.5 with Informix database

I want the Crystal SQL Designer to issue 2 or 3 separate SQL statements against my database. From what I can tell SQL Designer can only handle one SQL statement, is there a work around?

My first statement will be to set the user role to read only (SET ROLE readonly Then the second will be to select fields from 2 tables into a temp table and the third statement will be to join a third and fourth table with my temp table and produce the record set that the report will be built on.

Thanks in advance for any insight.

 

[synapsevampire]

SQL Designer is a dying product, I suggest you not use it.

You also might want to mention what version of Crystal you are using, as the newest, CR 9, supports real SQL, and older versions have many limitations.

Earlier versions of Crystal allow UNIONS, and directly from within a report.

If what you are meaning to say is that you want to show information from multiple select statements, as separate data sets in a report, most would call that several reports, and Crystal allows for this using subreports.

If this answer doesn't help you, please post table layout(s), example data, and expected output.

-k

 

[hawg]

synapsevampire -
crystal reports 8.5 developer ed. with Informix database

Thanks for the response -- I wouldn't use SQL Designer by choice, but I am trying to accomodate our DBA and his security concerns.

I believe a UNION could combine two of my SQL statements into one, but that won't solve the issue that when I login to the database the first statement I have to issue is "SET ROLE readonly;" (this statement just forces the users permissions to read only and is part of our security specs on the database) I can't issue a SELECT statement until I have first sent the set role statement.

I would offer the table structures and expected layout, but they aren't quite the focus for me right now because I just want to know if I can issue two statements within SQL designer.

Because I have not been able to place two sql statements into one SQL Designer file, I thought of building a report on the SET ROLE statement and then a subreport on the SELECT statement, but it appears the sql statement must return data for a report to be built on that query.

 

[Reebo99]

I haven't used SQL designer for a while now, but can't you create 2 different SQL designer files then report from them?

Reebo
Scotland (Sunny with a Smile)

 

[hawg]

Reebo -

Crystal User manual states that only one data source is allowed when using a query file as the data source.

I tried anyway - but wasn't able to use that method.

For now it looks like there is not a way to issue my SET Role statement.

 

[synapsevampire]

Use a Stored Procedure.

-k

 

[AlexB1234]

I'm using Crystal Reports for ASP.NET and I can pass it any number of SQL queries as "tables" and in Crystal join those "tables", so I can generate pretty complex data sets made up of other datasets. I don't know if you could do that in Crystal 4, though.

You could also run however number of SQL statements and dump the data into a temporary table and use the temp table to drive the report.