SQL Queries via Crystal Reports 9 1

[kc27]

I've been provided a sql query to run against my database.

The Crystal Reports 9 Help file says that this can be used via the following instructions (excerpt below)

I don't see the Crystal SQL Designer as an option for me. I reinstalled Crystal Reports and still don't see it. Is it possible that it was not included with my version of Crystal Reports. I am running the full version of Crystal Reports, ver. 9.2.2.634

What do I need to do to use a SQL query in Crystal?
.
Excerpt from Crystal Reports Help File:
Using an SQL query that was designed elsewhere
Copy the SQL statement to the Windows Clipboard.
Most SQL editors allow the SQL statement to be copied to the Clipboard by using the Ctrl-C key combination.

From the Start menu, select Programs, select Crystal Reports Tools, then click Crystal SQL Designer.

 

[rbh123456789]

If you go to the Database Expert, do you have the 'Add a Command' option?

 

[kc27]

Yes, "Add a Command" is available.

 

[lbass]

Add the query to the "add command" area. If you are using any parameters, create them within the command screen on the right.

-LB

 

[kc27]

Thanks so much for the help. I went to the Database Expert, double clicked on "Add A Command" and entered in the SQL query and was able to get results back.

Once the query produces results, I am supposed to further filter it by running a second and third query off of those results. For example:

select * from db.table where typeid in (typeids from the previous query)

then run

select * from db.table where pformatid in (pformatids found in the first query)

I tried filtering by entering the formula via Select Expert, then choosing the field, selecting formula from the drop down list, and entering "select * from db.table where typeid in", but that returns a message that states "A number, currency amount, boolean, date, time, date-time, or string is expected here.

I also tried Report > Selection Formula > Record. That did not work, either.

How do I do the secondary filters of the results from the first query?

 

[lbass]

These layers of selection should built into the initial command.

Although you could add selection criteria in report->selection formula->record like the following:

{command.typeid} in [your criteria here] and
{command.pformatid} in [your criteria here]

...but the filtering would occur locally. Why not add this into your where clause in the original query?

-LB

 

[kc27]

Thanks for the help. I feel as though I am getting closer. What you say makes sense, but based on the two statesments below, I do not understand what criteria is being specified.

I was told to run

select * from db.table where typeid in (typeids from the previous query)

and also run

select * from db.table where pformatid in (pformatids found in the first query)

"select *" means select all columns, correct? What does "where type id in" mean as far as criteria? I am not sure what the two statements above are supposed to return.

 

[Turkbear]

Hi,
Please post the original SQL query..

It appears that what is wanted, ultimately, is a set of records that have both a particular typeid AND a particular pformatid -
if that is correct your original SQL query can probably be modified to obtain those without muliple queries.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

Turkbear

Here is the original query:


SELECT TYPEID, PFORMATID, ITEMCLASSID, PFBOXNUM, RTOP, RBOTTOM, RLEFT, RRIGHT

FROM db.TYPEPF

WHERE (RTOP > 1000)



select * from db.itemtype where typeid in (typeids from the previous query)

select * from db.pformat where pformatid in (pformatids found in the first query)

 

[lbass]

Try something like this:

SELECT *
FROM ((db.TYPEPF
inner join db.itemtype on
db.TYPEPF.TYPEID=db.itemtype.typeid
)
inner join db.pformat on
db.typepf.pformatid=db.pformat.pformatid
)
WHERE (RTOP > 1000)

-LB

 

[kc27]

That got it. Thanks for the help everyone.

I forgot to ask, what about the "Crystal SQL Designer"? The Help file mentions it, but it seems the "Add A Command" was the only method that was available to me. Is the "Crystal SQL Designer" something that has to be installed separately?

 

[lbass]

I think that was only available through version 8.5.

-LB

 

[crystalkiwibruce]

The Crystal SQL Designer and Crystal Dictionary utilites were last used in Crystal 8.5. They were replaced by SQL Commands in Crystal Reports 9 and Business Views in Crystal Reports 10.

Those new tools are far more useful than the older utilities.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[Turkbear]

Hi,
Just a small note: The 'Add Command' option was not replaced by Business Views - BVs are part of Crystal/Business Objects Enterprise not Crystal Reports itself.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

Thanks for the clarification - the Crystal Reports 9 Help File on my PC does not refer to "Add A Command", only Crystal SQL Designer. This was even after a reinstall of Crystal Reports 9. As far as I know, I never had anthing earlier than Crystal Reports 9 on this PC, so I'm not sure why the help file was out of date.