| autonumber field - not/not null [merged] [message #482567] |
Fri, 12 November 2010 11:05  |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
declare
a number;
b number;
begin
if :class_name = 'DAE (ELECTRICAL)' then
select nvl(id,0) into b from stu_info;
if b is null then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (ELECTRONICS)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (CIVIL TECHNOLOGY)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (MECHNICAL TECHNOLOGY)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
end if;
end;
next_item;
-
Attachment: 123.JPG
(Size: 63.60KB, Downloaded 1248 times)
|
|
|
|
|
|
|
|
|
|
| autonumber [message #483116 is a reply to message #482567] |
Thu, 18 November 2010 09:47  |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Structure:
If class = 'DAE' then start from 3100 first time. then every time pick from table.
If class = 'B-Tech' then start from 4100 first time. then every time from table.
...pls check the attached code and advised me where i am wrong.
-----------------------------------------------------
declare
a number;
b number;
begin
select COUNT(id) into b from stu_info where class_name=:stu_info.class_name;
if :stu_info.class_name = 'DAE (CIVIL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (CIVIL TECHNOLOGY)';
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and :stu_info.class_name ='DAE (CIVIL TECHNOLOGY)';
end if;
if :class_name = 'DAE (ELECTRICAL)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (ELECTRICAL)';
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and :class_name ='DAE (ELECTRICAL)';
end if;
if :class_name = 'DAE (ELECTRONICS)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (ELECTRONICS)';
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and :class_name ='DAE (ELECTRONICS)';
end if;
if :class_name = 'DAE (MECHNICAL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (MECHNICAL TECHNOLOGY)';
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and :class_name ='DAE (MECHNICAL TECHNOLOGY)';
end if;
exception
when no_data_found then
Message('Please Enter The Roll No List in Session...');
Message('Please Enter The Roll No List in Session...');
Raise form_trigger_failure;
end;
next_item;
----------------------------------------------
|
|
|
|
| Re: autonumber [message #483117 is a reply to message #483116] |
Thu, 18 November 2010 09:56 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
shahzad-ul-hasan wrote on Thu, 18 November 2010 15:47Structure:
If class = 'DAE' then start from 3100 first time.
Start what from 3100?
Quote:
then every time pick from table.
Pick what from what table?
Quote:
If class = 'B-Tech' then start from 4100 first time. then every time from table.
Same questions as above.
Quote:
...pls check the attached code and advised me where i am wrong.
We have no idea what that code is supposed to do or what it is actually doing. Plus the complete lack of identation and white space makes it really hard to read.
So explain in detail what the code is supposed to do and what it is actually doing.
Repost the code, formatted, in [code] tags - see the fourm guide if you're not sure how
|
|
|
|
| Re: autonumber [message #483119 is a reply to message #483117] |
Thu, 18 November 2010 10:00 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Dear
my mean is that if i select a class 'DAE' the id should be start from 3100. and if i select class 'Btech' then the id should start from 4100. My problem is that when i select class 'DAE' the id start autonumber from 1. but i want to start from 3100 when no data exist.and next time it should pick its maximum from the table.
please advised.
|
|
|
|
| Re: autonumber [message #483121 is a reply to message #483119] |
Thu, 18 November 2010 10:21 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you check if you found no data and if so use the value of 3100. That's programming at it's most basic.
Why ask where you're going wrong when the code makes no reference to the values you want to use?
By the way, this:
if :stu_info.class_name = 'DAE (CIVIL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (CIVIL TECHNOLOGY)';
if :stu_info.class_name = 'DAE (CIVIL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where 1=1;
I assume the where clause should be refering to a column in the table and not an item in the datablock (which is what : signifies).
Likewise all your where clauses appear to be wrong.
|
|
|
|
|
|
|
|
|
|
| Re: autonumber [message #483155 is a reply to message #483151] |
Thu, 18 November 2010 13:31 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
|
I HAVE REMOVED THE ERROR. BUT IT GIVES ME AUTONUMBER 1 INSTEAD OF 3100,4100,2100.FIRST TIME IT WILL PICK 2100 FROM INITLIZED STAGE AND STORED IN DATABASE.THEN AFTER IT WILL PICK THE MAX VALUES PLUS 1 FROM DATABASE. BUT IT NOT WORKING.PLEASE ADVISED.
|
|
|
|
| Re: autonumber [message #483156 is a reply to message #483155] |
Thu, 18 November 2010 13:52 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Here's one piece of advise - don't post in all capital letters, it's considered shouting, is against the forum guidelines, and puts people off helping you.
Past that - the code you have posted here contains no reference to the numbers you say you want to use. So write some code that does use those numbers. We don't mind helping people but we are not going to write your code for you.
|
|
|
|
| Re: autonumber [message #483167 is a reply to message #483156] |
Thu, 18 November 2010 19:59 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and :class_name ='DAE (MECHNICAL TECHNOLOGY)';
here is the reference values i can used in first time. then pick from the tABLE.
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where LTRIM(class_name) =RTRIM('DAE (MECHNICAL TECHNOLOGY)');
|
|
|
|
|
|
| Re: autonumber [message #483580 is a reply to message #483197] |
Tue, 23 November 2010 04:30 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
2) Where does the value 3100 come into this?
i have stored these values(1100,2100,3100) in another table. that why i am calling these values first time. and then from the other table.
|
|
|
|
| Re: autonumber [message #483584 is a reply to message #483121] |
Tue, 23 November 2010 04:40 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And you didn't think it'd be a good idea to tell us?
Before you go any further fix this:
cookiemonster wrote on Thu, 18 November 2010 16:21By the way, this:
if :stu_info.class_name = 'DAE (CIVIL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where :class_name = 'DAE (CIVIL TECHNOLOGY)';
if :stu_info.class_name = 'DAE (CIVIL TECHNOLOGY)' and b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where 1=1;
I assume the where clause should be refering to a column in the table and not an item in the datablock (which is what : signifies).
Likewise all your where clauses appear to be wrong.
|
|
|
|
| Re: autonumber [message #483828 is a reply to message #483584] |
Wed, 24 November 2010 20:44 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
Dear
i have solved the problem.there is a little problem every time i create a new record.Its work for two or three enteries and then It pick up the maximum after two or three enteries.pls advised me where i can put this code in a trigger.
declare
a number;
b number;
begin
if :class_name = 'DAE (ELECTRICAL)' then
select nvl(id,0) into b from stu_info;
if b is null then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (ELECTRONICS)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (CIVIL TECHNOLOGY)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Elsif :class_name = 'DAE (MECHNICAL TECHNOLOGY)' then
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
end if;
end;
next_item;
--------------------------------------------------------------
|
|
|
|
|
|
| Re: autonumber [message #483878 is a reply to message #483850] |
Thu, 25 November 2010 04:08 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You do realise that you're using the same code for each class_name don't you?
And you're populating a variable called a which you then don't use.
As we're getting nowhere fast I'll suggest that this:
select nvl(id,0) into b from stu_info;
if b is null or b = 0 then
select nvl(max(roll),0)+1 into a from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
else
select nvl(max(id),0)+1 into :stu_info.id from stu_info;
end if;
Has one select too many, and as Littlefoot suggests is missing some exception handlers (quite why I'm not sure since you've added them once already).
This is probably closer to what you want:
BEGIN
select max(id) + 1 into :stu_info.id
from stu_info
WHERE <some where clause that actually restricts by class name
unlike your original where clause that did not despite what you thought>;
EXCEPTION WHEN no_data_found THEN
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id;
END;
Note how that's formatted - it's a lot more readable than your code. That's how you should always format your code.
|
|
|
|
| Re: autonumber [message #483916 is a reply to message #483878] |
Thu, 25 November 2010 09:23 |
shahzad-ul-hasan
Messages: 643
Registered: August 2002
|
Senior Member |
|
|
eclare
a number;
b number;
c varchar2(300);
begin
select COUNT(id) into b from stu_info
where class_name IN ('DAE (CIVIL TECHNOLOGY)','DAE (ELECTRICAL)',
'DAE (ELECTRONICS)','DAE (MECHNICAL TECHNOLOGY)');
if LTRIM(:CLASS_NAME)='DAE (CIVIL TECHNOLOGY)' THEN
IF B > 0 THEN
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where LTRIM(class_name)=RTRIM('DAE (CIVIL TECHNOLOGY)') ;
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and LTRIM(:class_name)=RTRIM('DAE (CIVIL TECHNOLOGY)');
end if;
ELSIF LTRIM(:CLASS_NAME)='DAE (ELECTRICAL)' THEN
IF B > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where LTRIM(:class_name)=RTRIM('DAE (ELECTRICAL)');
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and LTRIM(:class_name)=RTRIM('DAE (ELECTRICAL)');
end if;
ELSif LTRIM(:CLASS_NAME)='DAE (ELECTRONICS)' THEN
IF b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where LTRIM(class_name)=RTRIM('DAE (ELECTRONICS)');
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and LTRIM(:class_name)=RTRIM('DAE (ELECTRONICS)');
end if;
ELSif LTRIM(:CLASS_NAME)='DAE (MECHNICAL TECHNOLOGY)' THEN
IF b > 0 then
select nvl(max(id),0)+1 into :stu_info.id from stu_info
where LTRIM(class_name)=RTRIM('DAE (MECHNICAL TECHNOLOGY)') ;
else
select nvl(max(roll),0)+1 into :stu_info.id from ses_det,sesion
where ses_det.ses_id=sesion.ses_id and LTRIM(:class_name)=RTRIM('DAE (MECHNICAL TECHNOLOGY)');
End if;
END IF;
exception
when no_data_found then
Message('Please Enter The Roll No List in Session...');
Message('Please Enter The Roll No List in Session...');
Raise form_trigger_failure;
end;
next_item;
pls send me your email address so i will send you its movie. how the form is run/execute and then may you will in position to notify me where i can be wrong?
|
|
|
|
| Re: autonumber [message #483919 is a reply to message #483916] |
Thu, 25 November 2010 09:48 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're not getting my email address.
I'm also not prepared to help you further if you're too lazy to format your code.
Read the forum guide and follow the instructions on formatting - use [code] tags not bold.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
