executing sql query in crystal

[2969]

hi

i have this sql query which gets executed but it's rather slow. Is there any other way i can get to generate rpt. I have used passing recordset and it wroks fine too but this time i would like ti have my rpt standalone..

thx

 

[kenhamady]

The key is how much of the selection formula is passed to the WHERE of your SQL statement. What is your record selection formula? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[2969]

here is my selection formula:
{qth.qtnum} = {@QuoteSelection} (this is a parameter i pass after getting a prompt from the user)

this is sql query

SELECT
qtt.`qtlin`,
udtl.`lndesc`, udtl.`udtqtllin`, udtl.`lninfo`,
qtk.`qtlin`,
qth.`ccode`, qth.`csnam`, qth.`plant`, qth.`qtalt`, qth.`qtnum`, qth.`sale1`,
qtl.`qtlin`,
plt.`pcode`, plt.`pdesc`,
cus.`ccode`, cus.`cname`,
udth.`qtnmb`,
srt.`scode`, srt.`sname`
FROM
((((qth LEFT JOIN cus ON qth.ccode = cus.ccode) INNER JOIN srt ON qth.sale1 = srt.scode) LEFT JOIN plt ON qth.plant = plt.pcode) INNER JOIN udth ON qth.qtnum = udth.qtnmb) INNER JOIN (((udtl INNER JOIN qtk ON udtl.qtlkey = qtk.qtlky) INNER JOIN qtl ON qtk.qtlin = qtl.qtlin) INNER JOIN qtt ON (qtl.qtlky = qtt.qtlky) AND (qtk.qtlky = qtt.qtlky)) ON qth.qtnum = left(udtl.udtqtllin,5)

 

[kenhamady]

You don't have a WHERE clause, which means that Crystal is doing all of the work. Your formula is probably part of the problem. What is the formula for {@QuoteSelection} and where is it coming from? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[2969]

(@Quoteselection) is a formula which gets the parameter (?quotenumeber). Quotenumber is entered by the user on prompt when the rpt is run.

That's why the sql statement also doesnt have a where clause b/c at this time i dont know what value to put. Is there a better solution..

thx

 

[kenhamady]

Post the actual formula. if it is just the parameter, put the parameter into the selection formula instead of the formula and see if it passes to the SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

You can use a formula and get CR to build pass through SQL based on it, but how it's constructed, and how you construct the entire record selection criteria is key to whether Crystal will be kind enough to pass it along to the database.

I just spect a fair amount of time learning the ins and outs, and it wasn't intuitive, nor were there any hard and fast rules in a FAQ to enable me to do this, though Ken has some great pointers on his site (post above this).

Post the entire formula you're creating, and the entire record selection formula.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[2969]

here is my selection formula:
{qth.qtnum} = {@QuoteSelection} (this is a parameter i pass after getting a prompt from the user)

this is sql query

SELECT
qtt.`qtlin`,
udtl.`lndesc`, udtl.`udtqtllin`, udtl.`lninfo`,
qtk.`qtlin`,
qth.`ccode`, qth.`csnam`, qth.`plant`, qth.`qtalt`, qth.`qtnum`, qth.`sale1`,
qtl.`qtlin`,
plt.`pcode`, plt.`pdesc`,
cus.`ccode`, cus.`cname`,
udth.`qtnmb`,
srt.`scode`, srt.`sname`
FROM
((((qth LEFT JOIN cus ON qth.ccode = cus.ccode) INNER JOIN srt ON qth.sale1 = srt.scode) LEFT JOIN plt ON qth.plant = plt.pcode) INNER JOIN udth ON qth.qtnum = udth.qtnmb) INNER JOIN (((udtl INNER JOIN qtk ON udtl.qtlkey = qtk.qtlky) INNER JOIN qtl ON qtk.qtlin = qtl.qtlin) INNER JOIN qtt ON (qtl.qtlky = qtt.qtlky) AND (qtk.qtlky = qtt.qtlky)) ON qth.qtnum = left(udtl.udtqtllin,5)

 

[kenhamady]

You haven't posted the formula for {@QuoteSelection}, is it coming from an application?

Either way, instead of a formula called {@QuoteSelection}, create a parameter called {?QuoteSelection}, put that in the selection formula, and pass your value to the parameter.

Or, if this is an application, pass the whole selection formula from the app to the report. The goal is to get CR to generate a WHERE clause.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com