SQL Expressions, commands and more

[Former Member]

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.

[Former Member · Accepted solution]

Scott,

A SQL Command is probably the best way to go. While it's true that you can't use SQL Expressions in conjunction with Commands, you don't need to.

Just add whatever you would add whatever you would put in the Expression, to the SQL Command.

For example, just add the PARAMTRS table to the existing SQL script in the FROM clause, link it in your WHERE clause and then add whatever fields you need from that table under the SELECT statement.

Use the SQL Command...

Jason