SQL within Record Selection Formula.

[cantademorna]

Can I build up a SQL where statemant? i.e.
can I do this :

the_where_action := "{JOBS.CONTRACT_NO} = {?contract_no}"
+ " and {JOB_TYPES.ORDER_TYPE} = 'R'"
+ " and {ADMIN.PRIORITY} <> '4'";

and then excecute the_where_action for the select query?
Is there another way I can add to or take away from the
where clause of the query within the formula environment at excecution time?

Thank you.

 

[synapsevampire]

Crystal passes the where clause by using the Report->Selection Formulas->Record.

{JOBS.CONTRACT_NO} = {?contract_no}
and {JOB_TYPES.ORDER_TYPE} = 'R'
and {ADMIN.PRIORITY} <> '4'

It does not execute dynamic SQL.

Depending upon your version of Crystal, you can also paste in a query, and apply parameters in a Command object.

Remember to take the time to post technical information:

crystal version
database/connectivity used

-k

 

[Turkbear]

Hi,
Use user entered/selected parameters for the values and have 'ALL' as a default value..

Then your record selection formula could be something like

(
if {?contract_no} = 'ALL' then
True 
else
{JOBS.CONTRACT_NO} = {?contract_no}
)

and

(
If {?OrdType} = 'ALL'
then
True 
else
{JOB_TYPES.ORDER_TYPE} = {?OrdType}
)

and

(
If {?Priority} = 'ALL' 
then
True
else 
{ADMIN.PRIORITY} = {?Priority}
)

Should give you some idea of how to make it work for your situation..






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

 

[Turkbear]

Hi,
My posting assumed you wanted to filter in the report, not when building a recordset...






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

 

[cantademorna]

Turkbear, that's exactly what I wanted, much thanks my man/gal, by the way is there a CR10/11 manual that goes into this? Most of them seem to just skim the surface.
Again thank you sir.

 

[synapsevampire]

Check my FAQ:

faq767-3825

-k

 

[Turkbear]

Hi,
No problem..Glad to help..
( Man, by the way )

Synapse's FAQ on passthroughs should be a shortcut on your Crystal development desktop - it is on mine ( altho' I sometimes fall back into bad habits)





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