Home » Developer & Programmer » Forms » Insert record
Insert record [message #683295] Sat, 26 December 2020 05:36 Go to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Dear friends.


I need your idea to resolve the problem.

Example

I have one table (name Products) and it has three column (id,lot_no and qty) .

Id Lot_no Qty
1001 100251 50
1001 100252 100

Now If i insert record id=1001 and qty =130

Result should be shown as
Id Lot_no Qty
1001 100251 50
1001 100252 80

kindly guide me.

Thanks

Haider

Re: Insert record [message #683296 is a reply to message #683295] Sat, 26 December 2020 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Insert record [message #683298 is a reply to message #683296] Sat, 26 December 2020 06:23 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 584
Registered: August 2002
Senior Member
search forum running sum


Quote:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947
Re: Insert record [message #683299 is a reply to message #683298] Sat, 26 December 2020 07:42 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Thanks for reply .
I want to auto select result which is mention up .
Re: Insert record [message #683301 is a reply to message #683299] Sat, 26 December 2020 10:09 Go to previous messageGo to next message
Littlefoot
Messages: 21689
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
haider_1pk

Result should be shown as ...
Why? You inserted the 3rd row (for the same ID, without LOT_NO, QTY = 130). How come that QTY (which was previously 100) now became 80? Maybe it is obvious, but it isn't for me.
Re: Insert record [message #683303 is a reply to message #683301] Sat, 26 December 2020 10:45 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Sir. sorry i could not able to describe you. That's why I try to describe you in picture .If i insert the value Id and qty in oracle forms.
Re: Insert record [message #683304 is a reply to message #683303] Sat, 26 December 2020 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does explain nothing.

How and why does the 100 change to 80 when you insert 130.
Why does the "first" line (50) is not change? Why is this the "second" one?
What is the relation with 130?

Re: Insert record [message #683308 is a reply to message #683304] Mon, 28 December 2020 00:15 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Sir I am trying to describe you in pictures .
  • Attachment: 1.gif
    (Size: 326.49KB, Downloaded 339 times)
Re: Insert record [message #683309 is a reply to message #683308] Mon, 28 December 2020 00:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 584
Registered: August 2002
Senior Member
send a new picture with clear
Re: Insert record [message #683310 is a reply to message #683308] Mon, 28 December 2020 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

haider_1pk wrote on Mon, 28 December 2020 07:15
Sir I am trying to describe you in pictures .

Instead answer the questions with words.

[Updated on: Mon, 28 December 2020 01:08]

Report message to a moderator

Re: Insert record [message #683317 is a reply to message #683310] Mon, 28 December 2020 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are the results if you "insert" 1001 with qty:
  • 200?
  • 60?
  • 40?
  • 0?
Re: Insert record [message #683319 is a reply to message #683317] Mon, 28 December 2020 16:00 Go to previous messageGo to next message
Littlefoot
Messages: 21689
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't have to repost the same information all over again. We see it in the original message.

What we don't understand is the algorithm which leads from current data, over values you insert, to target data. Can you explain that?
Re: Insert record [message #683325 is a reply to message #683319] Tue, 29 December 2020 23:07 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Dear Sir,
I think very simple question that is, I have a table of product which contain three column id, lot_no and qty , and its data values are
Id Lot_no Qty
1001 100251 50
1001 100252 100
now i have make a forms in oracle which has two column Id and qty , and also make push button for trigger which select id and qty from forms (id 1001 and qty 130)
select the values in data which are
Id Lot_no Qty
1001 100251 50
1001 100252 80

if forms vales has id 1001 and qty 70
then
select the values in data which are
Id Lot_no Qty
1001 100251 50
1001 100252 20

I hope now you will understand

Regards

Haider

Re: Insert record [message #683328 is a reply to message #683325] Wed, 30 December 2020 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 28 December 2020 19:35

What are the results if you "insert" 1001 with qty:

  • 200?
  • 60?
  • 40?
  • 0?
Re: Insert record [message #683329 is a reply to message #683328] Wed, 30 December 2020 00:48 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
if qty 200 then not working because balance is 150 .total. if qty 60 then
result

Id Lot_no Qty
1001 100251 50
1001 100252 10
if qty 40 then
result

Id Lot_no Qty
1001 100251 40
if qty 0 then
result

no
Re: Insert record [message #683330 is a reply to message #683329] Wed, 30 December 2020 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So, WITH WORDS, you want to DELETE and/or UPDATE the current rows so the balance of them matches the new BALANCE (not qty) in the form fields.
There are NO INSERTS.
Is this a DISPLAY stuff (changes are just in the form) or DATABASE changes?
In other words, do you want to DISPLAY how the db rows can fit the (new) balance you ask in the form or do you want to UPDATE the data inside the database?


Post what is requested:


Michel Cadot wrote on Sat, 26 December 2020 12:47

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Wed, 30 December 2020 01:06]

Report message to a moderator

Re: Insert record [message #683333 is a reply to message #683330] Wed, 30 December 2020 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's an example. Let's say we have products that are contained in boxes, a box contains only one product type, boxes can contain different numbers of a product:
SQL> drop table t;

Table dropped.

SQL> create table t
  2  as
  3  select 1 as prodid, level as boxid,
  4         10*round(dbms_random.value(5,15)) as qty
  5  from dual
  6  connect by level <= 5
  7  /

Table created.

SQL> insert into t
  2  select 2, 2*(10+boxid), 10*round(dbms_random.value(5,15))
  3  from t where rownum <= 3
  4  /

3 rows created.

SQL> select * from t order by 1, 2
  2  /
    PRODID      BOXID        QTY
---------- ---------- ----------
         1          1         80
         1          2        140
         1          3         50
         1          4         90
         1          5        150
         2         22        120
         2         24         60
         2         26        140

8 rows selected.
Boxes are ordered by their id which means we collect products from the first boxes before the other ones.
Now let's say we want "qty" elements of product "prodid".
Here what you can do:
SQL> def prodid=1
SQL> def qty=100
SQL> with
  2    data as (
  3      select prodid, boxid, qty,
  4             sum(qty)
  5               over (order by boxid
  6                     rows between unbounded preceding and current row)
  7               cumsum,
  8             sum(qty) over () totsum
  9      from t
 10      where prodid = &prodid
 11    )
 12  select prodid, boxid,
 13         case
 14           when &qty > cumsum then qty
 15           else qty-(cumsum-&qty)
 16         end qty,
 17         'Remainder: '||to_char(greatest(0,cumsum-&qty)) comments
 18  from data
 19  where qty > cumsum-&qty and &qty <= totsum
 20  union all
 21  select prodid, null, null,
 22         'Asked quantity (&qty) greater than available ('||totsum||')'
 23  from data
 24  where &qty > totsum and rownum = 1
 25  order by 1, 2
 26  /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1          1         80 Remainder: 0
         1          2         20 Remainder: 120

2 rows selected.

SQL> def qty=200
SQL> /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1          1         80 Remainder: 0
         1          2        120 Remainder: 20

2 rows selected.

SQL> def qty=300
SQL> /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1          1         80 Remainder: 0
         1          2        140 Remainder: 0
         1          3         50 Remainder: 0
         1          4         30 Remainder: 60

4 rows selected.

SQL> def qty=400
SQL> /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1          1         80 Remainder: 0
         1          2        140 Remainder: 0
         1          3         50 Remainder: 0
         1          4         90 Remainder: 0
         1          5         40 Remainder: 110

5 rows selected.

SQL> def qty=500
SQL> /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1          1         80 Remainder: 0
         1          2        140 Remainder: 0
         1          3         50 Remainder: 0
         1          4         90 Remainder: 0
         1          5        140 Remainder: 10

5 rows selected.

SQL> def qty=600
SQL> /
    PRODID      BOXID        QTY COMMENTS
---------- ---------- ---------- --------------------------------------------------
         1                       Asked quantity (600) greater than available (510)

1 row selected.
Is your scenario something like that?

[Updated on: Thu, 31 December 2020 01:25]

Report message to a moderator

Re: Insert record [message #683334 is a reply to message #683333] Wed, 30 December 2020 13:05 Go to previous messageGo to next message
Littlefoot
Messages: 21689
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(I don't know if this answer the question, but - it's just marvelous, Michel!]
Re: Insert record [message #683336 is a reply to message #683334] Wed, 30 December 2020 22:30 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Thank you very much Sir. I have received answer in my mail.
Re: Insert record [message #683344 is a reply to message #683336] Fri, 01 January 2021 06:12 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Dear Sir ,

Only guide me about it.Is it procedure or function?
def prodid=1
def qty=100
with
data as (
select prodid, boxid, qty,
sum(qty)
over (order by boxid
rows between unbounded preceding and current row)
cumsum,
sum(qty) over () totsum
from t
where prodid = &prodid
)
select prodid, boxid,
case
when &qty > cumsum then qty
else qty-(cumsum-&qty)
end qty,
'Remainder: '||to_char(greatest(0,cumsum-&qty)) comments
from data
where qty > cumsum-&qty and &qty <= totsum
union all
select prodid, null, null,
'Asked quantity (&qty) greater than available ('||totsum||')'
from data
where &qty > totsum and rownum = 1
order by 1, 2


Thanks and Regards
Re: Insert record [message #683345 is a reply to message #683344] Fri, 01 January 2021 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is a single SQL SELECT statement.

Re: Insert record [message #683350 is a reply to message #683345] Fri, 01 January 2021 23:02 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Sir in Statement i found error ( from data) table is not exist.KIndly guide me
Thanks
Re: Insert record [message #683351 is a reply to message #683350] Sat, 02 January 2021 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WITH clause defines "data".

[Updated on: Sat, 02 January 2021 00:27]

Report message to a moderator

Re: Insert record [message #683352 is a reply to message #683351] Sat, 02 January 2021 04:38 Go to previous messageGo to next message
haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN
Senior Member
Sir can you guide me how run this statment in sql oracle. I try many time . but all time found error
Re: Insert record [message #683355 is a reply to message #683352] Sat, 02 January 2021 10:45 Go to previous message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I executed it in SQL*Plus, SQL Oracle, above.

Quote:
but all time found error

If you don't show us as I did then we can't help.
And the first thing to do is to give us your Oracle version.

Previous Topic: Passing 'ALL' value in reports 10g parameter
Next Topic: Insert with order by in forms
Goto Forum:
  


Current Time: Tue Oct 19 10:27:58 CDT 2021