Xin chào các bác, tính hình là mình đang gặp vấn đề với truy vấn SQL với số code khá dài.
Code hiện tại như thế này:
SELECT DISTINCT * FROM (
SELECT
TO_CHAR(ATA.CYCLE_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CYCLE_DATE,
TEMP.TXTAGENT_CD AS TXTAGENTCODE,
TEMP.AGENT_CODE,
AGENT_NAME_THAI,
PROSPECT_CODE,
PROSPECT_FIRSTNAME_EN,
PROSPECT_SURNAME_EN,
PROSPECT_AGENT_NAME_THAI,
CITIZEN_ID,
COURSE_CODE,
COURSE_NAME,
TO_CHAR(FINISHED_DATE, 'yyyy-MM-dd hh24:mi:ss') AS dteCourse_passed_date,
INSTRUCTOR_CODE,
INSTRUCTOR_NAME,
TEMP.TXTCLASS_CD,
TO_CHAR(CLASS_START_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CLASS_START_DATE,
TO_CHAR(CLASS_END_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CLASS_END_DATE,
LOCATION_CODE,
CLASS_RESULT,
CLASS_ID,
USER_INFO_ID,
CASE WHEN ATA.CYCLE_DATE IS NULL
AND ATA.AGENT_CODE IS NULL
AND ATA.TXTCLASS_CD IS NULL
AND TEMP.TXTAGENT_CD IS NOT NULL
AND TEMP.TXTCLASS_CD IS NOT NULL
AND (IS_AGENT_PASSED_CLASS='Pass') THEN 0
ELSE ATA.TRANSFER_RESULT
END AS TRANSFER_RESULT,
IS_AGENT_PASSED_CLASS
FROM (
select1String
) TEMP
FULL OUTER JOIN (
SELECT DISTINCT TO_NCHAR(AT.AGENT_CD) AS AGENT_CODE, AT.CLASS_CD AS TXTCLASS_CD , CLASS_START_DATE, CLASS_END_DATE, LOCATION_CD AS LOCATION_CODE, TEMP_ATA.CYCLE_DATE, TRANSFER_RESULT
FROM LMS_AGENT_TRAINING_AMS AT
INNER JOIN (
SELECT AGENT_CD, CLASS_CD, MAX(CYCLE_DATE) AS CYCLE_DATE
FROM LMS_AGENT_TRAINING_AMS
GROUP BY AGENT_CD, CLASS_CD) TEMP_ATA ON AT.AGENT_CD = TEMP_ATA.AGENT_CD AND AT.CLASS_CD = TEMP_ATA.CLASS_CD AND AT.CYCLE_DATE = TEMP_ATA.CYCLE_DATE
) ATA ON ATA.AGENT_CODE = TEMP.txtAgent_cd AND ATA.TXTCLASS_CD = TEMP.txtClass_cd
WHERE 1=1
AND TO_DATE(TO_CHAR(FINISHED_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') >= TO_DATE('02/18/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE(TO_CHAR(FINISHED_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') <= TO_DATE('02/20/2019 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
AND COURSE_CODE LIKE 'APR'
)
ORDER BY AGENT_CODE ASC
giờ mình muốn add thêm code để truy vấn ra thêm trường REMOVE_RESULT theo như sau:
SELECT DISTINCT * FROM (
SELECT
TO_CHAR(ATA.CYCLE_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CYCLE_DATE,
TEMP.TXTAGENT_CD AS TXTAGENTCODE,
TEMP.AGENT_CODE,
AGENT_NAME_THAI,
PROSPECT_CODE,
PROSPECT_FIRSTNAME_EN,
PROSPECT_SURNAME_EN,
PROSPECT_AGENT_NAME_THAI,
CITIZEN_ID,
COURSE_CODE,
COURSE_NAME,
TO_CHAR(FINISHED_DATE, 'yyyy-MM-dd hh24:mi:ss') AS dteCourse_passed_date,
INSTRUCTOR_CODE,
INSTRUCTOR_NAME,
TEMP.TXTCLASS_CD,
TO_CHAR(CLASS_START_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CLASS_START_DATE,
TO_CHAR(CLASS_END_DATE, 'yyyy-MM-dd hh24:mi:ss') AS CLASS_END_DATE,
LOCATION_CODE,
CLASS_RESULT,
CLASS_ID,
USER_INFO_ID,
CASE WHEN ATA.CYCLE_DATE IS NULL
AND ATA.AGENT_CODE IS NULL
AND ATA.TXTCLASS_CD IS NULL
AND TEMP.TXTAGENT_CD IS NOT NULL
AND TEMP.TXTCLASS_CD IS NOT NULL
AND (IS_AGENT_PASSED_CLASS='Pass') THEN 0
ELSE ATA.TRANSFER_RESULT
END AS TRANSFER_RESULT,
**ATA.REMOVE_RESULT **
** END AS REMOVE_RESULT,**
IS_AGENT_PASSED_CLASS
FROM (
select1String
) TEMP
FULL OUTER JOIN (
SELECT DISTINCT TO_NCHAR(AT.AGENT_CD) AS AGENT_CODE, AT.CLASS_CD AS TXTCLASS_CD , CLASS_START_DATE, CLASS_END_DATE, LOCATION_CD AS LOCATION_CODE, TEMP_ATA.CYCLE_DATE, TRANSFER_RESULT, **REMOVE_RESULT**
FROM LMS_AGENT_TRAINING_AMS AT
INNER JOIN (
SELECT AGENT_CD, CLASS_CD, MAX(CYCLE_DATE) AS CYCLE_DATE
FROM LMS_AGENT_TRAINING_AMS
GROUP BY AGENT_CD, CLASS_CD) TEMP_ATA ON AT.AGENT_CD = TEMP_ATA.AGENT_CD AND AT.CLASS_CD = TEMP_ATA.CLASS_CD AND AT.CYCLE_DATE = TEMP_ATA.CYCLE_DATE
) ATA ON ATA.AGENT_CODE = TEMP.txtAgent_cd AND ATA.TXTCLASS_CD = TEMP.txtClass_cd
WHERE 1=1
AND TO_DATE(TO_CHAR(FINISHED_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') >= TO_DATE('02/18/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE(TO_CHAR(FINISHED_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') <= TO_DATE('02/20/2019 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
AND COURSE_CODE LIKE 'APR'
)
ORDER BY AGENT_CODE ASC
nhưng thêm như trên thì chương trình báo lỗi truy vấn. Các bác ai biết cách có thể giúp mình đưa ra cách truy vấn đúng được không ạ. Dưới là bảng có chứa trường REMOVE_RESULT cần thêm vào
Mình xin cảm ơn!