Table Linking

[ekta22]

I have this report that uses 4 tables linked to each other. When I go to the 'links' tab, I see the relationship but is there a way to look at the sql statement behind the joins?

Thanks

 

[Turkbear]

Hi,
Under the Database menu is "Show Sql Query" - that will show what is going to be sent to the datasource..






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

 

[ekta22]

I don't want to see the parameter info. I want to see the SQL side of the table joins that I created in my report.

 

[Turkbear]

Hi,
Did you try what I posted?
It will show you the Sql code which includes the join information..

I said nothing about parameters..

For instance:

SELECT "HRDATA".EMPL_NM", "EMAIL_ADDRESSES"."EMAIL_ADDR"
 FROM   "WAREHOUSE"."HRDATA" " INNER JOIN "WAREHOUSE"."EMAIL_ADDRESSES" "EMAIL_ADDRESSES" ON "HRDATA"."EMPL_NBR"="EMAIL_ADDRESSES"."EMPL_NBR"

2 tables. showing Join




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

 

[ekta22]

ooops.. sry.. my bad.. i see it

thanks

 

[Turkbear]

Hi,
No problem..with complex joins especially, it can be hard to see the parts that make up the join..





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

 

[ekta22]

hi, can I add on another question to this. I wanted to remove all join stuff from crystal to my database. So I created a stored procedure and did the joins on the database side. Now I want to update the report to use that single stored procedure instead of 4 tables. I went to Database->Set Database Location.
Do I have to update every single table one by one? Coz it does not let me select all 4 tables and then update. Is doing it 4 times okay?
Other thing that I noticed is that when you update it does not update the name. I still see the table name instead of the stored procedure name in the report. That can be confusing although if I look at the property it will say the right procedure name.

 

[Turkbear]

Hi,
Yes each must be updated and using the same new source for each one is no problem.

It retains the same name by default ( in case formulas, etc. refer to it) but, I believe, it can be changed after updating..Don't know the method to do so, however..





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

 

[ekta22]

I did that. But I am noticing that after updating it to the stored procedure, the report is taking forever to run. While if I just run the same stored procedure in query analyzer it gets done in 10mins. Any idea why this may be happening?

 

[Turkbear]

Hi,
Not sure..Try copying the code generated by Crystal ( the Show Sql Query output) and using it in Query Analyzer.

Are any parameters passed by Crystal that you do not pass when you run it in the QA?





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

 

[ekta22]

Ok, will try that.

No, I am passing the exact same parameters in Crystal and QA.

 

[ekta22]

Just to give you an update. Crystal was still taking forever to run so I called crystal support. They told me that you can only update one object with one single subject. You cannot update 4 objects(tables in my case) and replace it with a single object. Crystal does not let you that. He said the only way would be to design the report from scratch if I want to use the stored procedure..:-(

Thanks for all your help.

 

[Turkbear]

Hi,
Not to disagree with them ( they are the experts) , but I think I have done just that in the past..Let me check and I will post the outcome..
( It may have been replacing multiple tables with one table, not an SP)



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