Using Sub-Queries in CR

[lynnd]

Hello,
I am trying to create a report using a correlated sub-query. I'm connecting to an Oracle db via ODBC, and need the option to 'perform grouping on server'. Has anyone used a sub-query and gotten it to work? I really need a method of how to set it up--I tried modifying the SQL directly, but kept locking up Crystal. Also, do I need to have the table from the sub-query included in the tables for the main query?
(I do have a rather large query working now--no sub-query--so if you want to see the SQL just ask!)

I've been trying to find information on this by using the Keyword Search for this site for past 5 or 6 days, but it has been down... so if this is a question that has already been asked and answered, I apologize.

BTW, does anyone know the pros/cons of using ODBC connection versus native connection???

Thanks,
Lynn

 

[lyanch]

I have only successfully used subqueries in the "Where" part of the statement by adding them manually (not through the select formula). You don't state what version you are using, but I believe this issue is resolved in 9 (i am still using 8.5)

Lisa

 

[lynnd]

I'm also still using 8.5...
I may have had it working earlier without realizing it--the query took so long to execute that I thought I was locking up Crystal. When I ran the same query in SQL Plus, it took over 10 min. I know I didn't wait that long!!!
I'm now working with our DBA to optimize the query. Keep your fingers crossed for me!

Thanks for the feedback,
Lynn

 

[Naith]

Crystal isn't compatible with subqueries as you would use them in your Oracle database. But you can use a subreport within your main report, which exactly mimics how a subquery works.

Naith

 

[lynnd]

I did consider a sub-report, but because time to execute/performance is a big factor, I'm trying to stay away from them.

Do you have any experience with stored procedures from Oracle? We've not used them here before, and I'm trying to do some experimenting with them right now, but keep hitting small snags...

Thanks,
Lynn

 

[Naith]

You'll hit a lot more if you don't read this first.

SCR_Oracle_Stored_Procedures.pdf from

http://support.crystaldecisions.com/docs

Crystal is pretty picky about the type of stored procedure it wants from Oracle. Once you get it rolling, however, if performance is your main concern, there's no quicker solution.

Naith

 

[lynnd]

I have accessed the above document and it has been a big help, but the snag I'm working on right now is creating a temporary table. When I try to add the procedure to a report, I get a very non-descriptive "Not Supported" error. Here's my procedure...(keep in mind that more SQL will exist after the temp table has been created):

CREATE OR REPLACE PROCEDURE Summary_Procedure_COMPLEX (
  Summary_Cursor IN OUT Summary_Package_COMPLEX.Summary_Type,
  P_LT_ID IN listdev.attributes.aa_id_fk%TYPE)
