Insert record [message #683295] |
Sat, 26 December 2020 05:36 |
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 #683303 is a reply to message #683301] |
Sat, 26 December 2020 10:45 |
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 #683325 is a reply to message #683319] |
Tue, 29 December 2020 23:07 |
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 #683329 is a reply to message #683328] |
Wed, 30 December 2020 00:48 |
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 |
|
Michel Cadot
Messages: 68665 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 |
|
Michel Cadot
Messages: 68665 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 #683344 is a reply to message #683336] |
Fri, 01 January 2021 06:12 |
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 #683355 is a reply to message #683352] |
Sat, 02 January 2021 10:45 |
|
Michel Cadot
Messages: 68665 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.
|
|
|