## Forum Stats

• 3,750,522 Users
• 2,250,187 Discussions

Discussions

# Pascal's triangle or binomial coefficients

<font color="red">you know Pascal's triangle like below.</font>

1
11
121
1331
14641
...
...

my question is to find out a query giving nth sequence,
that is to say,

when n=1, that query yields
1

when n=2
1
1

when n=3
1
2
1

when n=4
1
3
3
1

...
...

(oh, but you have NOT to use any subqueries)

for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
«13

• If you dont want subqueries, you can try in PL/SQL.

Assuming your biggest length of the charector is 6, If the length is more you can copy and paste more ElsIf's:
```DECLARE
lv_text   VARCHAR2(50);
lv_len    NUMBER;
CURSOR c1 IS
SELECT text, length(text)
FROM table;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO lv_text;
EXIT WHEN c1%NOTFOUND;
IF nvl(lv_length) = 1 THEN
dbms_output.put_line (lv_text);
ELSIF
nvl(lv_length) = 2 THEN
FOR i IN 1..2 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 3 THEN
FOR i IN 1..3 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 4 THEN
FOR i IN 1..4 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 5 THEN
FOR i IN 1..5 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 6 THEN
FOR i IN 1..6 LOOP
dbms_output.put_line (substr(lv_text, i, i));
END IF;
END LOOP;
CLOSE c1;
END;
/```
Not tested !

HTH
Ghulam
• Are there restrictions on creating functions?
• FOR i IN 1..2 LOOP
dbms_output.put_line (substr(lv_text, i, i));
END LOOP;

Sorry you have to add END LOOP; at the end of all For Loops.

HTH
Ghulam
• sorry, but i want it in one SQL , not PL/SQL

a variable n is given.
no subqueries allowed.

* this is not a sort of exam.
• and the result is n rows....

when n=1
1 (one row)

when n=2
1
1 (two rows)

where n=3
1
2
1 (three rows)
...
...
• * this is not a sort of exam.
Do you have a solution and you're just testing us, or is this a kite flying exercise to see whether its possible?

I imagine it might be possible to do this with the Model clause but not having 10g to try it on I can't explore this theory at the moment.

Cheers, APC
• I recall the triangle starting at row 0 (perhaps that is a matter of choice)...
```Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> VARIABLE n NUMBER;
SQL> EXEC :n := 0;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1

SQL> EXEC :n := 1;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
1

SQL> EXEC :n := 2;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
2
1

SQL> EXEC :n := 3;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
3
3
1

SQL> EXEC :n := 4;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
4
6
4
1

SQL> EXEC :n := 5;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
5
10
10
5
1

6 rows selected.

SQL> EXEC :n := 6;

PL/SQL procedure successfully completed.

SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
2            EXP (SUM (LN (GREATEST (LEVEL - 1, 1)))
3               OVER (ORDER BY LEVEL)) *
4            EXP (SUM (LN (GREATEST (:n - (LEVEL - 1), 1)))
5               OVER (ORDER BY LEVEL DESC))) n
6  FROM   dual
7  CONNECT BY LEVEL <= CEIL (:n + 1);

N
----------
1
6
15
20
15
6
1

7 rows selected.

SQL> ```
• by the way, how are you going to generate multiple rows in plain SQL?
```def n=5

with fac as (select rownum n,exp(sum(ln(rownum))over(order by rownum)) fac from all_objects where rownum<12)
select nvl(n.fac/r.fac/nr.fac,1)
from (select rownum-1 r from all_objects where rownum<=&n+1),
fac n, fac r, fac nr
where n.n (+)=&n and r.n (+)=r and nr.n(+)=(&n-r)
order by r
/

1
5
10
10
5
1```
Message was edited by:
Laurent Schneider
• note that I consider CONNECT BY without PRIOR to be disallowed... as specified in the doc, CONNECT BY must contain a PRIOR operator defining the parent-child relationship... but this is just a small note of mines and feel free to use CONNECT BY if you like / trust it ;-)

Message was edited by:
Laurent Schneider
still padders method with analytics rules, because it does not use subquery
• Should this work for all values of :n?

When :n = 10...

N
0
10
45
120
210
252
210
120
45
10
0
This discussion has been closed.