Home » SQL & PL/SQL » SQL & PL/SQL » How can one make a Distinct Multiplication Table ? (DB 10gR2 , Win XP SP2)
How can one make a Distinct Multiplication Table ? [message #349422] Sun, 21 September 2008 13:38 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

This Code make Multiplication Table :-

BEGIN
FOR I IN 1.. 12 LOOP
DBMS_OUTPUT.PUT_LINE('---------------------['
||I
||']----------------------');

FOR J IN 1.. 12 LOOP
DBMS_OUTPUT.PUT_LINE(I
||'*'
||J
||'='
||I * J);
END LOOP;
END LOOP;
END;
/


Quote:
---------------------[1]----------------------
1*1=1
1*2=2
1*3=3
1*4=4
1*5=5
1*6=6
1*7=7
1*8=8
1*9=9
1*10=10
1*11=11
1*12=12
---------------------[2]----------------------
2*1=2
2*2=4
2*3=6
2*4=8
2*5=10
2*6=12
.................. ......................
.................. .......................
11*7=77
11*8=88
11*9=99
11*10=110
11*11=121
11*12=132
--------------------- [12] ----------------------
12*1=12
12*2=24
12*3=36
12*4=48
12*5=60
12*6=72
12*7=84
12*8=96
12*9=108
12*10=120
12*11=132
12*12=144

PL/SQL procedure successfully completed.

=====================

that's good , but I want to change my code to perform a distinct results .
by accurate meaning , if this result happen ( 1*12=12) than I don't it repeat
again like (12*1=12) or (2*6=12) .... ect.

is there any suggest ?!
Re: How can one make a Distinct Multiplication Table ? [message #349424 is a reply to message #349422] Sun, 21 September 2008 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is no more a table of multiplication.
Easy.
Firs step: just always put the coefficient in ascendant order (J always >= to I).
Second step: ... Any number N is the result of 1*N
Done!

Regards
Michel

[Updated on: Sun, 21 September 2008 13:47]

Report message to a moderator

Re: How can one make a Distinct Multiplication Table ? [message #349433 is a reply to message #349422] Sun, 21 September 2008 15:47 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

thanks Michel for your replay.

I did as in the attachment.
  • Attachment: calc.txt
    (Size: 13.98KB, Downloaded 103 times)
Re: How can one make a Distinct Multiplication Table ? [message #349435 is a reply to message #349433] Sun, 21 September 2008 15:57 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
It has been said that you learn something new everyday; that
is an understatement when one reads from this site on a daily basis.

Not only is this exercise output something for me to show my friend's nephew who we have been helping in his multiplication tables; but my new knowledge was found in

 if not results.exists(i * j)


Far reaching benefit for myself in some existing processes working heavily off of parsing result collections.

Looking up now at what version <table-type-array>.exists came into play (hoping 9i!)

Thanx for this post!
Harry
Re: How can one make a Distinct Multiplication Table ? [message #349460 is a reply to message #349422] Sun, 21 September 2008 23:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We can do the same using the single sql query also . Something like ..

SQL> SELECT I||'*'||J||'='||K DISTINCT_MULTIPLICATION FROM (
  2  select X.i I , Y.j J  ,X.i * Y.j k ,
  3      Row_number( ) OVER (PARTITION BY X.i * Y.j Order by  X.i ,Y.j ) rn from
  4  (select level i from dual connect by level<13 ) x ,
  5  (select level  j from dual connect by level<13 ) y)
  6  Where rn =1
  7  order by i ,j;

DISTINCT_MULTIPLICATION
------------------------- 

1*1=1
1*2=2
1*3=3
1*4=4
1*5=5
1*6=6
1*7=7
1*8=8
1*9=9
1*10=10
1*11=11
1*12=12
2*7=14
2*8=16
2*9=18
2*10=20
2*11=22
2*12=24
3*5=15
3*7=21
3*9=27
3*10=30
3*11=33
3*12=36
4*7=28
4*8=32
4*10=40
4*11=44
4*12=48
5*5=25
5*7=35
5*9=45
5*10=50
5*11=55
5*12=60
6*7=42
6*9=54
6*11=66
6*12=72
7*7=49
7*8=56
7*9=63
7*10=70
7*11=77
7*12=84
8*8=64
8*10=80
8*11=88
8*12=96
9*9=81
9*10=90
9*11=99
9*12=108
10*10=100
10*11=110
10*12=120
11*11=121
11*12=132
12*12=144

59 rows selected.

SQL>


But has to do some more exercise to get the exact output of OP.

Thumbs Up
Rajuvan
icon14.gif  Re: How can one make a Distinct Multiplication Table ? [message #354155 is a reply to message #349460] Thu, 16 October 2008 12:28 Go to previous message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Thumbs Up thanks rajavu1 ..... it's also working good with me.
Previous Topic: Multiple Row insert into a single insert query
Next Topic: Error in decode function?
Goto Forum:
  


Current Time: Sat Dec 10 01:12:18 CST 2016

Total time taken to generate the page: 0.11257 seconds