Parameterized SQL?

[2005 IR Help]

By: Geoff Drnec - gdrnec

Parameterized SQL?

2005-01-04 12:12

Is it possible to use a parameter as a field name in my report's SQL?

 

For example: If I declared a parameter: $P{FieldName} whose defalt value is "Name" could I write my sql as:

 

SELECT $P{FieldName} FROM Accounts

 

Assuming "Name" is a field in the Accounts table?

 

If this is possible, I must be going about it incorrectly because the sql is being interpreted as:

 

SELECT 'Name' FROM Accounts

 

Those troublesome single quotes are unwanted.

 

Any ideas? Thanks in advance.

 

Geoff

 

 

 

 

By: sst_marvin - sst_marvin

RE: Parameterized SQL?

2005-01-06 21:42

I dont know if it's possible to use a parameter as a field name - i dont think it is.

 

An alternative that will work is to simply SELECT all the fields that you need in your query, then in the report layout itself, use a parameter in a field's 'print when expression' to specify which field you want to display:

 

(($P{FieldName}.equalsIgnoreCase("name"))?$F{name}:

($P{FieldName}.equalsIgnoreCase("age"))?$F{age}:

($P{FieldName}.equalsIgnoreCase("sex"))?$F{sex}: ...

 

 

 

 

By: Mike Warne - mwarne

RE: Parameterized SQL?

2005-01-10 14:48

Try adding a dummy column name to your SQL such as

as 'colname'

 

Then a new Field 'colname' will be available to your report.

Haven't tried this, I have done thins like select 'test' as 'test123'

 

That produces a column 'test123' with a value of 'test'

 

 

 

 

 

 

 

By: aither - aither

RE: Parameterized SQL?

2005-10-05 06:27

try with

SELECT $P!{FieldName} FROM Accounts