TEST SQL

Sql code posted by NT
created at 17 Jun 10:02, updated at 21 Oct 04:41

Edit | Back 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
SELECT              
  S1.JOBOFFER_MANAGEMENT_NO,              
  S1.CONTRACT_GENERATION_NO,              
  S1.TRANSLATE_CORP_NAME_KNJ_ABB,              
  S1.CONTACT_TITLE,              
  S1.JOB_HEADING,              
  S1.GUARANTEE_TSTAMP,              
  S1.ICHIOSI_FLAG,              
  S1.FOLDER_TYPE_CODE,              
FROM              
  (SELECT T1.JOBOFFER_MANAGEMENT_NO,              
    T2.CONTRACT_GENERATION_NO,              
    T3.TRANSLATE_CORP_NAME_KNJ_ABB,              
    T4.CONTACT_TITLE,              
    T2.JOB_HEADING,              
    T1.READ_FLAG,              
    T1.ICHIOSI_FLAG,              
    T1.FOLDER_TYPE_CODE,              
    T6.GUARANTEE_TSTAMP,              
    NVL(T7.ASCERTAIN_RATIO,-1) AS ASCERTAIN_RATIO,              
    CASE              
      WHEN T2.JOBOFFER_1_APP_DATE <= TRUNC(SYSDATE-?)              
      THEN NVL(T8.SEND_COUNT,999)              
      ELSE 999              
    END SEND_COUNT,              
    T1.SEND_DATE,              
    T4.CO_CAT_CODE,              
    T3.CORP_NAME_KNA_COMPRESS              
  FROM T_PDT_RECOMMEND_JOBOFFER_POST T1,              
    T_JOBOFFER T2,              
    T_CORPORATE T3,              
    T_CONTACT T4,              
    T_CONTACT_INDUSTRY_TYPE T5,              
    T_INTERVIEW_COMMIT_OFFER T6,              
    T_JOBOFFER_VACANT T7,              
    T_JOBOFFER_STATUS T8,              
  WHERE T2.CORP_CODE               = T5.CORP_CODE(+)              
  AND T2.CONTACT_CODE              = T5.CONTACT_CODE(+)              
  AND T1.JOBOFFER_MANAGEMENT_NO    = T2.JOBOFFER_MANAGEMENT_NO              
  AND T2.CORP_CODE                 = T3.CORP_CODE(+)              
  AND T2.CORP_CODE                 = T4.CORP_CODE(+)              
  AND T2.CONTACT_CODE              = T4.CONTACT_CODE(+)              
  AND T1.JOBSEEKER_NO              = T6.JOBSEEKER_NO(+)              
  AND T1.JOBOFFER_MANAGEMENT_NO    = T6.JOBOFFER_MANAGEMENT_NO(+)              
  AND T6.GUARANTEE_TSTAMP(+)      >= SYSDATE              
  AND T6.PDT_DISPLAY_FLAG (+)      = '1'              
  AND T2.JOBOFFER_MANAGEMENT_NO    = T7.JOBOFFER_MANAGEMENT_NO(+)              
  AND T2.CONTRACT_GENERATION_NO    = T7.CONTRACT_GENERATION_NO(+)              
  AND T7.ASCERTAIN_RATIO(+)       >= ?              
  AND T2.JOBOFFER_MANAGEMENT_NO    = T8.JOBOFFER_MANAGEMENT_NO(+)              
  AND T8.SEND_COUNT(+)            <= ?              
  AND T1.JOBSEEKER_NO              = ?
2.13 KB in 6 ms with coderay