Home » SQL & PL/SQL » SQL & PL/SQL » checking the flag and comparing
checking the flag and comparing [message #359527] Mon, 17 November 2008 02:46 Go to next message
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 #359529 is a reply to message #359527] Mon, 17 November 2008 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "last records".

Regards
Michel
Re: checking the flag and comparing [message #359533 is a reply to message #359529] Mon, 17 November 2008 02:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #359538 is a reply to message #359537] Mon, 17 November 2008 02:57 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
I am not sure with that.
Will you be able to tell me what is the way to get the last record of the table? and also the question asked in the previous post ..
Re: checking the flag and comparing [message #359541 is a reply to message #359538] Mon, 17 November 2008 03:00 Go to previous messageGo to next message
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 #359543 is a reply to message #359527] Mon, 17 November 2008 03:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There is no way for retrieving the 'LAST' record unless you have some fields like 'last action date' .

Smile
Rajuvan.
Re: checking the flag and comparing [message #359575 is a reply to message #359527] Mon, 17 November 2008 06:23 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
if i have a field with time stamps then what will be the process.Please tell me .

Regards,
Shailendra
Re: checking the flag and comparing [message #359579 is a reply to message #359575] Mon, 17 November 2008 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also post your Oracle version (with 4 decimals like 10.2.0.4).

Regards
Michel
Re: checking the flag and comparing [message #359591 is a reply to message #359575] Mon, 17 November 2008 08:28 Go to previous messageGo to next message
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) Analytics
select 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 #359661 is a reply to message #359527] Mon, 17 November 2008 22:34 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Thanks a lot Buddy.. I will try that and will let you if it works.
Re: checking the flag and comparing [message #359685 is a reply to message #359527] Tue, 18 November 2008 00:33 Go to previous messageGo to next message
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 #359689 is a reply to message #359685] Tue, 18 November 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 17 November 2008 13:48
Post a test case: create table and insert statements along with the result you want with these data.

Also post your Oracle version (with 4 decimals like 10.2.0.4).

Regards
Michel


Re: checking the flag and comparing [message #359716 is a reply to message #359527] Tue, 18 November 2008 01:12 Go to previous message
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)

Previous Topic: "if condition" and "how to get output" (merged)
Next Topic: use varchar2 value as cursor elements ..
Goto Forum:
  


Current Time: Fri Dec 09 03:51:54 CST 2016

Total time taken to generate the page: 0.07448 seconds