b]bold[/b]SQL Subqueries

[cantademorna]

In the interest of not wanting to spend countless hours
on this, is there a way of effecting SQL subqueries?
The below doesn't work I know, but is there a way round this?
Unfortunately CR10 is really vague on this crucial kind of thing.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

(
if {?report_type} = 'Closed' then
{JOBS.JOB_NUMBER} IN(SELECT j2.job_number
FROM test.jobs
j2,test.job_status_history js2
WHERE j2.completion_date IS NOT NULL)
else
True
)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thank You

 

[synapsevampire]

Crystal doesn't have sql subqueries. You can paste in a real SQL statement in an Add Command (listed under your data source), providing you have a SQL type of database. Perhaps this will resolve.

You need to post technical information, and I suggest that you seek architectural help, rather than trying to specify it at this stage.

For instance, why are you using a subquery, what type of database do you have, where are you attempting to use this bizarre bastardization of query and code?

Successful posts tend to include:

Crystal version
Database/connectivity used
Example data
Expected report output

-k

 

[cantademorna]

The version of Crystal I have already supplied which is CR10, the connectivity is via an ODBC connection and example data and output is not relevant here. The "bizarre" query/code you refer to is quite common in dealing with an Oracle data base if you weren't aware of that and indeed I am attempting a conversion of that query (part of a more complex sub query procedure by the way) so need to see how Crystal handles this kind of thing.
But thanks for your initial tip, I'll look into it although I relly need an advanced manual for Crystal if truth be told.

 

[Turkbear]

Hi,
Using subqueries, while certainly supported by Oracle, is not the optimal way ( IMHO ) to access data..create Views or Stored Procedures instead..

The use of a Command object is the 'standard' way of using database features not implemented in a reporting tool like Crystal ( Oracle's various SET operations ( Intersect, Minus, etc.) for example, or its hierarchical query ability which is more flexible than Crystal's)..

This book may be helpful for advanced techniques ( it has examples for Oracle and SqlServer, I believe):

http://www.amazon.com/gp/product/07...104-1029157-5737518?s=books&v=glance&n=283155

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

 

[synapsevampire]

I code PL/SQL often, and you might consider using a Stored Procedure.

I've never seen that syntax in Oracle, I've only worked through 10G, must be a new version...

As many PL/SQL coders will tell you, subqueries aren't necessarily the most effective way to query, you may find that inline queires/derived tables will produce a faster query, taking a look at the explain plan will help.

But still, you've not supplied anything about the data nor what you intend to do with it, so it's difficult to assist you, and you're obviously reluctant to ask for assistance.

I suggest that you supply technical information rather than a Crystal manual. aside from the fact that BO will try to rope you into using their methods (usually involving Business Views/subreports and other proprietary techniques), which aren't necessarily what a seasoned Business Intelligence person would go with.

Again, rather than attempting to hack your way through it, leverage this resource, tek-tips is the definitive resource for Crystal on the web, post example data and the output requirements.

I'm sure that you're a competent coder, but as with most coders, providing documentation/technical information is often not a favorite thing to do, too many prefer to go try to figure it out them$elve$.

-k

 

[cantademorna]

I agree vampire, I'm not interested in the least in hacking through anything for the sake of it and indeed the code you saw was something I cooked up to highlight the sub query I was trying to achieve, it's not CR12 by the way!
In fact here's the original code, or part of a wider piece of code.
So there are 2 tables : test.jobs and test.job_status_history where j. and j2. is test.jobs

AND j.job_number IN(SELECT j2.job_number
FROM test.jobs j2,test.job_status_history js2
WHERE j2.completion_date IS NOT NULL
AND js2.status_date = (SELECT MAX(status_date)
FROM test.job_status_history
WHERE job_number = j2.job_number
AND ((DECODE(status_code,''6HPZ'',''AAA#'',status_code) like ''%#'')
OR (status_code = ''SUBZ''))
)

Oh and this sub query isn't of my doing and neither would it be, but I want to translate this effectively in CR10.
A derived table isn't on, but this isn't the sql environment or a past Oracle tool where the above was set up in. I've looked into sub-reports and the like and know that they aren't the way to go.

A.

 

[synapsevampire]

You can paste SQL into the Add Command, so whatever works in Oracle will work in CR 10.

When you paste in the SQL, you can create parameters and include those as part of the sql as well, so hopefully that was all that you needed to know.

-k

 

[Turkbear]

Hi,
It seems like a command Object can reproduce the logic needed to get a job_number you can then use as needed:
Something like:

Select job_number
FROM test.jobs j2,test.job_status_history js2
       WHERE j2.completion_date IS NOT NULL
       AND js2.status_date = (SELECT MAX(status_date)
                      FROM test.job_status_history
                      WHERE job_number = j2.job_number
                      AND ((DECODE(status_code,''6HPZ'',''AAA#'',status_code) like ''%#'')
                      OR (status_code = ''SUBZ''))
                      )

Joining ( = join) this to the other tables in the report will effectively use it like a WHERE clause




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

 

[cantademorna]

Vampire, where do you place the code in the add command? in the Create New Connection?, Repository?, is it within a text file with the sql code that you add?

 

[synapsevampire]

Under the datasource you'll see Add Command.

Once opened, paste in your SQL.

-k

 

[cantademorna]

Thanks Turkbear, the Oracle code and concepts are clear as crystal (pun) to me, but CR10 command objects aren't and I'll have to see what one is first. CR10 so far has been good, but this is tricky stuff if you don't know the guts of CR10, but I'll check out what a command object is and see if that works.

 

[cantademorna]

Vampire you've lost me I'll tell you what I do.
go into Database Expert
go to Data tab
then it says Current Connections, Favourites,History etc
no Add command or anywhere I can past code.
I've Added selected tables, but I can only Add tables here,
can't I?

 

[cantademorna]

I'm going to attempt to create views and circumvent CR10 vagueries, maybe that's the simplest way to get this going.

 

[synapsevampire]

If you're already connected to your database, then it's in the list, right?

And the first thing under your currently connected datasource should be Add Command.

-k

 

[cantademorna]

The add command is there to add new data sources.
There is no Add command key, however there is a Create New Connection option. There are single and double arrows which are greyed out which actually do the adding, but only if you select a valid datasource or table.

 

[synapsevampire]

...

I've no idea what you're saying: "The add command is there to add new data sources.
There is no Add command key, however there is a Create New Connection option."

I don't know what an add command key is, and yes, you can create new connections.

Crystal requires that you FIRST connecto to a datasource, then you can either create a Command object (sql), or you can select tables, Views or SPs.

Then you start writing the report.

Is there or is there not an Add Command under your current Oracle connection, or haven't you connected yet?

If so, double click it and enter your SQL. If not, then how were you connecting to an Oracle database?

btw, you should see Oracel Server listed, adn this is how you should connect (it's a native connection provided by Crystal).

-k

 

[cantademorna]

Yep, I've seen it, I'll try that and the creation of views also.
Much thanks.

A.