Home » SQL & PL/SQL » SQL & PL/SQL » SQL for Tables (Oracle 11g R2)
SQL for Tables [message #653079] Mon, 27 June 2016 06:11 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Hi,

We have grown up by mugging up Multiplication Tables. Trying to do the same in SQL.
Create a Multiplication Table in Following Format. Don't want to use PLSQL.
User Inputs : A, B are user parameter inputs.
-----------------------------------------------------------------------------------
A * B = C
Example: A = 1
B=3
Output: 
1 * 1 = 1
1* 2 = 2
1* 3 = 3


Javed

[Updated on: Mon, 27 June 2016 06:12]

Report message to a moderator

Re: SQL for Tables [message #653081 is a reply to message #653079] Mon, 27 June 2016 06:23 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Is this a college homework question?

What SQL have you tried so far? I would start by using CONNECT BY LEVEL to generate some rows:
orclz>
orclz> select rownum from dual connect by level <=12;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12

12 rows selected.

orclz>
Re: SQL for Tables [message #653082 is a reply to message #653081] Mon, 27 June 2016 06:43 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Thanks, This is count . Wanted table of the number where i am taking two inputs first number and second number like if its A=2 and B=5 the result will be
2 * 1= 2
2 * 2= 4
2 * 3= 6
2 * 4= 8
2 * 5= 10

Hope i am clear.
Re: SQL for Tables [message #653083 is a reply to message #653082] Mon, 27 June 2016 06:43 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Quote:
What SQL have you tried so far?
Hope I am clear.
Re: SQL for Tables [message #653084 is a reply to message #653082] Mon, 27 June 2016 06:46 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Whatever i tried its all PL Blocks. But trying to get this done in SQL.

Javed
Re: SQL for Tables [message #653085 is a reply to message #653084] Mon, 27 June 2016 06:49 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
I've already shown you how to generate values that you can use for one operand. You should be able to generate the other operand, and multiply them together.
Re: SQL for Tables [message #653086 is a reply to message #653084] Mon, 27 June 2016 06:52 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Sorry forgot to post what i have tried already here it is ...

declare
n number(5);
i number(2);
m number(5);
limit number(5);
begin
i:=1;
n:=&n;  -- The number for which multiplication table should be printed.
limit:=&l;  -- This specifies the limit of the table.
dbms_output.put_line('Multiplication table for '|| n);
for i in 1..limit
loop
m:=n*i;
dbms_output.put_line(n||'*'||i||'='||m);
end loop;
end;

Its not college project Smile Something i am trying to write , not urgent , not required too. I am trying.

Javed A. Khan
Re: SQL for Tables [message #653087 is a reply to message #653086] Mon, 27 June 2016 06:58 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Okay i got it , Sorry to ask this lame question. Sometime i just want to check who's awake Smile

select :myval  ||'*'|| rownum ||'='|| rownum*:A from dual connect by level <=:a

[Updated on: Mon, 27 June 2016 08:13] by Moderator

Report message to a moderator

Re: SQL for Tables [message #653089 is a reply to message #653087] Mon, 27 June 2016 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Sometime i just want to check who's awake


Check again you are not: your query is not correct.

Re: SQL for Tables [message #653090 is a reply to message #653087] Mon, 27 June 2016 07:25 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
MC is right (as usual)! It doesn't quite work:
orclz>
orclz> var myval number
orclz> var A number
orclz> exec :myval:=5

PL/SQL procedure successfully completed.

orclz> exec :a:=2

PL/SQL procedure successfully completed.

orclz> select :myval  ||'*'|| rownum ||'='|| rownum*:A from dual connect by level <=:myval;

:MYVAL||'*'||ROWNUM||'='||ROWNUM*:A
--------------------------------------------------------------------------------------------------------------
5*1=2
5*2=4
5*3=6
5*4=8
5*5=10

orclz>
A good thing it wasn't a college assignment Smile
Re: SQL for Tables [message #653091 is a reply to message #653090] Mon, 27 June 2016 08:13 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The code should be

select :myval  ||'*'|| rownum ||'='|| rownum*:myval from dual connect by level <=:myval;

[Updated on: Mon, 27 June 2016 08:14]

Report message to a moderator

Re: SQL for Tables [message #653107 is a reply to message #653091] Mon, 27 June 2016 21:01 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Okay tried again. It wasn't correct . you are right.

with aaa as ( select :x as x,:y as y  from dual) 
SELECT x||'*'||level || ' = ' ||round(exp(ln(x) + ln(level))) as output from aaa connect by level <= y;

Re: SQL for Tables [message #653108 is a reply to message #653107] Mon, 27 June 2016 21:01 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Or this

select 
:1 as A,
level as B,
(:1*(level)) AS AintoB from dual connect by level <= (:2) ;
Re: SQL for Tables [message #653109 is a reply to message #653108] Mon, 27 June 2016 21:04 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Thanks for the expert comments , I hope i dint bother you all here. Smile
Re: SQL for Tables [message #653110 is a reply to message #653087] Mon, 27 June 2016 21:04 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

I meant this

SELECT     :myval || '*' || ROWNUM || '=' || ROWNUM * :myval
      FROM DUAL
CONNECT BY LEVEL <= :myval;
Re: SQL for Tables [message #653111 is a reply to message #653110] Mon, 27 June 2016 21:11 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
javed.khan wrote on Mon, 27 June 2016 19:04
I meant this

SELECT     :myval || '*' || ROWNUM || '=' || ROWNUM * :myval
      FROM DUAL
CONNECT BY LEVEL <= :myval;


SQL> SELECT     :myval || '*' || ROWNUM || '=' || ROWNUM * :myval
      FROM DUAL
CONNECT BY LEVEL <= :myval;  2    3  
SP2-0552: Bind variable "MYVAL" not declared.
SQL> 

Consider to actually test code before posting here.
Previous Topic: Help Creating a Trigger for Reoccuring Events
Next Topic: low performance of CONNECT BY
Goto Forum:
  


Current Time: Thu Apr 25 07:43:11 CDT 2024