Command Object Paramenter modification Question

[plotner]

I am trying to modify a select statement using a command object (the SQL statement), and a parameter to append to the table names.

All of the tables can be modified by a suffix to alter the select statement appropriately for different years data. I can set parameters for each of the tables (tedious), but am trying to set a way to use one parameter as a suffix to all the table names of interest.

The basic layout of the select is as follows :

Select a.field, b.field2, c.field
from table1 a, table2 b, table3 c
where a.field = b.field and b.field = c.field

I'd like to append a string suffix to table1 and table2 names such as
table1_p, table2_p

Any insight would be greatly appreciated

 

[synapsevampire]

I think that you'll need a Stored Procedure and pass the table suffix as a parameter.

-k

 

[IdoMillet]

There's a Crystal taech support document describing how to do this.

Assuming your parameter provides the Table Name, the example they provide is:
---------------------------------------------
SELECT `{?TableParam}`.`field_one`, `{?TableParam}`.`field_two`, `{?TableParam}`.`field_three`
FROM `{?TableParam}` `{?TableParam}`
---------------------------------------------

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kenhamady]

I was able to embed the parameter into the SQL as part of the the SQL statement as follows:

SELECT `Custo{?test}`.`Customer ID`, `Custo{?test}`.`Customer Name`, `Custo{?test}`.`Region`, `Orders`.`Order ID`, `Orders`.`Order Amount`, `Orders`.`Order Date`

FROM `Custo{?test}` `Customer` INNER JOIN `Orders` `Orders` ON `Custo{?test}`.`Customer ID`=`Orders`.`Customer ID`
WHERE `Orders`.`Order ID`<3000
ORDER BY `Custo{?test}`.`Customer Name`

I put the value 'mer' into the parameter and it was inserted into each table name. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[plotner]

Embedding it worked great (Oracle, in CR9):

Thanks Ken. This is exaclty what I was trying to do, but I was trying to concatanate the values. Works like a charm and saves me tons of work!

SELECT `Custo{?test}`.`Customer ID`, `Custo{?test}`.`Customer Name`, `Custo{?test}`.`Region`,
`Orders`.`Order ID`, `Orders`.`Order Amount`, `Orders`.`Order Date`

FROM `Custo{?test}` `Customer` INNER JOIN `Orders` `Orders` ON `Custo{?test}`.`Customer
ID`=`Orders`.`Customer ID`
WHERE `Orders`.`Order ID`<3000
ORDER BY `Custo{?test}`.`Customer Name`