Skip to Content
0
Former Member
Aug 04, 2008 at 07:49 PM

SQL Expressions, commands and more

18 Views

Hello:

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:

SELECT bridge.brkey,

bridge.bridge_id,

bridge.struct_num,

inspevnt.suff_rate,

inspevnt.nbi_rating,

bridge.fips_state,

bridge.facility,

bridge.location,

roadway.on_under

FROM bridge,

roadway,

inspevnt

WHERE

( 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?

Thanks.