Home » Developer & Programmer » Forms » Mandatory Values/LOV Prohibited values (10g)
Mandatory Values/LOV Prohibited values [message #550238] Sat, 07 April 2012 10:33 Go to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
Hello,
I created a list of values for the purpose of showing what unit_codes are selectable for a student's course. Each selected value gets dumped into the text field on my form.

But i was wondering, is there a way to show what unit_codes(values) are mandatory? And is there a way to show which ones are prohibited depending on data inside the database?
Re: Mandatory Values/LOV Prohibited values [message #550241 is a reply to message #550238] Sat, 07 April 2012 11:28 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Mandatory unit_codes can be displayed in List of Values; you'd include that information into the SELECT column list. For example (as you didn't provide test case), if UNIT_CODES table contains UNIT_CODE, UNIT_NAME, MANDATORY columns, List of Values would look like
select unit_code, unit_name, mandatory
from unit_codes
where ...


Prohibited unit_codes should not be displayed at all - in order to do that, you'd include a condition that restricts these values into SELECT statement's WHERE clause, such as
... 
where unit_code not in (select unit_code
                        from prohibited_unit_codes 
                        where ...
                       )


The above is just an idea; actual solution probably depends on information you know (but we do not).
Re: Mandatory Values/LOV Prohibited values [message #550242 is a reply to message #550241] Sat, 07 April 2012 12:00 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
My apologies, I should have been WAAAYY more specific. There are no fields on my database to show whether unit codes are mandatory or prohibited.
Below is my units table. The unit codes from this table.....

Create table Units(
unit_code varchar2 (11) constraint pk_Units primary key,
unit_name varchar2(50) not null;


... will be selected into this table as follows...
Create table listofinserts(
insertion_id number constraint pk_listofinserts primary key,
person_id number(8) constraint fk_Persons references Persons (person_id),
unit_code varchar2(11) constraint fk_Units references Units (unit_code));


...... but in some cases a unit_code may not be selectable because the person will require a 'pass' in one or more
prerequisite units
and heres my grades table which i showed in another message some time ago
Create table Grades(
grade_code number constraint pk_Grades primary key, 
grade varchar(4) check (result in ('fail','pass')),
person_id number,
unit_code varchar2,
CONSTRAINT fk_Persons FOREIGN KEY (person_id) REFERENCES Persons (person_id),
CONSTRAINT fk_Units FOREIGN KEY (unit_code) REFERENCES Units (unit_code));


for the moment im more concerned with prohibiting specific values on my LOV from being selected... is there a way?
Re: Mandatory Values/LOV Prohibited values [message #550249 is a reply to message #550242] Sat, 07 April 2012 13:08 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
In fact, would it be easier to accomplish this with a multi record block instead?
Re: Mandatory Values/LOV Prohibited values [message #550253 is a reply to message #550249] Sat, 07 April 2012 13:26 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vinni
in some cases a unit_code may not be selectable because the person will require a 'pass' in one or more prerequisite units

This is the key. If you can write a query (condition) that will satisfy what you explained in this sentence, then you'd be able to use it just as I showed in my previous message.

Viewing information you provided so far, I can't tell what "prerequisites" must be met or how to find them. I guess that it is something like this: there are two mathematics, let's say Math_1 and Math_2. You can't take Math_2 until you PASS Math_1. But - how are we supposed to know these conditions? The above example is rather obvious to us, human. Question is: how to explain that to Oracle? Is there any table that contains such information?
Re: Mandatory Values/LOV Prohibited values [message #550254 is a reply to message #550249] Sat, 07 April 2012 13:33 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you really need the prohibited values to appear in the list?
Re: Mandatory Values/LOV Prohibited values [message #550255 is a reply to message #550254] Sat, 07 April 2012 13:37 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's what I said ...
LF
Prohibited unit_codes should not be displayed at all
Re: Mandatory Values/LOV Prohibited values [message #550256 is a reply to message #550253] Sat, 07 April 2012 13:41 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
well really and truly, prerequisite codes are in the unit_codes column. The values are just defined differently to the others and they are used in that grades table.

and yes you're correct about the maths_1 and maths_2 part.

Many thanks
Re: Mandatory Values/LOV Prohibited values [message #550257 is a reply to message #550256] Sat, 07 April 2012 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does it mean? Do you know how to do it, or ...?
Re: Mandatory Values/LOV Prohibited values [message #550258 is a reply to message #550257] Sat, 07 April 2012 14:10 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
well no not yet. im gonna try out that query on my LOV u suggested

[Updated on: Sat, 07 April 2012 14:18]

Report message to a moderator

Re: Mandatory Values/LOV Prohibited values [message #550259 is a reply to message #550258] Sat, 07 April 2012 14:20 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't need a trigger; didn't you say that you want to use a list of values? I'd suggest you to forget about Forms for a moment and switch to SQL*Plus. Create a query which will select data you are interested in (i.e. records that don't contain "prohibited unit codes"). Once you manage to do that, simply copy/paste that query into a LoV.

We could try to assist, but you should provide some sample data and explain how "prohibited" unit code differs from "accepted" one.
Re: Mandatory Values/LOV Prohibited values [message #550260 is a reply to message #550259] Sat, 07 April 2012 14:55 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
ok cheers, i'll post my attempted query a.s.a.p

it will probably require the largest "where clause" ever...
Re: Mandatory Values/LOV Prohibited values [message #550261 is a reply to message #550260] Sat, 07 April 2012 15:00 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you really think so? Then your previous WHERE clauses were really tiny, or - hopefully not! - you'll need to create a monster WHERE clause. If the latter is the case, perhaps you should consider redesigning the data model.
Re: Mandatory Values/LOV Prohibited values [message #550262 is a reply to message #550261] Sat, 07 April 2012 15:07 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
The only reason i say that is because there is another table in question... i forgot i had a table for prerequisite unit_codes which is linked with the units table.
shown below:

Create table Prerequisites(
preunit_code varchar2(11),
unit_code varchar2(11),
PRIMARY KEY (preunit_code, unit_code),
CONSTRAINT fk_Units3 FOREIGN KEY (unit_code) REFERENCES Units(unit_code),
CONSTRAINT fk_Units4 FOREIGN KEY (preunit_code) REFERENCES Units(unit_code));
Re: Mandatory Values/LOV Prohibited values [message #550263 is a reply to message #550261] Sat, 07 April 2012 15:49 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
heres some sample data in my prerequisites table

INSERT INTO Prerequisites (preunit_code, unit_code)
VALUES
('MATHS010101', 'MATHS020202');

INSERT INTO Prerequisites (preunit_code, unit_code)
VALUES
('MUSIC010101', 'MUSIC020202');


**those rnadom numbers are there to make it easier to define which unit is more advanced

now those "preunit_code"s are also "unit_codes" for the units table. and it will be those that are used in the grades table just to state what grade a person got in that unit.

some sample data for my grades table:

INSERT INTO Grades (grade_code, grade, person_id, unit_code)
VALUES
(Grades_seq.NEXTVAL, 'pass','12345678','MUSIC010101');

INSERT INTO Grades (result_id, result, person_id, module_id)
VALUES
(Grades_seq.NEXTVAL, 'fail','12345678','MATHS010101');


now as shown above the person known as '12345678' cannot select the unit 'MATHS020202' because he/she has a fail in 'MATHS010101'.

[Updated on: Sat, 07 April 2012 15:51]

Report message to a moderator

Re: Mandatory Values/LOV Prohibited values [message #550301 is a reply to message #550263] Sun, 08 April 2012 05:09 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select q.unit_code,
       u.unit_name
from prerequisites q,
     units u
where q.preunit_code in (select g.unit_code
                         from grades g,
                              persons p
                         where g.grade = 'pass'
                           and g.person_id = p.person_id
                           and p.person_id = :form_block.person_id
                        )
  and u.unit_code = q.unit_code
order by u.unit_code


The idea is: select units from the PREREQUISITES table that are passed (these units are returned by a subquery).

What do you think?
Re: Mandatory Values/LOV Prohibited values [message #550304 is a reply to message #550301] Sun, 08 April 2012 06:16 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
seeing as there's a global user, would i change :form_block.person_id to :global.person_id ??

Re: Mandatory Values/LOV Prohibited values [message #550312 is a reply to message #550304] Sun, 08 April 2012 10:02 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
yes
Re: Mandatory Values/LOV Prohibited values [message #550493 is a reply to message #550301] Mon, 09 April 2012 15:03 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
cheers cookiemonster, and Littlefoot your code works but its not selecting the data i need, as i forgot to mention two things...

1) theres a different table i want to select the modules from and thats a course table (which is a broad structure that offers many units to choose from)

2) some of my units require a pass on TWO or MORE prerequisite units
(for example a unit such as SUPERADVANCED-SCIENCE requires a pass in chemistry and physics , if one or both is a fail then this wont be displayed on the LOV)


Below is where i want to select the units from
(LINK/JUNCTION TABLE)
Create table Courses_Units(
course_code number(3),
unit_code varchar2(11),
PRIMARY KEY (course_code, unit_code),
CONSTRAINT fk_Courses FOREIGN KEY (course_code) REFERENCES Courses (course_code),
CONSTRAINT fk_Units2 FOREIGN KEY (unit_code) REFERENCES Units(unit_code));


SAMPLE DATA
INSERT INTO Courses_Units (course_code, unit_code)
VALUES
(101, 'MUSIC020202');

INSERT INTO Courses_Units (course_code, unit_code)
VALUES
(101, 'MATHS020202');


So how would i go about in making those extra additions to my query?

sorry to disrupt your easter


Re: Mandatory Values/LOV Prohibited values [message #550691 is a reply to message #550493] Wed, 11 April 2012 10:40 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
as an alternative approach, which probably isnt the best approach..... could i set up a trigger on my text field that raises an alert if the end user (global user) chooses a unit from the LOV, but doesn't have a pass in one or more prerequisites?
Re: Mandatory Values/LOV Prohibited values [message #550927 is a reply to message #550691] Fri, 13 April 2012 10:18 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
found a solution, requires a group by clause and a few unions
Re: Mandatory Values/LOV Prohibited values [message #550962 is a reply to message #550312] Fri, 13 April 2012 18:18 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
Sorry i just wanted to jump back on the topic about the LOV query...

theres a problem im having as my select statement is completely ignoring my global variable (A.K.A the person_id) and selecting incorrect data

select q.unit_code,
       u.unit_name
from prerequisites q,
     units u
where q.preunit_code in (select g.unit_code
                         from grades g,
                              persons p
                         where g.grade = 'pass'
                           and g.person_id = p.person_id
                           and p.person_id = global.person_id)--Not selecting the logged in ID
  and u.unit_code = q.unit_code
order by u.unit_code


Many thanks

[Updated on: Fri, 13 April 2012 18:19]

Report message to a moderator

Re: Mandatory Values/LOV Prohibited values [message #551005 is a reply to message #550962] Sat, 14 April 2012 11:32 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
GLOBAL must be preceded with a colon sign.
... and p.person_id = :global.person_id)
                      ^
                      |
                      HERE!
Re: Mandatory Values/LOV Prohibited values [message #551236 is a reply to message #551005] Mon, 16 April 2012 09:32 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
sorry for the late reply. i've corrected the variable syntax, however i got this error
"frm-40502 oracle unable to read list of values"
"ORA-01036 illegal variable name/number"
how do i correct this?

[Updated on: Mon, 16 April 2012 09:32]

Report message to a moderator

Re: Mandatory Values/LOV Prohibited values [message #551238 is a reply to message #551236] Mon, 16 April 2012 09:43 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use legal variable name/number.

If it is of any help: run the form. Open LoV. It will raise an error. Now go to Help menu and choose "Display error". If you know what to do based on that information, fine. If not, copy/paste it over here.
Re: Mandatory Values/LOV Prohibited values [message #551239 is a reply to message #551238] Mon, 16 April 2012 09:54 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
but i dont understand, i've been using this global variable throughout and its spelled correctly. am i suppose to declare it another place?
Re: Mandatory Values/LOV Prohibited values [message #551242 is a reply to message #551239] Mon, 16 April 2012 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Global variables don't exist in a session until a value is assigned to them.
Are you sure that value is assigned to the global before the LOV is called?
Re: Mandatory Values/LOV Prohibited values [message #551247 is a reply to message #551242] Mon, 16 April 2012 11:52 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
The first block which is a login block had this SelectInto statement as follows:
	select person_id, person_pword
	into :global.user_id, user_pword
	from Persons
	:signin.txt_username = Persons.person_id;
	and :signin.txt_pword = Persons.person_pword;

then when login is accepted, the user is taken to the course menu block. however in that block i never had a declaration for that same variable as shown below....
declare
	
	user_grade varchar(4) := 'pass';
	user_grade_code varchar2(11) := 'MATHS010101';
	alert_button number;
begin

select DISTINCT grade, unit_code
	into user_grade, user_grade_code
	from Grades
	where user_grade = grade 
	and user_grade_code = unit_code and :global.user_id = person_id;
	message('Please select your units for this course');
	Go_block('block_with_LOV');
exception 
when no data found then (meaningful message) 

should i have declared that variable AGAIN in the declaration above or not?
Re: Mandatory Values/LOV Prohibited values [message #551252 is a reply to message #551247] Mon, 16 April 2012 12:51 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is difficult to debug code that is incorrectly made up.

In your first query, WHERE keyword (and perhaps something else) is missing.

In your second query, what is
where user_grade = grade
for example? You are selecting into USER_GRADE, and use the same variable in the same SELECT's WHERE clause? Moreover, we are discussing list of values problem (at least, that's what you reported). What does this PL/SQL code have to do with it?

Maybe we'd be happier with LoV query, don't you think?

And, to answer your final question: no, you don't have to declare a global variable twice (unless you erase it).
Re: Mandatory Values/LOV Prohibited values [message #551257 is a reply to message #551252] Mon, 16 April 2012 13:05 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
i only put those coding scripts up because they are a part of the 2 blocks that comes before the block with the LoV. i thought it would be relevant.
But anyways, im still having the error:

"ORA-01036 illegal variable name/number".
the only place ive declared that global variable was on the button on the login block as shown in the previous message. is there a place where i should declare it before that, like a trigger?
Re: Mandatory Values/LOV Prohibited values [message #551260 is a reply to message #551257] Mon, 16 April 2012 13:08 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
a) Did you check Help - Display Error? What was the result?
b) Will you, finally, post that LoV code, or not?
Re: Mandatory Values/LOV Prohibited values [message #551263 is a reply to message #551260] Mon, 16 April 2012 13:26 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
a) ORA-01036: illegal variable name/number

b) My LoV code below, Similar to yours

SELECT Courses_Units.unit_code 
FROM Courses_Units, Units 
WHERE Units.unit_code NOT IN ('COMPU020202')
AND Courses_Units.unit_code = Units.unit_code
AND course_code = '7777'
UNION
SELECT Courses_Units.unt_code
FROM Courses_Units, Units, Prerequisites
WHERE Units.unit_code = 'COMPU020202'
AND Prerequisites.preunit_code in (select unit_code
                         from Grades, Persons 
                         where grade = 'pass'
                         and Grades.person_id = Persons.person_id
   			and Persons.person_id = :global.user_id
		   )
AND Prerequisites.unit_code = Units.unit_code
AND Courses_Units.unit_code = Units.units_code
AND course_code = '7777'
GROUP BY Courses_Units.unit_code
HAVING count(*) = 2

[Updated on: Mon, 16 April 2012 13:29]

Report message to a moderator

Re: Mandatory Values/LOV Prohibited values [message #551264 is a reply to message #551263] Mon, 16 April 2012 13:33 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should learn how to properly write a SQL. Google for "SQL best practices". Among other stuff, you'll find that it is a good practice to give aliases to tables, and to precede ALL column names with these aliases.

Also, it would be nice if you test query (you are about to use in Forms) in SQL*Plus and verify it compiles correctly.

./fa/10064/0/

  • Attachment: lov.png
    (Size: 7.48KB, Downloaded 423 times)
Re: Mandatory Values/LOV Prohibited values [message #551265 is a reply to message #551264] Mon, 16 April 2012 13:42 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
i edited that just now from my actual LOV , for personal reasons. that mistake was accidental. those feld names you see there are completely different to what i have written on forms. ive tested that script on SQL*PLUS replacing the global user_id with an id that's saved on the database which works.
Re: Mandatory Values/LOV Prohibited values [message #551267 is a reply to message #551265] Mon, 16 April 2012 13:56 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you are doing everything you can to make it difficult for us to help you.

But don't worry, I think I found a perfect answer for you: click here. See you tomorrow (maybe); good luck!
Re: Mandatory Values/LOV Prohibited values [message #551268 is a reply to message #551267] Mon, 16 April 2012 13:59 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
comedian
Re: Mandatory Values/LOV Prohibited values [message #551285 is a reply to message #551268] Mon, 16 April 2012 17:43 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some things you can try:
1) put a button in the block that has the LOV. In the when-button-pressed trigger put a message to display the global, check it has the correct value.
2) Create a datablock item to hold user_id. Assign it from the global in the WBP trigger mentioned above. Change the LOV to use the datablock item, see if that works.
3) Run a DB trace on the form session to see what query it is trying to send to the DB.
Re: Mandatory Values/LOV Prohibited values [message #551437 is a reply to message #551285] Wed, 18 April 2012 05:07 Go to previous messageGo to next message
vinni
Messages: 34
Registered: March 2012
Location: london
Member
sorry 4 the late reply. managed to solve the problem , a good old windows restart lol
Re: Mandatory Values/LOV Prohibited values [message #551512 is a reply to message #551437] Wed, 18 April 2012 14:24 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you that YOYO will fix the problem, and you didn't believe me ./fa/1599/0/
Re: Mandatory Values/LOV Prohibited values [message #607025 is a reply to message #551512] Sat, 01 February 2014 11:26 Go to previous message
l.d.
Messages: 1
Registered: February 2014
Junior Member
Hi!
I've had the same problem and the solution was to remove the "double minus" comments "--" from select and replace them with "/* */".
Previous Topic: Trigger on a Summarized Column
Next Topic: Oracle Form 10g Graph
Goto Forum:
  


Current Time: Fri Apr 18 08:55:23 CDT 2014

Total time taken to generate the page: 0.25351 seconds