Back

Samples

Source select

 

/*! VAR :Max_salary_dep number {select department_id

from (select ee.department_id,

sum(ee.salary)

                                          from employee ee

                   /*! IS_NOT_NULL :emp_id {where ee.employee_id = :emp_id}; !*/

group  by ee.department_id

                                         order by sum(ee.salary) desc)

where rownum = 1} !*/

   /*! VAR :Debug_print char    'Y' ; !*/

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

         j.function,e.hire_date,e.salary,e.commission

 /*! IS_NOT_NULL :loc_id {,l.regional_group}; !*/

   from department d,employee e,job j

 /*! IS_NOT_NULL :loc_id {,loc l}; !*/

  where e.department_id=d.department_id

    and e.job_id=j.job_id

 /*! IS_NOT_NULL :loc_id { and l.loc_id=d.loc_id}; !*/

 /*! IS_NOT_NULL :hire_date { and hire_date >= :hire_date}; !*/

 /*! IS_NOT_NULL :function { and j.function=upper(:function)}; !*/

 /*! IIF_EXPR {:dep_id is not null} {and d.department_id = :dep_id}

{and d.department_id = :Max_salary_dep}; !*/

 /*! IS_NOT_NULL :emp_id {and employee_id = :emp_id}; !*/

 /*! ROW_SUM {Total row} F G; !*/

 /*! BOTTOM_SUM Total F G /*! IS_NOT_NULL :loc_id I ; !*/

 /*! IS_NULL :loc_id H; !*/ ; !*/


Procedure parameters

<PARAMS_LIST>

    <DIR_NAME>/RM</DIR_NAME>

    <FILE_NAME>scott</FILE_NAME>

    <EXCEL_TITLE>Emploee List</EXCEL_TITLE>

    <LIMIT_ROWS> 20000</LIMIT_ROWS>

    <SUBTITLE>With departments details</SUBTITLE>

    <DIRECTION>ltr</DIRECTION>

    <CHARSET></CHARSET>

    <PARAM_TITLE>Parameters </PARAM_TITLE>

    <PAR_NAME_HEAD>Name </PAR_NAME_HEAD>

<PAR_VALUE_HEAD>Value</PAR_VALUE_HEAD>

    <NOT_FOUND_MSG> Data not found</NOT_FOUND_MSG>

    <CURR_DATE_PROMPT>Current date </CURR_DATE_PROMPT>

    <DEFAULT_DATE_MASK> </DEFAULT_DATE_MASK>

<OUTPUT_DATE_FORMAT></OUTPUT_DATE_FORMAT>

    <CURRENT_SHEET></CURRENT_SHEET>
   <TOTAL_SHEETS></TOTAL_SHEETS>
   <PARAMS>

      <PARAM>

        <NAME>emp_id</NAME>

        <DATATYPE>number</DATATYPE>

        <FORMAT_MASK> </FORMAT_MASK>

        <PROMPT>Emploee Id</PROMPT>

        <LABEL></ LABEL >

        <VALUE></VALUE>

       </PARAM>

      <PARAM>

        <NAME>loc_id</NAME>

        <DATATYPE>number</DATATYPE>

        <FORMAT_MASK></FORMAT_MASK>

        <PROMPT>Location</PROMPT>

        <LABEL></ LABEL >

        <VALUE>123 </VALUE>

      </PARAM>

      <PARAM>

        <NAME>dep_id</NAME>

        <DATATYPE>number</DATATYPE>

        <FORMAT_MASK></FORMAT_MASK>

        <PROMPT>Department Id</PROMPT>

        <LABEL></ LABEL >

        <VALUE>  30  </VALUE>

      </PARAM>

      <PARAM>

        <NAME>FUNCTION</NAME>

        <DATATYPE>char</DATATYPE>

        <FORMAT_MASK></FORMAT_MASK>

<PROMPT>Job<br>name</PROMPT>

        <LABEL></ LABEL >

        <VALUE>SALESPERSON</VALUE>

      </PARAM>

      <PARAM>

        <NAME>hire_date</NAME>

        <DATATYPE>date</DATATYPE>

<FORMAT_MASK>yyyy-mm-dd</FORMAT_MASK>

        <PROMPT>Hire Date</PROMPT>

        <LABEL></ LABEL >

        <VALUE>1985-09-08</VALUE>

      </PARAM>

    </PARAMS>

  </PARAMS_LIST>

 

 

Parameters

Name

Value

Emploee Id

 

Location

 

Department Id

 

Job<BR>name

 

Hire Date

 

 

 

!*************Result Select*************!

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

   from department d,employee e,job j

  where e.department_id=d.department_id

    and e.job_id=j.job_id

and d.department_id = 20

!*************Result Select*************!

 

 

 

Parameters

Name

Value

Emploee Id

 

Location

123

Department Id

30

Job<BR>name

SALESPERSON

Hire Date

1985-09-08

 

 

 

 

!*************Result Select*************!

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

         j.function,e.hire_date,e.salary,e.commission

  ,l.regional_group

   from department d,employee e,job j

  ,loc l

  where e.department_id=d.department_id

    and e.job_id=j.job_id

  and l.loc_id=d.loc_id

  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

  and j.function=upper('SALESPERSON')

 and d.department_id = 30

!*************Result Select*************!

 


Parameters

Name

Value

Emploee Id

 

Location

 

Department Id

30

Job<BR>name

SALESPERSON

Hire Date

1985-09-08

 

!*************Result Select*************!

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

   from department d,employee e,job j

  where e.department_id=d.department_id

    and e.job_id=j.job_id

  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

  and j.function=upper('SALESPERSON')

 and d.department_id = 30

!*************Result Select*************!

 

 

Parameters

Name

Value

Emploee Id

 

Location

 

Department Id

 

Job<BR>name

SALESPERSON

Hire Date

1985-09-08

 

!*************Result Select*************!  

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

   from department d,employee e,job j

  where e.department_id=d.department_id

    and e.job_id=j.job_id

  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

  and j.function=upper('SALESPERSON')

 and d.department_id = 20

!*************Result Select*************!

 

 

 

 

Parameters

Name

Value

Emploee Id

 

Location

 

Department Id

 

Job<BR>name

 

Hire Date

1985-09-08

 

!*************Result Select*************!

  select e.first_name "First <br>Name",

         e.last_name,

         d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

   from department d,employee e,job j

  where e.department_id=d.department_id

    and e.job_id=j.job_id

  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

and d.department_id = 20

!*************Result Select*************!