I have several reports I need to create in Crystal Reports XI, and I'm not sure of the best methodology. I have an ASA (Adaptive Server Anywhere) 9.02 database that I am connecting to through ODBC. The main part of the query is fairly straightforward, but the WHERE clause, I believe, forces me to use a command object. My query, cut down for sanity's sake, is as follows:
( bridge.brkey = :as_brkey ) and
(( ( roadway.brkey = bridge.brkey ) and
( inspevnt.brkey = bridge.brkey ) and
( inspevnt.inspkey = (select max(i.inspkey) from inspevnt i where
i.brkey = bridge.brkey and i.inspdate = (select max(j.inspdate) from
inspevnt j where j.brkey = bridge.brkey))) and
( roadway.on_under = (select min(r.on_under) from roadway r where
roadway.brkey = r.brkey )) ))
That's the query as it is used on an old Sybase DW.NET report, which I have to make work in Crystal. I am able to use the command object and make it work (is this the best way?), but that makes it so I can't use SQL Expressions in the report, which I think I need to solve my other problem as described below.
The field "bridge.fips_state" is actually a code such as "06" or "12" that refers to data in an as yet unreferenced table names PARAMTRS. The PARAMTRS table replaces all of the possible individual lookup tables, and has columns that contain a field for the table name, the field name and the parameter value. For example, if I have the value "06" in my current record, I need to query from the PARAMTRS table to find out what "06" means for the "bridge" table and the "fips_state" column. The SQL code for that is as follows:
SELECT paramtrs.shortdesc FROM paramtrs
WHERE paramtrs.table_name='bridge' and paramtrs.field_name='fips_state' and paramvalue='06'
This returns the value in the shortdesc field, "06 California" which is what the users want on their report instead of the value "06" from the bridge table. If my WHERE statement in the first query forces the use of a command, and negates my abilty to use SQL Expressions, how can I extract values from my PARAMTRS table, which I need to do for several fields in the main query.
Does anyone have any advice on this?