Jump to content
Changes to the Jaspersoft community edition download ×

Dynamic field in sql


Recommended Posts


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.



Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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.



Link to comment
Share on other sites

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.




Link to comment
Share on other sites

  • 1 year later...

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

help plz

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

Link to comment
Share on other sites

  • 2 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...