Some important SQL Queries

To Display highest Nth salary of Employee:
--------------------------------------------------

SQL> select * from emp;



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                       
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                       
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                       
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                       
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                       
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                       
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                                                                                                                                                                       
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                       
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                       
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20                                                                                                                                                                                                                       
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                       
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                       
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                       
      1234 SHASHI     MANAGER         7839 10-JAN-13       4500        200         10                                                                                                                                                                                                                       

15 rows selected.

SQL> select * from emp A
  2  where 4=(select count(B.sal) from emp B where A.sal <= B.sal)
  3  order by sal;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                       
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                                                                                                                                                                       

SQL> select * from emp order by sal desc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                       
      1234 SHASHI     MANAGER         7839 10-JAN-13       4500        200         10                                                                                                                                                                                                                       
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                       
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                       
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                       
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                       
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                       
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                       
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                       
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                       
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                       
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20                                                                                                                                                                                                                       
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                       
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                       

15 rows selected.

SQL> select * from emp A
  2  where 5=(select count(B.sal) from emp B where A.sal <= B.sal)
  3  order by sal;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 

To display 4th row of a table:
----------------------------------

SQL> 
  1  Select * from emp where rownum<=4
  2  minus
  3* select * from emp where rownum<4 data-blogger-escaped-----------="" data-blogger-escaped----------="" data-blogger-escaped-02-apr-81="" data-blogger-escaped-20="" data-blogger-escaped-2975="" data-blogger-escaped-7566="" data-blogger-escaped-7839="" data-blogger-escaped-comm="" data-blogger-escaped-deptno="" data-blogger-escaped-empno="" data-blogger-escaped-ename="" data-blogger-escaped-hiredate="" data-blogger-escaped-job="" data-blogger-escaped-jones="" data-blogger-escaped-manager="" data-blogger-escaped-mgr="" data-blogger-escaped-sal="" data-blogger-escaped-sql=""> Select * from emp A
  2  where 4=(select count(rowid) from emp B where A.rowid>=B.rowid)
  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                       


To Display top N salaries in a employee table:
-----------------------------------------------------
Select * from (select * from emp order by sal desc )
  2  where rownum<10 data-blogger-escaped-br="">
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                       
      1234 SHASHI     MANAGER         7839 10-JAN-13       4500        200         10                                                                                                                                                                                                                       
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                                                                                                                                                                       
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                       
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                       
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                       
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                       
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                       

9 rows selected.

To Display Nth lowest salary of employee table:

-----------------------------------------------

SQL> select * from emp A
  2  Where 2=(Select count(sal) from emp B where A.sal>=B.sal)
  3  order by sal;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 

To display every 4th row in a table,suppose a table contains 21 rows,it has to display 4,8,12,16,20th rows ----------------------------------------------------------------------------------------------------------

SQL>Select * from emp where (rowid,0) in (select rowid,mod(rownum,4) from emp)

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                       
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                       
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20                                                                                                                                                                                                                       

To display alternates EVEN rows of a table. ------------------------------------------

SQL> select * from emp
   where (rowid,0) in (select rowid,mod(rownum,2) from emp)
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                       
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                       
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                       
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                       
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                       
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20                                                                                                                                                                                                                       
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                       

7 rows selected.

To display alternate ODD rows in a table:
-----------------------------------------

SQL>select * from emp
  2* where (rowid,1) in (select rowid,mod(rownum,2) from emp) 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                       
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                       
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                       
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                       
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                       
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                                                                                                                                                                       
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                       
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                       
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                       
      1234 SHASHI     MANAGER         7839 10-JAN-13       4500        200         10                                                                                                                                                                                                                       

8 rows selected.

Read More

PPR(Partital Page Rendering) in OAF.

Code in Controller(co):
==============

 public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    OAApplicationModule am=pageContext.getApplicationModule(webBean);
    
      am.invokeMethod("xxInitPPRRow");
      
      am.invokeMethod("xxHideFields");;
  }

  
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    
      OAApplicationModule am=pageContext.getApplicationModule(webBean);
      
        if("XX_HANDLE_UOM_EVENT".equalsIgnoreCase(pageContext.getParameter(EVENT_PARAM)))
        {
            am.invokeMethod("xxHandlePPR"); 
        }

}

