Home » SQL & PL/SQL » SQL & PL/SQL » How can we do it
How can we do it [message #378591] |
Wed, 31 December 2008 01:03  |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Dear All
Select 12/4 from dual.
Above query return 3. I want to display 'P' three times like 'PPP'. How can i do it.
Regards
Asif.
|
|
|
|
|
|
|
|
|
|
|
|
Re: How can we do it [message #379407 is a reply to message #379377] |
Tue, 06 January 2009 06:16   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
PL/SQL is overkill when the same can be achieved by pure SQL. Moreover, your very specific code does "something" only when the result is 3 or 4; in any other case, it does nothing.
SQL> exec repeat_p(9, 3);
Welcome
9/3,3,PPP
PL/SQL procedure successfully completed.
SQL> exec repeat_p(36, 9);
Welcome
36/9,4,PPPP
PL/SQL procedure successfully completed.
SQL> exec repeat_p(15, 3); --> no result
Welcome
PL/SQL procedure successfully completed.
SQL> exec repeat_p(28, 4); --> no result
Welcome
PL/SQL procedure successfully completed.
SQL>
This is a more generic code:SQL> SELECT &&A ||'/'|| &&B what,
2 &&A / &&B how_much,
3 MAX(RPAD ('P', LEVEL, 'P')) result
4 FROM dual
5 CONNECT BY LEVEL <= (SELECT &&A/&&B FROM dual);
Enter value for a: 9
Enter value for b: 3
WHA HOW_MUCH RESULT
--- ---------- ------------------------------
9/3 3 PPP
SQL> undefine a
SQL> undefine b
SQL> /
Enter value for a: 36
Enter value for b: 9
WHAT HOW_MUCH RESULT
---- ---------- ------------------------------
36/9 4 PPPP
SQL> undefine a
SQL> undefine b
SQL> /
Enter value for a: 15
Enter value for b: 3
WHAT HOW_MUCH RESULT
---- ---------- ------------------------------
15/3 5 PPPPP
SQL> undefine a
SQL> undefine b
SQL> /
Enter value for a: 28
Enter value for b: 4
WHAT HOW_MUCH RESULT
---- ---------- ------------------------------
28/4 7 PPPPPPP
SQL>
|
|
|
Re: How can we do it [message #379420 is a reply to message #379407] |
Tue, 06 January 2009 06:56   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I don't understand the purpose of connect by and max, or is it just a joke?
SQL> with data as
2 ( select 9 a, 3 b from dual
3 union all
4 select 36, 9 from dual
5 union all
6 select 15, 3 from dual
7 union all
8 select 28, 4 from dual
9 )
10 select a||'/'||b what, a/b how_much, rpad('P',a/b,'P') result
11 from data
12 /
WHAT HOW_MUCH RESULT
----- ---------- ----------
9/3 3 PPP
36/9 4 PPPP
15/3 5 PPPPP
28/4 7 PPPPPPP
4 rows selected.
Regards
Michel
[Updated on: Tue, 06 January 2009 06:56] Report message to a moderator
|
|
|
Re: How can we do it [message #379477 is a reply to message #379420] |
Tue, 06 January 2009 12:51   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Michel | I don't understand the purpose of connect by and max, or is it just a joke?
|
If you don't mind, I won't answer it respecting your word order.
No, it is just another way of providing the result. Someone does it using PL/SQL, you do it your way, I did it my way (which, of course, doesn't necessarily have to be optimal, but shows yet another possibility).
Michel | I don't understand the purpose of connect by ...
|
Well, as I used LEVEL pseudocolumn, I had to use CONNECT BY as well. Otherwise, I'd getSQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block
This is how query result looks like without the MAX function:SQL> SELECT &&A ||'/'|| &&B what,
2 &&A / &&B how_much,
3 RPAD ('P', LEVEL, 'P') result
4 FROM dual
5 CONNECT BY LEVEL <= (SELECT &&A/&&B FROM dual);
Enter value for a: 9
Enter value for b: 3
WHA HOW_MUCH RESULT
--- ---------- ------------------------------
9/3 3 P
9/3 3 PP
9/3 3 PPP
SQL> With MAX, it is justThat's why MAX.
Though, I hope you had some great time laughing of my query. Making people smile may be a difficult task these days.
|
|
|
Re: How can we do it [message #379480 is a reply to message #379477] |
Tue, 06 January 2009 13:19   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
"connect by and max" was a whole, I perfectly understood you need "max" if you use "connect by".
Quote: | I hope you had some great time laughing of my query.
|
Not at all, given your other answers here, I just thought you wanted to joke giving an oversophisticated solution.
As you can see from your last query (if you add level column) the result is for level=&&A/&&B and so you don't need the other generated rows (that you eliminate with "max"). So in short, you generate many rows and in the end eliminate them, I just thought you did it by joke, I didn't want to make a fool of you, surely not, I never ever had the single bit of desire of derision about you. I'm very sorry if my post offends you.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 19:06:19 CST 2025
|