Home » Developer & Programmer » Forms » Getting data from one table and saving into a new table (Developer 6i, Oracle9i)
Getting data from one table and saving into a new table [message #359234] Fri, 14 November 2008 05:17 Go to next message
roome
Messages: 8
Registered: November 2008
Location: Muzaffarabad Kashmir
Junior Member

Database table CASES carry all the records.

As few people have migrated from thier previos location. Now i wanna save thier new location and also want to have thier previous histroy.
I wanna to search record from this table CASES and then save with ammendments into a new table APPLICATION_RURAL.Now tell me some way that in new table APPLICATION_RURAL have both origional and ammended record.
FORM which i Use for this purpose is APPLICATION_REC_RURAL.
I have wrote this code for searching and then after ammendment i save it by SAVE BUTTON. I just mention the Search and ammendment programming here not Save Button Programming. Kindly help me.



DECLARE
V_FOUND NUMBER;
v_alert NUMBER;
V_mouno number :=NULL;
V_cnic number :=NULL;
BEGIN

--Search through NIC-------------------------
--=============================================

IF :CTRL_BLK.RG_SEARCH='N'THEN
SELECT COUNT(*) INTO V_FOUND FROM CASES WHERE C_NIC=:CTRL_BLK.V_SEARCH;

IF V_FOUND<>0 THEN
select nvl(count(*),0) into V_cnic from APLICATION_RURAL where C_NIC=:CTRL_BLK.V_SEARCH;
IF V_cnic <=0 then
INSERT INTO APLICATION_RURAL (ACC_NO, ACCT_TITLE, AMOUNT, APP_DATE,APP_ID, B_ADDRESS, B_BRANCH, BANK, C_NIC, COMMENTS, DISTRICT, ENTRY_BY, ENTRY_DATE, FATHER_NAME, PROVINCE, TEHSIL,UC, UPDATE_BY, UPDATE_DATE, VILLAGE)

SELECT ACC_NO, ACCT_TITLE, AMOUNT, APP_DATE,APP_ID, B_ADDRESS, B_BRANCH, BANK, C_NIC, COMMENTS, DISTRICT, ENTRY_BY, ENTRY_DATE, FATHER_NAME, PROVINCE, TEHSIL,UC, UPDATE_BY, UPDATE_DATE, VILLAGE FROM CASES
WHERE C_NIC=:CTRL_BLK.V_SEARCH;
COMMIT;
END IF;
END IF;
END IF;

Re: Getting data from one table and saving into a new table [message #359484 is a reply to message #359234] Sun, 16 November 2008 21:05 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I do not agree with your approach. I would use the single table and have either 'date_from' and 'date_to' fields or a single 'date_from' field. In the first case the current address is the one with the 'null' date_to' field for a particular 'acc_no'. In the second case, the current address is the record with the highest 'date_from' field for a particular 'acc_no'.

To try and make your code work, use 'standard.commit' instead of 'commit'. Search this forum for 'standard.commit' for previous explanations.

David
Re: Getting data from one table and saving into a new table [message #359664 is a reply to message #359234] Mon, 17 November 2008 23:06 Go to previous messageGo to next message
roome
Messages: 8
Registered: November 2008
Location: Muzaffarabad Kashmir
Junior Member

Our Database table CASES is the main table and carry all the records.

I search record from this table CASES and then save with ammendments into a new table APPLICATION_RURAL.Now tell me some way that in new table APPLICATION_RURAL have both origional and ammended record.
e.g As few people have migrated from thier previos location. Now i wanna save thier new location and also want to have thier previous histroy.
FORM which i Use for this purpose is APPLICATION_REC_RURAL.
I have wrote this code for searching and then after ammendment i save it by SAVE BUTTON. I mentioned here the CODE of Search Button and Save Button .

THIS CODE COMPILE SUCCESFULLY, AND ALSO FORM RUN SUCCESSFULY, FETCH DATA FROM CASES AND SAVE IT TO APPLICATION_RURAL SUCCESFULLY, But the only problem is that when i make changes to desired fields and save it, It make changes to existing record, But i require to save record with changes as a new record as well as origional record alongwith in new table APPLICATION_RURAL . Kindly help me.

code for SEARCH BUTTON is as under
DECLARE
  V_FOUND   NUMBER;
  v_alert   NUMBER;
  V_mouno   number := NULL;
  V_cnic    number := NULL;
BEGIN
--Search through NIC-------------------------
--=============================================
  IF :CTRL_BLK.RG_SEARCH = 'N' THEN
    SELECT COUNT (*)
      INTO V_FOUND
      FROM CASES
     WHERE C_NIC = :CTRL_BLK.V_SEARCH;
    IF V_FOUND0 THEN
      select nvl (count (*), 0)
        into V_cnic
        from APLICATION_RURAL
       where C_NIC = :CTRL_BLK.V_SEARCH;
      IF V_cnic <= 0 then
        INSERT INTO APLICATION_RURAL
                    (ACC_NO, ACCT_TITLE, AMOUNT, APP_DATE, APP_ID, B_ADDRESS,
                     B_BRANCH, BANK, C_NIC, COMMENTS, DISTRICT, ENTRY_BY,
                     ENTRY_DATE, FATHER_NAME, PROVINCE, TEHSIL, UC, UPDATE_BY,
                     UPDATE_DATE, VILLAGE)
          SELECT ACC_NO, ACCT_TITLE, AMOUNT, APP_DATE, APP_ID, B_ADDRESS,
                 B_BRANCH, BANK, C_NIC, COMMENTS, DISTRICT, ENTRY_BY,
                 ENTRY_DATE, FATHER_NAME, PROVINCE, TEHSIL, UC, UPDATE_BY,
                 UPDATE_DATE, VILLAGE
            FROM CASES
           WHERE C_NIC = :CTRL_BLK.V_SEARCH;
        COMMIT;
      END IF;
    END IF;
  END IF;
end;

code for SAVE BUTTON is as under

--Save Button Code--
====================
begin
  IF :APLICATION_REC.APP_ID IS NULL THEN
    SELECT NVL (MAX (APP_ID), 0)
      INTO :APLICATION_REC.APP_ID
      FROM APLICATION_RURAL;
    :APLICATION_REC.APP_ID  := :APLICATION_REC.APP_ID + 1;
    :APLICATION_REC.STATUS  := 'ARC';
    SELECT TO_DATE (SYSDATE, 'DD-Mon-YYYY HH24:MI:SS')
      INTO :APLICATION_REC.APP_DATE
      FROM DUAL;
    SELECT USERNAME
      INTO :APLICATION_REC.UPDATE_BY
      FROM USER_USERS;
    UPDATE APLICATION_RURAL
       SET APP_ID = :APLICATION_REC.APP_ID,
           APP_DATE = :APLICATION_REC.APP_DATE,
           UPDATE_BY = :APLICATION_REC.UPDATE_BY,
           UPDATE_DATE = :APLICATION_REC.APP_DATE
     WHERE C_NIC = :APLICATION_REC.C_NIC;
    COMMIT;
  else
    MESSAGE ('You Cannt Save the record');
    MESSAGE ('You Cannt Save the record');
    RAISE Form_Trigger_Failure;
  END IF;
end;

[EDITED by DJM: formatted the code and applied [code] tags - please do that yourself in the future]

[Updated on: Tue, 18 November 2008 00:47] by Moderator

Report message to a moderator

Re: Getting data from one table and saving into a new table [message #359692 is a reply to message #359664] Tue, 18 November 2008 00:49 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you placed 'message;pause;' pairs in your code and verified that the statements you are expecting to be run, are run?

Also, change the 'commit' to 'standard.commit'.

David
Re: Getting data from one table and saving into a new table [message #359824 is a reply to message #359692] Tue, 18 November 2008 05:06 Go to previous messageGo to next message
roome
Messages: 8
Registered: November 2008
Location: Muzaffarabad Kashmir
Junior Member

Yes i already have checked code by placing 'message;pause;' pairs in code and verified that the statements are running acurarely.
And i also have chaged the 'commit' to 'standard.commit'.

BUT THE MAIN ISSUE IS THAT I M NOT ABLE TO SAVE THE EXISTING RECORD OF TABLE CASES IN NEW TABLE RURAL_REC AS IT IS, AND THE AMMENDED RECORD COME AS A NEW ROW IN THE TABLE RURAL_REC. WHEN WE WILL SEARCH THE NEW TABLE RURAL_REC FOR A DESIRED RECORD, IT WILL SHOW TWO ENTRIES 1. ORIGIONAL ENTRY AND 2. AMMENDED ENTRY.
Re: Getting data from one table and saving into a new table [message #359944 is a reply to message #359824] Tue, 18 November 2008 22:58 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I thought you wanted both records in the 'rural_rec' table.

David
Re: Getting data from one table and saving into a new table [message #359993 is a reply to message #359944] Wed, 19 November 2008 02:47 Go to previous messageGo to next message
roome
Messages: 8
Registered: November 2008
Location: Muzaffarabad Kashmir
Junior Member

Yes sir, your rite ... I want both record in table RURAL_REC. This query which i m using is just save the ammended copy in RURAL_REC. I want to have both origional and ammended copy in RURAL_REC.

Could u please help me to modify this code accordingly or give me some nice tip to tackle this problem
Re: Getting data from one table and saving into a new table [message #360173 is a reply to message #359993] Wed, 19 November 2008 23:48 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Just write an 'insert' statement using ':rural_rec.item1', ':rural_rec.item2', ':rural_rec.item3', etc and put it with the 'update' statement.

David
Re: Getting data from one table and saving into a new table [message #360662 is a reply to message #360173] Fri, 21 November 2008 23:38 Go to previous messageGo to next message
roome
Messages: 8
Registered: November 2008
Location: Muzaffarabad Kashmir
Junior Member

thanx sir u got my point.
I m really thankful to u.
Kindly do me a favour, just point out where i should place this insert statement in SAVE BUTTON Code.
I tried insert statement with update statement, kindly guide me its exact position in code.

Thanx Again
Re: Getting data from one table and saving into a new table [message #360774 is a reply to message #360662] Mon, 24 November 2008 00:30 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
After the 'select' but before the 'standard.commit'.

David
Previous Topic: UTL_FILE time out
Next Topic: Getting Convid but not transferring the Data
Goto Forum:
  


Current Time: Fri Dec 02 22:43:57 CST 2016

Total time taken to generate the page: 0.13656 seconds