RS_ST_TEST_DATA(SQL View) |
Index Back |
|---|
| SELECT A.CANDIDATE_ID , CASE B.TEST_TYPE WHEN 'R' THEN %Substring(A.ACCOMPLISHMENT %Concat 'x' %Concat %NumToChar(C.SEQ_NUM),1,8) ELSE CASE A.PASSED WHEN 'Y' THEN %Substring(A.ACCOMPLISHMENT %Concat 'xP',1,8) WHEN 'N' THEN %Substring(A.ACCOMPLISHMENT %Concat 'xF',1,8) ELSE %Substring(A.ACCOMPLISHMENT %Concat 'xF',1,8) END END ,%Coalesce(B.TEST_TYPE,'P') ,A.PASSED ,%Coalesce(C.SEQ_NUM,1) ,A.SCORE ,%Coalesce(C.FROM_PERIOD,0) ,%Coalesce(C.TO_PERIOD,0) FROM %Table(FO_CAND_TEST) A LEFT OUTER JOIN %Table(FO_TEST_SRCH) B ON A.ACCOMPLISHMENT = B.ACCOMPLISHMENT LEFT OUTER JOIN %Table(FO_TEST_SRC_DTL) C ON B.ACCOMPLISHMENT = C.ACCOMPLISHMENT WHERE (B.TEST_TYPE = 'R' AND A.SCORE >= C.FROM_PERIOD ) OR B.TEST_TYPE = 'P' OR B.TEST_TYPE IS NULL |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CANDIDATE_ID | Character(11) | VARCHAR2(11) NOT NULL | Used as a general ID for anything from Employee id to other types of ID like asset and material. |
| 2 | ACCOMPLISHMENT | Character(8) | VARCHAR2(8) NOT NULL | Accomplishment |
| 3 | TEST_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Test Type
P=Pass/Fail R=Range |
| 4 | PASSED | Character(1) | VARCHAR2(1) NOT NULL | Passed Test |
| 5 | SEQ_NUM | Number(3,0) | SMALLINT NOT NULL | Sequence |
| 6 | SCORE | Number(7,2) | DECIMAL(6,2) NOT NULL | Test Score |
| 7 | FROM_PERIOD | Number(3,0) | SMALLINT NOT NULL | From Period |
| 8 | TO_PERIOD | Number(3,0) | SMALLINT NOT NULL | To Period |