creating dynamic LOV from a table containing multiple columns

Tayyeb

I have a table (A) with multiple columns and want to create a LOV to be used for other table (B).

ColumnS in Tabel (A=CURRICULUM) are: CURRICULUM_ID, SUBJECT_ID, GRADE_ID, DOMAIN_ID, CATEGORY_ID, STANDARD_CODE, STANADARD_STATEMENT, LEARNING_TARGETS.

My query is as below:

SELECT CURRICULUM_ID CI, STANDARD_CODE SC FROM CURRICULUM

WHERE

SUBJECT=:P48_SUBJECT_ID

AND GRADE_ID=:P48_GRADE_ID

AND DOMAIN_ID=:P48_DOMAIN_ID

AND CATEGORY_ID=:P48_CATEGORY_ID

Its working fine if I use one parameter. but If I use more than one parameter its not working for me. Anyone please elaborate and thanks in advance.

dvohra21

Is the SQL query generated by the GUI editor. If not, best to generate with the Query editor as additional features are available such as :

When you want the value to be supplied to a SQL WHERE clause using a bind variable in the clause, select the Required checkbox. This ensures that a runtime exception will be thrown if the value is not supplied. 

To add a named bind variable to a view object, use the Query page of the overview editor for the view object. You can define as many bind variables as you need.

To define a named bind variable:

In the Application Navigator, double-click the view object.

In the overview editor, click the Query navigation tab.

In the Query page, expand the Bind Variables section and click the Create new bind variable button.

In the Bind Variable dialog, enter the name and data type for the new bind variable.

Because the bind variables share the same namespace as view object attributes, specify names that don't conflict with existing view object attribute names. As with view objects attributes, by convention bind variable names are created with an initial capital letter, but you can rename it as desired.

https://docs.oracle.com/cd/E14571_01/web.1111/b31974/bcquerying.htm#ADFFD297

dvohra21

Is the SQL query generated by the GUI editor. If not, best to generate with the Query editor as additional features are available such as :

When you want the value to be supplied to a SQL WHERE clause using a bind variable in the clause, select the Required checkbox. This ensures that a runtime exception will be thrown if the value is not supplied. 

To add a named bind variable to a view object, use the Query page of the overview editor for the view object. You can define as many bind variables as you need.

To define a named bind variable:

In the Application Navigator, double-click the view object.

In the overview editor, click the Query navigation tab.

In the Query page, expand the Bind Variables section and click the Create new bind variable button.

In the Bind Variable dialog, enter the name and data type for the new bind variable.

Because the bind variables share the same namespace as view object attributes, specify names that don't conflict with existing view object attribute names. As with view objects attributes, by convention bind variable names are created with an initial capital letter, but you can rename it as desired.

https://docs.oracle.com/cd/E14571_01/web.1111/b31974/bcquerying.htm#ADFFD297

Tayyeb

Hi, Thanks for the brief response. Your response taught me a lot more than I anticipated. Now I have an issue almost similar kindly respond with details.

I have a form with more than seven items on it. they are SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE, STANADARD_STATEMENT, LEARNING_TARGETS.

I want these items (SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE) type to be select list. additionally, I want to make LOVs for each of these items.

1. LOV for SUBJECT_ID: I am making this LOV using a table(SUBJECTS) having TWO columns. MY query is (SELECT SUBJECT_ID, SUBJECT_NAME FROM SUBJECTS) It's working fine.
2. LOV for GRADE_ID: I am making this LOV using a table(GRADES) having TWO columns. MY query is (SELECT GRADE_ID, GRADE_NAME FROM GRADES) It's working fine.
3. LOV for DOMAIN_ID: I am making this LOV using a table(DOMAIN) having TRHEE columns. MY query is (SELECT DOMAIN_ID, DOMAIN_NAME FROM DOMAIN WHERE SUBJECT=:P48_SUBJECT_ID). It's working fine.
4. LOV for CATEGORY_ID: I am making this LOV using a table(CATEGORIES) having FOUR columns. MY query is (SELECT CATEGORY_ID, CATEGORY_NAME FROM CATEGORIES WHERE DOMAIN=:P4.8_DOMAIN_ID) It's working fine.
5. LOV for STANDARD_CODE: I am making this LOV using a table(CURRICULUM) having MORE THAN EIGHT columns. MY query is (SELECT CURRICULUM_ID CI, STANDARD_CODE SC FROM CURRICULUM WHERE SUBJECT=:P48_SUBJECT_ID AND GRADE_ID=:P48_GRADE_ID AND DOMAIN_ID=:P48_DOMAIN_ID AND CATEGORY_ID=:P48_CATEGORY_ID). It's not working for me.

Kindly tell me how I can correct the 5th LOV. Thanks

dvohra21

Does the GUI Query Editor not have a provision for multiple bind variables? If not, likely multiple bind variables are not supported. But most like are supported and the Query Editor could add them.

Tayyeb

Issue resolved, Thanks for the response

dvohra21

thanks for update