Hi All,
I have an issue, IN BO 6.5 deski, I am joining two data provider by free hand sql.One Dp has 71 rows (DP1) and other has 3 million rows. Between these 2 data providers one field Cust ID is common.
But When I try to drag the detail object ( 2nd data provider) to report it all the hangs and BO stops works.
What can be done in this situation? May be BO does’nt supoort that much rows.
E.g . DP1 has 71 rows & 15 col
DP2 has 3 milllion rows and 2 col. ( cust_id, Bill_cycle)
In both Cust_id is common. and in DP2 Bill_cycle is detailed object of Cust_id.
Is there any other way to do it query level?
Thanks in advance,
Mindtrench
DeskI is going to struggle with 3 million rows, that’s for sure. What are you hoping to see on the final report? Is it only the 71 rows, but with the billing cycle field along side? Are both data providers from the same database? If yes to those last two questions, then a sub-query (at the SQL level) to limit the second data provider to the equivalent customers of the first data provider is the answer.
First, let me go on record as saying freehand SQL is not a recommended solution for any kind of production reporting. In testing / proof of concept it is a nice option to have, but building a proper universe should be done for production purposes. That said, having the SQL of your second data provider similar to this will help:
SELECT tbl2.CUST_ID, tbl2.BILLING_CYCLE FROM tbl2
WHERE tbl2.CUST_ID IN
(SELECT tbl1.CUST_ID FROM tbl1 WHERE {same conditions as DP1})
Dwayne, Thanks for your reply, Well yes both DP represents different database icon_cry.gif and we dont have universe here icon_mad.gif , we only have to use free hand sql. Hope the problem is clear to you.
Quite clear. You could have saved a LOT of money, and just bought a stand-alone copy of Crystal Reports. If you’re not using universes, my analogy is this … it’s like buying a fancy new car, and only using the cigarette lighter. There are other options to consider. Maybe a link at the database level that makes data from one database “appear” to be in another, but that’s usually a performance problem. Another option is to run the first query, then use an approach like this … Create an InList from an Excel spreadsheet … for the second query. Nothing is going to be pretty, given your constraints.
Thanks for your reply, the solution you suggested with execl file with Prompt, we can only use with designer not with Free Hand SQL. But here we dont have designer.
Could you please inlight me more if I m wrong, Or Is any other way by whic we can do this?
Thanks in advance.
You can include @variable() and @prompt() syntax in freehand SQL. The only limitation is that in the @prompt() syntax, you obviously can’t reference a universe object as the source for an LOV.
(BOB member since 2008-02-28)
(BOB member since 2002-09-19)
Thanks , I cant do anything because of customer interest :).