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 Go to next message
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: please help [message #378595 is a reply to message #378591] Wed, 31 December 2008 01:10 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The simplest way is by far
select 'PPP' from dual;

Another way could be
select max(rpad ('P', level, 'P')) result
from dual
connect by level <= (select 12/4 from dual);
How can we do it. [message #378607 is a reply to message #378591] Wed, 31 December 2008 01:40 Go to previous messageGo to next message
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
Senior Member

Dear All

Select 12/4 from dual

above query return 3. Now i want to display 'P' three time.

I want result like follwoing

12/4,3,'PPP'
16/4,4,'PPPP'

how can we do it.

Advance Thanks.
Asif
Re: How can we do it. [message #378609 is a reply to message #378607] Wed, 31 December 2008 01:46 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, do not multipost.

What exactly did you not like about the previous answer?
Re: How can we do it. [message #378610 is a reply to message #378607] Wed, 31 December 2008 01:48 Go to previous messageGo to next message
Frank Naude
Messages: 4593
Registered: April 1998
Senior Member
select LPAD('P', 4, 'P') FROM dual
Re: How can we do it. [message #378611 is a reply to message #378607] Wed, 31 December 2008 01:49 Go to previous messageGo to next message
anasjamil
Messages: 31
Registered: April 2008
Location: Sydney
Member

hi,

Use LPAD,RPAD FUNCTIONS WITH GROUP BY IN QUERY.

HOWEVER I HAVE NOT TRIED IT BUT HOPE IT WILL HELP.

regards,
anas jamil
Re: How can we do it. [message #378612 is a reply to message #378611] Wed, 31 December 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
anasjamil wrote on Wed, 31 December 2008 08:49
hi,

Use LPAD,RPAD FUNCTIONS WITH GROUP BY IN QUERY.

HOWEVER I HAVE NOT TRIED IT BUT HOPE IT WILL HELP.

regards,
anas jamil

1/ Don't post in UPPER case
2/ If you don't know what you are saying it is better to not say it. Why GROUP BY? Where is there any aggregate here?

Regards
Michel

Re: How can we do it. [message #378614 is a reply to message #378612] Wed, 31 December 2008 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel
Why GROUP BY? Where is there any aggregate here?

Ps are aggregating into a group so, at the end, there's a group of 3 Ps: PPP /forum/fa/1587/0/
Re: How can we do it [message #378637 is a reply to message #378591] Wed, 31 December 2008 03:14 Go to previous messageGo to next message
anasjamil
Messages: 31
Registered: April 2008
Location: Sydney
Member

hi,

sorry for suggesting a group by in the solution.
actually i misunderstood the condition.
sorry once again.

regards
anas jamil
Re: How can we do it [message #379377 is a reply to message #378637] Tue, 06 January 2009 04:27 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi mamalik,

you can try this and enhance according to your requirements:

12/4,3,'PPP'
16/4,4,'PPPP'

see the attached file.

Ashu

[Updated on: Tue, 06 January 2009 04:30]

Report message to a moderator

Re: How can we do it [message #379407 is a reply to message #379377] Tue, 06 January 2009 06:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.

Michel
is it just a joke?

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 get
SQL> select level from dual;
select level from dual
                  *
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block


Michel
... and max

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 just
9/3          3 PPP
That'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 Go to previous messageGo to next message
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

Re: How can we do it [message #379492 is a reply to message #379480] Tue, 06 January 2009 14:58 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel
I'm very sorry if my post offends you.
Not at all! You have just made my life a little bit more complicated (after I made it complicated myself) figuring out how to write words of excuse for being that complicated /forum/fa/917/0/

Well, at the moment, those were my ideas ... sorry for creating too much mess here.
Previous Topic: create table
Next Topic: convert cursor into a select into statement.
Goto Forum:
  


Current Time: Mon Feb 17 19:06:19 CST 2025