modify single-select query input control

[serfer2]

I need to be able to modify single-select query input control.

I explain myself:

I need to add to the list of values that gives back the SQL query a static "added by hand" value.

 

In example:

If the SQL query returns these values['1', '2', '3', ...], I need to be able to add at the beginning of those values the value '0' or any other arbitrary value, that I want.

 

Then, when the user executes the report, in the list of possible values that will be able to select, they will also appear the values that I have added in addition of whom the SQL query returns.

 

Is possible to do that?

 

where do i must to do the changes, to be able to do that?

 

Some one can help me, or tell me where to start probing?

 

 

Thanks a lot ;)

[kiyone]

serfer2 wrote:

I need to be able to modify single-select query input control. I explain myself: I need to add to the list of values that gives back the SQL query a static "added by hand" value. In example: If the SQL query returns these values['1', '2', '3', ...], I need to be able to add at the beginning of those values the value '0' or any other arbitrary value, that I want.

 

You can simply add the additional parameters to the sql query:

 

select id from mytable union select 0

 

adds a 0 to the result

[serfer2]

Ok, thanks. At the moment it can be a valid solution, but anyway I need to be able to make this modification i fact, I´ll need to be able to modify the JSP input parameters page, for some others questions.

 

Thanks a lot for your fast reply ;)

[sergioOrtiz]

Hi,

 

I tried to follow your approach to populate a CUSTOMER_ID,CUSTOMER_NAME combo.

 

I used this SQL query...

 

select ID,NAME from CUSTOMER

union

select 0,'All customers' from CUSTOMER

 

...but I got a SQLException :: (ORA-12704: character set mismatch, sqlcode=12704, sqlstate=72000)

 

I use an OracleXE database, ID field is a NUMBER and NAME is NVARCHAR2.

 

I know that database casting functions may solve the problem but I wonder whether there is any db-independant solution.

 

Thanks in advance

[csbac]

Hi!

Probably some cast( as ) operator should be the solution, yes, but ...

 

shouldn't it be

Code:

...
UNION
select 0,'All customers' from DUAL

 

Otherwise, you get 0, 'All customers' as often as you have entries in Customer ....

 

Yours,

Sebastian

[sergioOrtiz]

Hi Sebastian,

 

You're right.

Unfortunately, it seems that casting will be required to overcome the problem and I fear this may pose platform-dependance problems if migrating to other databases in the future.

 

Thanks

[csbac]

Mh ... use a view that contains the cast and the additional row.

"from dual" is oracle-specific anyway, so you can keep the db-specific code inside the DB, and just create another view for a different rdbms.

Sebastian