I have a table with 100000 records and I get 97000 records as part of this below query how to get the remaining 30000 records. I tried not equal to 'H.RESOURCE_ID <> SQ.CPNT_OG_ID'
but not getting correct data?
SELECT H.pernr AS "STUD_ID",
SQ.CPNT_TYP_ID,
SQ.CPNT_ID,
TO_CHAR (SQ.REV_DTE, 'MON-DD-YYYY HH24:MI:SS') AS REV_DTE,
CASE --WAIVED
WHEN sq.CPNT_TYP_ID = 'VIDEO' AND enrollment_status = 'Complete'
THEN
'VIDEO-COMPLETE'
WHEN sq.CPNT_TYP_ID = 'VIDEO' AND enrollment_status <> 'Complete'
THEN
'VIDEO-WAIVED'
WHEN sq.CPNT_TYP_ID = 'CERTIFICATION'
AND enrollment_status = 'Complete'
THEN
'CERT-COMPLETE'
WHEN sq.CPNT_TYP_ID = 'CERTIFICATION'
AND enrollment_status <> 'Complete'
THEN
'CERT-WAIVED'
WHEN sq.CPNT_TYP_ID = 'CDOC' AND enrollment_status = 'Complete'
THEN
'CDOC-COMPLETE'
WHEN sq.CPNT_TYP_ID = 'CDOC' AND enrollment_status <> 'Complete'
THEN
'CDOC-WAIVED'
WHEN sq.CPNT_TYP_ID = 'ELEARNING'
AND enrollment_status = 'Complete'
THEN
'Elearning-COMPLETE-PASSED'
WHEN sq.CPNT_TYP_ID = 'ELEARNING'
AND enrollment_status <> 'Complete'
THEN
'Elearning-WAIVED'
ELSE
NULL
END
"CMPL_STAT_ID",
TO_CHAR (H.COMPLETION_DATE, 'MON-DD-YYYY HH24:MI:SS')
AS COMPLETION_DATE,
'RECORD_Admin'
AS "ESIG_MEANING_CODE_ID",
NULL AS "LST_UPD_USR",
sq.rev_num AS "REV_NUM",
'LEARNSHARE' AS "COMMENTS"
FROM ZB_USR_HISTORY_NEW H
JOIN
(SELECT DISTINCT CPNT_ID,
CPNT_TYP_ID,
CPNT_OG_ID,
REV_DTE,
REV_NUM,
CPNT_COURSE_NUM
FROM TBL_ZIM_ITEM_new
WHERE VALID = '1' AND app_id LIKE '%en-us%' OR LENGTH (app_id) < 9)
SQ
ON (H.RESOURCE_ID = SQ.CPNT_OG_ID);
See Question&Answers more detail:os