Complex SQL Queries

1. Display Salary in Words.
    SELECT ename,
                   sal,
               TO_CHAR(to_date(sal,'jsp'),'jsp')"in words"
    FROM emp;

2. Find the 3rd MAX salary in the emp table.
    SELECT DISTINCT sal
    FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

3. Find the 3rd MIN salary in the emp table.
     SELECT DISTINCT sal
     FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 where e1.sal >= e2.sal);

4. Select FIRST n records from a table.
     SELECT * FROM emp WHERE rownum <= &n;

5. Select LAST n records from a table.
     SELECT * FROM emp
     MINUS
    SELECT * FROM emp WHERE rownum <=
   (SELECT COUNT(*) - &n FROM emp);

6. How to get 3 Max salaries?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
   (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal <= b.sal  )
   ORDER BY a.sal DESC;

7. How to get 3 Min salaries ?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

8. How to get nth max salaries?
   SELECT DISTINCT hire_date
   FROM emp a
   WHERE
   &n =
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal );

Query to Get the Account Flex field Structure

SELECT DISTINCT sob.name Ledger_Name ,
  sob.set_of_books_id,
  sob.chart_of_accounts_id coa_id ,
  fifst.id_flex_structure_name struct_name ,
  ifs.segment_name ,
  ifs.application_column_name column_name ,
  sav1.attribute_value BALANCING ,
  sav2.attribute_value COST_CENTER ,
  sav3.attribute_value NATURAL_ACCOUNT ,
  sav4.attribute_value INTERCOMPANY ,
  sav5.attribute_value SECONDARY_TRACKING ,
  sav6.attribute_value GLOBAL ,
  ffvs.flex_value_set_name ,
  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs ,
  fnd_id_flex_structures_tl fifst ,
  fnd_segment_attribute_values sav1 ,
  fnd_segment_attribute_values sav2 ,
  fnd_segment_attribute_values sav3 ,
  fnd_segment_attribute_values sav4 ,
  fnd_segment_attribute_values sav5 ,
  fnd_segment_attribute_values sav6 ,
  fnd_id_flex_segments ifs ,
  fnd_flex_value_sets ffvs ,
  gl_sets_of_books sob
WHERE 1                          =1
AND fifs.id_flex_code            = 'GL#'
AND fifs.application_id          = fifst.application_id
AND fifs.id_flex_code            = fifst.id_flex_code
AND fifs.id_flex_num             = fifst.id_flex_num
AND fifs.application_id          = ifs.application_id
AND fifs.id_flex_code            = ifs.id_flex_code
AND fifs.id_flex_num             = ifs.id_flex_num
AND sav1.application_id          = ifs.application_id
AND sav1.id_flex_code            = ifs.id_flex_code
AND sav1.id_flex_num             = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id          = ifs.application_id
AND sav2.id_flex_code            = ifs.id_flex_code
AND sav2.id_flex_num             = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id          = ifs.application_id
AND sav3.id_flex_code            = ifs.id_flex_code
AND sav3.id_flex_num             = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id          = ifs.application_id
AND sav4.id_flex_code            = ifs.id_flex_code
AND sav4.id_flex_num             = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id          = ifs.application_id
AND sav5.id_flex_code            = ifs.id_flex_code
AND sav5.id_flex_num             = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id          = ifs.application_id
AND sav6.id_flex_code            = ifs.id_flex_code
AND sav6.id_flex_num             = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type  = 'GL_BALANCING'
AND sav2.segment_attribute_type  = 'FA_COST_CTR'
AND sav3.segment_attribute_type  = 'GL_ACCOUNT'
AND sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type  = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type  = 'GL_GLOBAL'
AND ifs.id_flex_num              = sob.chart_of_accounts_id
AND ifs.flex_value_set_id        = ffvs.flex_value_set_id;

Query to get details of all Concurrent programs used by a Request Set

SELECT rs.user_request_set_name ,
  rs.request_set_name,
  rs.description ,
  rss.display_sequence Seq,
  cp.user_concurrent_program_name ,
  ap.application_name ,
  e.executable_name ,
  e.execution_file_name ,
  lv. meaning
FROM apps.fnd_request_sets_vl rs,
  apps.fnd_req_set_stages_form_v rss,
  applsys.fnd_request_set_programs rsp,
  apps.fnd_concurrent_programs_vl cp,
  apps.fnd_executables e,
  apps.fnd_lookup_values lv,
  apps.fnd_application_vl ap
WHERE rs.application_id          = rss.set_application_id
AND rs.request_set_id            = rss.request_set_id
AND rss.set_application_id       = rsp.set_application_id
AND rss.request_set_id           = rsp.request_set_id
AND rss.request_set_stage_id     = rsp.request_set_stage_id
AND rsp.program_application_id   = cp.application_id
AND rsp.concurrent_program_id    = cp.concurrent_program_id
AND cp.executable_id             = e.executable_id
AND cp.executable_application_id = e.application_id
AND e.application_id             =ap.application_id
AND lv.lookup_code = e.execution_method_code
AND lv.language         ='US'
AND ap.application_name = 'Order Management';

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt ,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl ,
  fnd_descr_flex_col_usage_vl fdfcuv ,
  fnd_application_vl fav
WHERE fcp.concurrent_program_id       = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id                =fcp.application_id
AND fcpl.language                     = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name;