Dynamic field in sql

[dpinfo]

Hello,

I need to built a query where the field shown is composed with a parameter.

In oracle I do this with this sintax

 

select field&Num from table

and it works.

 

If I run the query Oracle ask me a value for the parameter Num.

 

In Jasper I have tried with :

 

select field$P{NUM} from table

 

but the error was : "SQL Problems : Ora-00923 : key word FROM don't found"

 

Please help me.

Bye

Dpinfo

[scottward]

Try using field$P!{NUM}.

 

Not sure of the details, but the ! causes the value to be inserted as a "literal" or something like that. As far as I can figure:

If NUM = 2

$P{NUM} will enter '2' into the SQL statement

$P!{NUM} will enter 2 into the SQL statement

 

Let us know if this works.

 

Scott

[dpinfo]

Scott, you're fantastic !!!

 

It works with the sintax :

 

select field$P!{NUM}AS alias from table

 

If you choose that the parameter NUM is for prompting you can change the default value of the parameter and so the field will change.

For example if the default value of NUM is "2" and in the prompt of NUM you put "3" your query will be :

 

select field3 as alias from table

 

P.S. It's very important that the field has a fixed name because you can't change dinamically the value of the field in the band. So you have to put an alias in order to fix the name of the field. If you don't put an alias the report will not work.

 

Thank you a lot for help.

 

Bye

Dpinfo

[sridi]

plz  can u help me do do that

i need to have my result in fildes

but i dont know how many filds so they are dynamic

{col1}  as col1,$P!{col2} as col2
FROM
     "AML".$P!{table1}

help plz

send me the anser in mansour.sridi@gmail.com plz

[sridi]

select
$P!{col}
FROM
     "AML".$P!{table1}

sorry the correct answer is 

but i had a problem print my fields i dont know how i can i  have it  to put them in my report

help plz

[nimal]

 I have a cast exception I can resolve with this solution !

It's waiting for BigDecimal. How can I cast to a "string" ?