| inserting into another table from non db-items on a FORM [message #132097] |
Wed, 10 August 2005 16:44  |
ramisy2k
Messages: 150
Registered: April 2005
|
Senior Member |
|
|
Hi,
I am facing a huge problem. I would appreciate if someone helps me.
I have a form in which there is a database block. The block shows one record at a time. Two columns
of the block are jointly primary key which means they cannot be repeated combined...let's say the
two promary key columns are column_A, column_B
Plus there are some non-database numeric items on the form in which values are entered at run-time
by the pressing of a button. The problem is that i want to save/update/delete these values of non-db
items into another Table..
suppose the other table in which i want to save data has the followling descritpion
SQL> DESC TABLE
/
col1 number(4)
col2 number(4)
col3 number(4)
col4 number(4)
col5 number(4)
col6 number(4)
now suppose on the form i have also six non-db items..and i want to save each of the value of these
six non-db items into the columns of the table shown above..
let's say the six items on the form are :
ITEM_1
ITEM_2
ITEM_3
ITEM_4
ITEM_5
ITEM_6
where
Item_1 = col1
Item_2 = col2
Item_3 = col2
Item_4 = col4
Item_5 = col5
Item_6 = col6
it should be noted that the values of item_1 and item2 are equal to the values of column_A and
column_B respectively for each record of the databse block..(both column_A and column_A are
primary keys of the db-block on the form as mentioned above)
i.e. item_1 gets its value from column_A
and item_2_gets its value from column_B
My requiremnet is to have such an efficient method that will insert/update the data in the TABLE as
soon as a change is made in any of the six non-db items.
while updating the other table it should update only those records
where col1 = item_1 or column_A
and
col2 = item_2 or column_b
Hope this will clear my requirement
regards,
Asim.
|
|
|
|
|
|
|
|
|
|
| Re: inserting into another table from non db-items on a FORM [message #132199 is a reply to message #132097] |
Thu, 11 August 2005 05:46  |
reachme_r
Messages: 8
Registered: July 2005
Location: india
|
Junior Member |
|
|
Dear Asim,
The way i have intercepted ur problem. do u jst need to insert/update those non-database item values in the other table.
If this is what u want then can't u write an "insert into " and "update table " (sql queries) in the pre-insert and pre-update triggers of the database item block, whereby in those triggers the table being ur "other table"
(check ur "where" clause)
regards
vinod
|
|
|
|
| Re: inserting into another table from non db-items on a FORM [message #132234 is a reply to message #132097] |
Thu, 11 August 2005 08:28 |
ramisy2k
Messages: 150
Registered: April 2005
|
Senior Member |
|
|
reachme_r,
thanks for ur meesage..yes u intercepted my problem correctly but the prolem is that i am already using the following code to update the item values
but its not good and sometimes doesnot update and sometimes update the values...i want some other cursur/procedure to accomplish this..
update TABLE
set
COL1 = :ITEM_1,
COL2 = :ITEM_2,
COL3 = :ITEM_3,
COL4 = :ITEM_4,
COL5 = :ITEM_5,
COL6 = :ITEM_6
WHERE
COL1 = :ITEM_1
AND
COL2 = :ITEM_2;
insert statement will only insert new records and will not update existing records, thats why i used the update statement..
can anyone some other effiecient way???
regards
Asim.
|
|
|
|
| Re: inserting into another table from non db-items on a FORM [message #132255 is a reply to message #132234] |
Thu, 11 August 2005 10:18 |
 |
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
|
Senior Member |
|
|
hi,
I don't have access to database and forms right now so I didn't test this code.
However, if this doesn't work you can at least apply the logic to get the problem solved.
Here's the code:
DECLARE
v_1 NUMBER;
v_2 NUMBER;
v_3 NUMBER;
v_4 NUMBER;
v_5 NUMBER;
v_6 NUMBER;
CURSOR c1 IS
SELECT col1, co2
FROM your_table
WHERE col1 = :block.item_1
AND col2 = :block.item_2;
c2 c1%ROWTYPE;
BEGIN
v_1 := item_1;
v_2 := item_2;
v_3 := item_3;
v_4 := item_4;
v_5 := item_5;
v_6 := item_6;
OPEN c1;
LOOP FETCH c1 INTO c2;
IF SQL%FOUND THEN --OR IF SQL%ROWCOUNT > 0 THEN
UPDATE your_table
........
ELSE
INSERT INTO your_table
........
END IF;
END LOOP;
CLOSE c1;
END;
Hope this will help.
Regards
[Updated on: Thu, 11 August 2005 10:22] Report message to a moderator |
|
|
|
| Re: inserting into another table from non db-items on a FORM [message #132272 is a reply to message #132097] |
Thu, 11 August 2005 12:00 |
ramisy2k
Messages: 150
Registered: April 2005
|
Senior Member |
|
|
Hi saadatahmad,
problem not solved yet...many thanks for ur reply..
i tried your cursor in my actual form and naming the actual column/item names...and the procedure succesfull compiled...
I put the procedure on button-pressed trigger and made some changes in the non-db items and then presed the button but no changes are made to the database...
following is the exact code i used in my actual from..
DECLARE
mglobal_MATCH_ID NUMBER;
mglobal_TEAM_1ST_ID NUMBER;
mPLAYER_BATTING_ORDER_NO NUMBER;
mPLAYER_1_ID NUMBER;
mPLAYER_1_DISMISSAL_ID NUMBER;
mFIELDER_FOR_PLAYER_1 NUMBER;
mFIELDER2_FOR_PLAYER_1 NUMBER;
mBOWLER_FOR_PLAYER_1 NUMBER;
mGET_PLAYER_1_SCORE NUMBER;
mGET_PLAYER_1_BALLS NUMBER;
mGET_PLAYER_1_MINS NUMBER;
mGET_PLAYER_1_4s NUMBER;
mGET_PLAYER_1_6s NUMBER;
mGET_PLAYER_1_SR NUMBER;
CURSOR c1 IS
SELECT MATCH_ID, TEAM_ID, BATTING_ORDER
FROM INNINGS_BATTING
WHERE MATCH_ID = :global.MATCH_ID
AND TEAM_ID = :global.TEAM_1ST_ID
AND BATTING_ORDER = mPLAYER_BATTING_ORDER_NO;
C2 C1%ROWTYPE;
BEGIN
mglobal_MATCH_ID := :global.MATCH_ID;
mglobal_TEAM_1ST_ID := :global.TEAM_1ST_ID;
mPLAYER_BATTING_ORDER_NO := :PLAYER_BATTING_ORDER_NO;
mPLAYER_1_ID := :PLAYER_1_ID;
mPLAYER_1_DISMISSAL_ID := :PLAYER_1_DISMISSAL_ID;
mFIELDER_FOR_PLAYER_1 := :FIELDER_FOR_PLAYER_1;
mFIELDER2_FOR_PLAYER_1 := :FIELDER2_FOR_PLAYER_1;
mBOWLER_FOR_PLAYER_1 := :BOWLER_FOR_PLAYER_1;
mGET_PLAYER_1_SCORE := :GET_PLAYER_1_SCORE;
mGET_PLAYER_1_BALLS := :GET_PLAYER_1_BALLS;
mGET_PLAYER_1_MINS := :GET_PLAYER_1_MINS;
mGET_PLAYER_1_4s := :GET_PLAYER_1_4s;
mGET_PLAYER_1_6s := :GET_PLAYER_1_6s;
mGET_PLAYER_1_SR := :GET_PLAYER_1_SR;
OPEN c1;
LOOP FETCH c1 INTO c2;
IF SQL%FOUND
THEN --- OR IF SQL%ROWCOUNT > 0 THEN
UPDATE INNINGS_BATTING
SET
match_id = mglobal_MATCH_ID,
team_id = mglobal_TEAM_1ST_ID,
BATTING_ORDER = mPLAYER_BATTING_ORDER_NO,
player_id = mPLAYER_1_ID,
DISMISSAL_ID = mPLAYER_1_DISMISSAL_ID,
FIELDER_1ST_ID = mFIELDER_FOR_PLAYER_1,
FIELDER_2ND_ID = mFIELDER2_FOR_PLAYER_1,
BOWLER_ID = mBOWLER_FOR_PLAYER_1,
RUNS = mGET_PLAYER_1_SCORE,
BALLS = mGET_PLAYER_1_BALLS,
MINUTES = mGET_PLAYER_1_MINS,
FOURS = mGET_PLAYER_1_4s,
SIXES = mGET_PLAYER_1_6s,
SR = mGET_PLAYER_1_SR
WHERE
match_id = :global.MATCH_ID
AND
team_id = :global.TEAM_1ST_ID
AND BATTING_ORDER = mPLAYER_BATTING_ORDER_NO;
ELSE
INSERT INTO INNINGS_BATTING (match_id , team_id , BATTING_ORDER, player_id,
DISMISSAL_ID, FIELDER_1ST_ID, FIELDER_2ND_ID, BOWLER_ID,
RUNS , BALLS , MINUTES , FOURS , SIXES , SR)
VALUES (mglobal_MATCH_ID, mglobal_TEAM_1ST_ID, mPLAYER_BATTING_ORDER_NO,
mPLAYER_1_ID, mPLAYER_1_DISMISSAL_ID, mFIELDER_FOR_PLAYER_1,
mFIELDER2_FOR_PLAYER_1 , mBOWLER_FOR_PLAYER_1,
mGET_PLAYER_1_SCORE, mGET_PLAYER_1_BALLS, mGET_PLAYER_1_MINS,
mGET_PLAYER_1_4s, mGET_PLAYER_1_6s, mGET_PLAYER_1_SR);
END IF;
END LOOP;
CLOSE c1;
COMMIT;
END;
please tell me whats the problem now..
regards,
Asim.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
