checking the flag and comparing [message #359527] |
Mon, 17 November 2008 02:46  |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
I need to Verify "state" field from table_name , it should be either "E" or "P".Then need to check the last records in table_name.
and if no Record exists then new record will be inserted in table_name.
What can be the probable procedure code in oracle for this ?
|
|
|
|
Re: checking the flag and comparing [message #359533 is a reply to message #359529] |
Mon, 17 November 2008 02:52   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
select state_field from table_name where rowid = (select max(rowid) from table_name);.
I think this is the query to fetch the last record of table_name. But before i need to check the condition , "state" field from table_name , it should be either "E" or "P".Then need to check the last records in table_name.
|
|
|
Re: checking the flag and comparing [message #359537 is a reply to message #359533] |
Mon, 17 November 2008 02:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Nope.
That query just fetches the record with the 'largest' row id.
That's not automatically the most recently inserted record.
Are you sure that you want 'somne record at random' to be classed as the 'last record'?
|
|
|
|
Re: checking the flag and comparing [message #359541 is a reply to message #359538] |
Mon, 17 November 2008 03:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The reason that we're asking what you want the 'last record' to be is that there is no such concempt inherrent to a relational database.
'Last' only exists in reference to your data - if you have a 'Created Date' column, that can be used to define the last record.
If you have a sequence populated field, that can be used to define last.
If you don't have something like this, then you have no way of finding the most recently inserted record.
|
|
|
|
|
|
Re: checking the flag and comparing [message #359591 is a reply to message #359575] |
Mon, 17 November 2008 08:28   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I can think of 3 approaches.
I'd have posted working code, but you didn't feel the need to post a test case.
1) Straight select:select state_field
from table_name
where date_field = (select max(date_field)
from table_name);.
2) Analyticsselect state_field
from (select first_value(state_fiel) over (order by date_field desc) state_field
,row_number() over (order by date_field desc) rnum
from table_name)
where rnum = 1;
3) Neat MAX value trick:SELECT substr(max_field,-1) state_field
FROM (SELECT max(to_char(date_field,'yyyymmddhh24miss')||state_field)
FROM table_name)
|
|
|
|
Re: checking the flag and comparing [message #359685 is a reply to message #359527] |
Tue, 18 November 2008 00:33   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
yes i am able to get the correct values.
After that suppose i have table 1 and using that table 1 i need fill table 2. I mean using fields of table 1 fill the fields of table 2.
So do i need to take the the column names in another variable, How should i proceed further?
Any link or sample code will be helpful.
|
|
|
|
Re: checking the flag and comparing [message #359716 is a reply to message #359527] |
Tue, 18 November 2008 01:12  |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
BEGIN
-- Check to see if a record already exists
SELECT COUNT (*)
INTO v_recordexists
FROM Members1
WHERE FirstName = P_FirstName
AND Lastname = P_LastName
AND Email = P_Email;
-- If Prospective Member does not exist, insert record
IF v_recordexists = 0 THEN
-- Insert row into Members2 table
INSERT INTO ............
The members2 table will contain the details of the members1.
for eg - date when the record entered and counter , state (E,P,T etc)
|
|
|