AS
  V_TEMP VARCHAR2(30) := 'LYNN_TEMP';
  V_JOB_TABLE VARCHAR2(100) := 'LYNN.TEST_JOB';
  BEGIN
	EXECUTE IMMEDIATE ('create global temporary table '||V_TEMP||'
	 on commit preserve rows as
	 SELECT DISTINCT aa_id_fk, atc_attype_fk, atc_code_fk, count(*) how_many
	 FROM listdev.attributes
	 WHERE aa_id_fk = '||P_LT_ID||' AND
	       le_pin_fk in (select ti_pin from '||V_JOB_TABLE||' WHERE EXCL_DEDUP IS NULL AND EXCL_REDUCE IS NULL)
	 GROUP BY aa_id_fk, atc_attype_fk, atc_code_fk');
    OPEN Summary_Cursor FOR 
	'SELECT * FROM '||V_TEMP;
  END Summary_Procedure_COMPLEX;
/

Any ideas?

Thanks,
Lynn

 

[lyanch]

I had problems with creating global temp tables within my SP. I created them separately and then inserted data into them. EXECUTE IMMEDIATE is essentially a call to another stored procedure, which crystal doesn't support with Oracle.

Lisa

sample script for permanent Global temp table (now isn't that an oxymoron)

/*===================================================*/
/* Table: GTT_Example */
/*===================================================*/


create global temporary table GTT_Example(
ChangeDate Date,
StringVar Number(16)
)
on commit delete rows

 

[lynnd]

So is this 'permanent Global temp table' persistant beyond the session? (i.e. Do you only create it once outside of your stored procedure, then insert data into it inside of your sp, and then only the rows are deleted at the end of the session?)
My concern is that there will be many different users potentially requesting this report at the same time. Does a temp table need to be created inside each of their schemas... Well, this may be an entirely different issue. This is the first time I've worked with stored procedures, and I haven't read the section on privileges and invoker's vs. definer's rights.
Thanks for you help!
Lynn

 

[lyanch]

Yes it is persistant beyond the session. Oracle does understand that it is used a temp table.. so even if two or more users are using the same table, they each get their own copy.

Lisa

 

[lynnd]

Hi!
Just wanted to update you on one note you made earlier: "EXECUTE IMMEDIATE is essentially a call to another stored procedure, which crystal doesn't support with Oracle."
Because I needed to alter my table, I found I couldn't use temp tables anyway. But I did find that the 'Execute Immediate' worked ok. Here's the code I used (minus extra wordage) that worked for me:

CREATE OR REPLACE PROCEDURE SP_JOB_ATTRIBUTE_SUMMARY (
 SUMMARY_CURSOR IN OUT JOB_ATTRIBUTE_SUMMARY_PACKAGE.SUMMARY_TYPE_CUR,
 P_LT_ID IN LISTDEV.ATTRIBUTES.AA_ID_FK%TYPE)
AUTHID CURRENT_USER AS
 V_TEMP VARCHAR2(30) := 'JOB_ATTRIBUTE_SUMMARY_TEMP';
 V_JOB_TABLE VARCHAR2(100) := 'JOB_TABLE';
BEGIN
 BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE '||V_TEMP;
 EXCEPTION WHEN OTHERS THEN
  NULL;
 END;
 EXECUTE IMMEDIATE ('CREATE TABLE '||V_TEMP||' AS
  SELECT DISTINCT AA_ID_FK, ATC_ATTYPE_FK, ATC_CODE_FK, COUNT(*) HOW_MANY
  FROM ATTRIBUTES
  WHERE AA_ID_FK = '||P_LT_ID||' 
  AND LE_PIN_FK IN 
  (SELECT TI_PIN FROM '||V_JOB_TABLE||' 
   WHERE EXCL_DEDUP IS NULL 
   AND EXCL_REDUCE IS NULL)
  GROUP BY AA_ID_FK, ATC_ATTYPE_FK, ATC_CODE_FK');
 EXECUTE IMMEDIATE 'ALTER TABLE '||V_TEMP||' ADD 
  (LT VARCHAR2(60), AT VARCHAR2(80), AC VARCHAR2(80), JOB VARCHAR2(50))';
 EXECUTE IMMEDIATE 'UPDATE '||V_TEMP||' SET ...';
 OPEN SUMMARY_CURSOR FOR 
  'SELECT * FROM '||V_TEMP||'
  WHERE ATC_ATTYPE_FK IN 
   (SELECT AT_ATTYPE_FK FROM AV_ATTRIBUTES 
    WHERE STATUS IS NULL)';
 BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE '||V_TEMP;
 EXCEPTION WHEN OTHERS THEN
  NULL;
 END;
END SP_JOB_ATTRIBUTE_SUMMARY;
/

Thanks for all your help!
Lynn

 

[SporeSpursFan]

LYNND,

I tried following the above codes but i got this error message:

[oracle][odbc][ora][ora-02041] client database did not begin with a transaction.

any advice?

 

[SporeSpursFan]

Hi all,

I realised that I will get the above error when I am using dblinks.
eg:

EXECUTE IMMEDIATE ('CREATE TABLE '||V_TEMP||' AS
SELECT * from ATTRIBUTES@ABC');

Anyone know how i can continue to use dblinks but avoid such an error.

TIA.