Home » SQL & PL/SQL » SQL & PL/SQL » Connect by help 2
icon2.gif  Connect by help 2 [message #213041] Tue, 09 January 2007 03:41 Go to next message
harpreetsinghkup
Messages: 52
Registered: May 2006
Location: Mumbai
Member

Respected Experts,
i have a query for all of you.
based on most popular table
i am using oracle 9i rel 2

select level,substr(lpad(' ',(level-1)*4,' ')||ename,1,30) ename
,sal,substr(sys_connect_by_path(sal,'.'),1,50) sal_path
from emp
start with mgr is null
connect by prior empno=mgr;


This query is return me values just like as a uploaded file.
but i want a result form the query as my expected_sal column

For more information please see uploaded file..
please help me for this.
Thanks in advance.

  • Attachment: File.txt.txt
    (Size: 2.17KB, Downloaded 173 times)
Re: Connect by help 2 [message #213043 is a reply to message #213041] Tue, 09 January 2007 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that all that your attachment contains is this text:
SQL>  1   select level,substr(lpad('   ',(level-1)*2,' ')||ename,1,15) ename
  2   ,sal,substr(sys_connect_by_path(sal,'.'),1,50) path
  3    from emp
  4   start with mgr is null
  5*  connect by prior empno=mgr
SQL> /

     LEVEL ENAME                  SAL                 PATH         EXP_SAL                               
---------- --------------- ------------------------------------------------                                           
         1 KING                  5000                 *5000           5000                        
         2   JONES               2975            *5000*2975       14875000                                
         3     SCOTT             3000       *5000*2975*3000    44625000000                                     
         4       ADAMS           1100  *5000*2975*3000*1100       --------                                  
         3     FORD              3000       *5000*2975*3000       --------                                    
         4       SMITH            800   *5000*2975*3000*800       --------                                 
         2   BLAKE               2850            *5000*2850       14250000                         
         3     ALLEN             1600       *5000*2850*1600       --------                              
         3     WARD              1250       *5000*2850*1250       --------                                        
         3     MARTIN            1250       *5000*2850*1250       --------                             
         3     TURNER            1500       *5000*2850*1500       --------                             
         3     JAMES              950        *5000*2850*950       -------                            
         2   CLARK               2450            *5000*2450       12250000                              
         3     MILLER            1300       *5000*2450*1300       --------




now I want that 1 record <EXP_SAL> as 5000           =       5000 
now I want that 2 record <EXP_SAL> as 5000*2975      =   14875000 
now I want that 3 record <EXP_SAL> as 5000*2975*3000 =44625000000    
              ----------------SO ON--------------                
WHY did you insist on putting it in an attachment?
Re: Connect by help 2 [message #213044 is a reply to message #213043] Tue, 09 January 2007 03:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't think of a way of doing this in SQL (which is usually a signal for someone to come up with a simple and efficient way of doing it), so I created a function
CREATE OR REPLACE FUNCTION calculate (p_string in varchar2) 
RETURN number AS
  v_string    varchar2(4000);
  v_return    number;
BEGIN
  v_String := ltrim(p_String,'*');
  execute immediate 'BEGIN :out := '||v_string||'; END;' 
          using out v_return;
          
  return v_return;
END calculate;
that you can call from SQL to do the job for you:
SQL> select calculate('*50*12*100') from dual;

CALCULATE('*50*12*100')
-----------------------
                  60000

Re: Connect by help 2 [message #213225 is a reply to message #213044] Tue, 09 January 2007 19:55 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try this:
select level,substr(lpad('   ',(level-1)*2,' ')||ename,1,15) ename
,sal
,substr(sys_connect_by_path(sal,'.'),1,50) path
,(   SELECT trunc(exp(sum(ln(sal))))
     FROM   emp
     START WITH empno = e1.empno
     CONNECT BY prior mgr = empno
) AS exp_sal
from emp e1
start with mgr is null
connect by prior empno=mgr


It's based on @zozogirl's product aggregate solution here, which will help if you have any zero or negative numbers.

Ross Leishman
Previous Topic: How can generate dump into XML format?
Next Topic: zero divisior
Goto Forum:
  


Current Time: Mon Dec 05 12:51:58 CST 2016

Total time taken to generate the page: 0.09798 seconds