Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 10G, Windows 2003)
Query Help [message #576198] Fri, 01 February 2013 05:16 Go to next message
mmohsinaziz
Messages: 85
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 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
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 #576211 is a reply to message #576202] Fri, 01 February 2013 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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      from numbers
 10    )
 11  select amount
 12  from rolling_sum
 13  where rolling_sum <= 30335
 14  /
    AMOUNT
----------
     14068
     12114

2 rows selected.

SQL> /
    AMOUNT
----------
     14307
     10431

2 rows selected.

SQL> /
    AMOUNT
----------
     12382
     12261

2 rows selected.

SQL> /
    AMOUNT
----------
     12860
     13623

2 rows selected.

Regards
Michel
Re: Query Help [message #576248 is a reply to message #576211] Fri, 01 February 2013 22:24 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Thanks Michel.

Please help me to resolve my problem also.
Re: Query Help [message #576249 is a reply to message #576248] Fri, 01 February 2013 22:36 Go to previous messageGo to next message
mmohsinaziz
Messages: 85
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
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

Re: Query Help [message #576268 is a reply to message #576258] Sat, 02 February 2013 02:31 Go to previous message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Thanks Michel.
Previous Topic: Tables function doubt
Next Topic: Upper function for long type
Goto Forum:
  


Current Time: Fri Aug 22 21:08:11 CDT 2014

Total time taken to generate the page: 0.09029 seconds