Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 10G, Windows 2003)
| Query Help [message #576198] |
Fri, 01 February 2013 05:16  |
mmohsinaziz
Messages: 79
Registered: May 2012
|
Member |
|
|
Dear Experts,
I need your help to find a query for the following result.
I have two tables Receipt and Issue. In receipts all the receipts are recorded and issue table all the issuance are recoded.
Point is we have multiple recipts of an item and one or more issues against it. I want know the party names from recipts against whose issuance is done.
Also, help me to find a query to break a amount into random small amounts whose total is equal to given amount.
For example If i have a amount 30335 and we want break it between 10000 and 15000 random amounts. Total of breaked amount will be 30335.
Sample data is below.
CREATE TABLE receipt
(
rno NUMBER(3),
code NUMBER(2),
party VARCHAR2(15),
qty NUMBER(3),
amount NUMBER(5)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
CREATE TABLE issue
(
ino NUMBER(3),
rno NUMBER(3),
code NUMBER(2),
qty NUMBER(3),
amount NUMBER(5)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
INSERT INTO receipt
VALUES (1, 10, 'ABC', 1, 975);
INSERT INTO receipt
VALUES (1, 10, 'ACB', 1, 975);
INSERT INTO receipt
VALUES (1, 10, 'XYZ', 1, 975);
INSERT INTO receipt
VALUES (1, 10, 'BCA', 1, 975);
INSERT INTO receipt
VALUES (1, 10, 'BAC', 1, 975);
INSERT INTO receipt
VALUES (1, 10, 'CBA', 2, 1850);
INSERT INTO issue
VALUES (5, 1, 10, 3, 2925);
INSERT INTO issue
VALUES (15, 1, 10, 2, 1850);
commit;
SQL>select * from receipt;
RNO CODE PARTY QTY AMOUNT
---------- ---------- --------------- ---------- ----------
1 10 ABC 1 975
1 10 ACB 1 975
1 10 XYZ 1 975
1 10 BCA 1 975
1 10 BAC 1 975
1 10 CBA 2 1850
6 rows selected.
SQL>select * from issue;
INO RNO CODE QTY AMOUNT
---------- ---------- ---------- ---------- ----------
5 1 10 3 2925
15 1 10 2 1850
Required Output is
INO RNO PARTY QTY AMOUNT
---------- ---------- --------------- ---------- ----------
5 1 ABC 1 975
5 1 ACB 1 975
5 1 BCA 1 975
15 1 CBA 2 1850
Party names could be any against this rno.
Thanks.
M. Mohsin
|
|
|
|
| Re: Query Help [message #576202 is a reply to message #576198] |
Fri, 01 February 2013 06:24  |
mmohsinaziz
Messages: 79
Registered: May 2012
|
Member |
|
|
For Random numbers i have tried the following query. It is giving the single line result but i require a list.
SELECT *
FROM (SELECT ROUND (DBMS_RANDOM.VALUE (10000, 15000)) amount
FROM DUAL)
HAVING ROUND (DBMS_RANDOM.VALUE (10000, 15000)) <= 30335;
AMOUNT
----------
13224
Please help to resolve it.
M.Mohisn
|
|
|
|
|
|
|
|
| Re: Query Help [message #576249 is a reply to message #576248] |
Fri, 01 February 2013 22:36 |
mmohsinaziz
Messages: 79
Registered: May 2012
|
Member |
|
|
Dear Michel,
No one output is equal to 30335. The difference amount must be in 3rd row.
Output will be like this
SQL> /
AMOUNT
----------
14068
12114
4153
3 rows selected.
SQL> /
AMOUNT
----------
14307
10431
5597
3 rows selected.
SQL> /
AMOUNT
----------
12382
12261
5692
3 rows selected.
Thanks
M. Mohsin
|
|
|
|
| Re: Query Help [message #576258 is a reply to message #576249] |
Sat, 02 February 2013 01:30 |
 |
Michel Cadot
Messages: 54253
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Just a slight modification of the query to include the last row:
SQL> break on report
SQL> compute sum of amount on report
SQL> with
2 numbers as (
3 SELECT ROUND (DBMS_RANDOM.VALUE (10000, 15000)) amount, rownum rn
4 FROM DUAL
5 connect by level <= 10
6 ),
7 rolling_sum as (
8 select amount, sum(amount) over (order by rn) rolling_sum,
9 sum(amount) over (order by rn) - amount prev_sum
10 from numbers
11 )
12 select least(amount, 30335-prev_sum) amount
13 from rolling_sum
14 where rolling_sum <= 30335 or prev_sum < 30335
15 /
AMOUNT
----------
12457
10041
7837
----------
30335
3 rows selected.
SQL> /
AMOUNT
----------
13828
13387
3120
----------
30335
3 rows selected.
SQL> /
AMOUNT
----------
12349
11442
6544
----------
30335
3 rows selected.
SQL> /
AMOUNT
----------
10246
11785
8304
----------
30335
3 rows selected.
Regards
Michel
[Edit: improve the post including the sum]
[Updated on: Sat, 02 February 2013 03:06] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun May 26 03:27:10 CDT 2013
Total time taken to generate the page: 0.12975 seconds
|
] 
Members
Search
Help
Register
Login
Home