Code in the Application Module(AM):
=======================

    public void xxInitPPRRow() 
      {
          PPRDemoVOImpl vo=getPPRDemoVO1();
        
          Row  row = vo.createRow();
          
          if(!vo.isPreparedForExecution())
          {
              vo.setMaxFetchSize(0);
          }
          vo.insertRow(row);
          
            row.setAttribute("Nou","Sample No of Units");          
            row.setAttribute("Wob","Sample Box");        
            row.setAttribute("Vol","Sample Volume");
            
          row.setNewRowState(Row.STATUS_INITIALIZED);
      }//End of InitPPRRRow Method..

       public void xxHandlePPR()
       {
          
                    PPRDemoVOImpl vo=getPPRDemoVO1();
                    PPRDemoVORowImpl row=(PPRDemoVORowImpl)vo.getCurrentRow();
                    
                                          
                          if(row.getUom()!=null && !"".equals(row.getUom()))
                          {
                                          String uomValue =row.getUom();
                                                                                   
                                           if(uomValue.equals("EA")) //Handle for Number of Units(NOU)
                                                {
                                                  row.setA1(true);
                                                  row.setA2(false);
                                                  row.setA3(false);
                                                  row.setA4("yes");
                                                                                         
                                               }
                                           else if(uomValue.equals("BOX"))  //Handle for weight of Box(WOB)
                                                { 
                                                   row.setA1(false);
                                                   row.setA2(true);
                                                   row.setA3(false);
                                                 }
                                          else //Handle for volume of gallon (VOG)
                                                {
                                                
                                                    row.setA1(false);
                                                    row.setA2(false);
                                                    row.setA3(true);
                                                }                   
                          }
                          else 
                          {
                                    this.xxHideFields();
                                    throw new OAException("Select  atlease one Value",OAException.ERROR);
                           }
          } //End of xxHadlePPR Method    
          


           public void xxHideFields() 
           {
                PPRDemoVOImpl vo=getPPRDemoVO1();
                PPRDemoVORowImpl row=(PPRDemoVORowImpl)vo.getCurrentRow();
                                         
               
               row.setA1(false);
               row.setA2(false);
               row.setA3(false);       
            }//End of xxHideFields method

Query in VO#1:PPRDemoVO:
===================

SELECT PPRDetailsEO.UOM, 
       PPRDetailsEO.NOU, 
       PPRDetailsEO.WOB, 
       PPRDetailsEO.VOL, 
       PPRDetailsEO.CREATED_BY, 
       PPRDetailsEO.CREATION_DATE, 
       PPRDetailsEO.LAST_UPDATED_BY, 
       PPRDetailsEO.LAST_UPDATE_DATE, 
       PPRDetailsEO.LAST_UPDATE_LOGIN, 
       PPRDetailsEO.ROWID
FROM XX_TEST_PPR PPRDetailsEO

Query in VO#2(Poplist VO):
==================

select LOOKUP_CODE,MEANING from fwk_tbx_lookup_codes_vl where lookup_type = 'FWK_TBX_UNIT_OF_MEASURE'

Read More

Page Creation in OAF - Supplier - Site Page.

 

Code in Controller
==============

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    OAApplicationModule am=pageContext.getApplicationModule(webBean);
    
    am.invokeMethod("CreateSupplierRow");
    
  }

 
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule am=pageContext.getApplicationModule(webBean);
      
    String event =pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM);
    String source =pageContext.getParameter(OAWebBeanConstants.SOURCE_PARAM);
            
    
        if(pageContext.getParameter("Apply")!=null)
           {
             am.invokeMethod("SaveData");
             
             
                //These five lines are to display the confirmation message..
             String supId =pageContext.getParameter("SuppNumber");
             String supName =pageContext.getParameter("SuppName");
            
             MessageToken[]   tokens = {new MessageToken("SUPID",supId),new MessageToken("SNAME",supName)};
             
             OAException  msg = new OAException("PO","SUPPLIER_SAVED_MSG",tokens,OAException.CONFIRMATION,null);
             throw msg;
           }

    if(ADD_ROWS_EVENT.equals(event))
   {
         am.invokeMethod("CreateSitesRow");
   }
    
  }

}

Code in the AM
==============

public void CreateSupplierRow()
    {
        SuppliersEOVOImpl vo=getSuppliersEOVO1();
        
        Row row=vo.createRow();
                
                if(!vo.isPreparedForExecution())
                {
                  vo.setMaxFetchSize(0);
                }
                vo.insertRow(row);
            
                //we need to import import oracle.jbo.domain.Number
                //Setting the sequence value to the Supplier ID attribute(field)
                Number seq =getOADBTransaction().getSequenceValue("fwk_tbx_suppliers_s");

                row.setAttribute("SupplierId",seq);
                
                row.setNewRowState(Row.STATUS_INITIALIZED);
        
    }
    
    
    public void CreateSitesRow() 
    {
        SitesEOVOImpl vo=getSitesEOVO1();
       
        SuppliersEOVOImpl vo1=getSuppliersEOVO1();
               
               Row siterow=vo.createRow();
               if(!vo.isPreparedForExecution())
               {
                   vo.setMaxFetchSize(0);   
               }
        int  count =vo.getRowCount();
        vo.insertRowAtRangeIndex(count,siterow);


        //Setting the sequence values in SupplierID,SuppliersiteID Attributes(fields).
        Number seq =getOADBTransaction().getSequenceValue("fwk_tbx_supplier_sites_s");
        String  mSupId =vo1.getCurrentRow().getAttribute("SupplierId").toString();
        
        siterow.setAttribute("SupplierId",mSupId);   //Setting the sequence value in SupplierID attribute
        siterow.setAttribute("SupplierSiteId",seq);  //Setting the sequence value in SupplierSiteId Attribute
        siterow.setAttribute("SerialNum",count+1);   //Setting the sequence value in SerialNum Transient Attribute.
        
       
        
        siterow.setNewRowState(Row.STATUS_INITIALIZED);
          
          
           
    }
    //Written by Shashi..
        public void SaveData()
         {
             this.getTransaction().commit();
         }

    }

Read More