create value set using unique recs from table

[rmwcalum98]

I am trying to create a value set in Oracle11i.

I am creating an LOV for the value set from a table. For example, the LOV will be populated by:
select batch_name from pa_transaction_interface_all;

When I go to concurrent programs and try to select a value for the parameter, I get every record's batch_name instead of just the unique batch_names. How can I set up the LOV to only pull each unique batch_name?

Thanks.

 

[BJCooperIT]

Try this:

select DISTINCT batch_name from pa_transaction_interface_all;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[rmwcalum98]

this is within the application, where I am trying to setup the value set; it does not allow me to specify code; I simply pick the table and the column. I can't write any code except for the where or order_by statement.

 

[SantaMufasa]

Then can you not create a VIEW that uses Barb's excellent suggestion, which your application can access?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

If you are talking about Oracle Forms LOVs, then you should be able to create a new record group using the aforementioned SQL. You then use the new group as the basis for the LOV.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[rmwcalum98]

thanks, I will create a view.

 

[rmwcalum98]

turns out that I can only select tables and not views if I choose the table validation type. the list of validation types are independent, dependent, special, table, none, pair, translatable independent, and translatable dependent. I did try special and chose query as the event and typed in the same sql I used to create the view, but that didn't work either. when I went to the conc. program to run it, it didn't give me an LOV. I also tried using ListVal as the event, but then I got an error when I tried to choose a value for that parameter. Any other suggestions?

 

[BJCooperIT]

Sorry, I am unfamiliar with the "application" you are using to do this. Some of your terminology is new to me. Anyone else care to jump in here?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[Turkbear]

Hi,
Please provide more details:
Application name and source
Operating System
Language used to code the app..

This does not appear to be an Oracle App, so we need to know what you are using to create LOVs and how they are implemented in whatever app you are using..

..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rmwcalum98]

The application is Oracle 11i. I am actually creating a value set to be used to populate a parameter in a concurrent program. When you log into the app, the path is: application developer (provided you have been assigned that responsibility by the sysadmin) -> application -> validation -> set.

 

[Turkbear]

Hi,
Oracle 11 covers a wide range of product designations..
Are you using the E-Business suite? What variant..What specific application?

The Current Oracle Application Server and Form/Report designer is 10g..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rmwcalum98]

Yes, it is the e-business suite. I pulled the following out of the About screen:
RDBMS : 9.2.0.5.0
Oracle Applications : 11.5.9

I can't really get more specific about the app. All I know is when I log in, I get a list of responsibilities. I am choosing the application developer one (the same one I would if I were registering a program or an executable). Regards....