Schema (simplified)
Job HasMany Elements (which can be cable or terminal)
Cable is an element.
Terminal is an element.
This is setup using table per type inheritance.
Goal: Query a job and include all its elements.
If I remove completely Cable from my project, the query will run fine and include all the terminals related. The same applies if I remove Terminal completely from my project: The query will run fine and include all the cables related.
When I include both and try to run the query, I get a "ORA-12704: character set mismatch" error.
SQL Generated:
SELECT "Project3".C1,
"Project3".G3E_IDENTIFIER,
"Project3".G3E_DESCRIPTION,
"Project3".G3E_OWNER,
"Project3".G3E_STATUS,
"Project3".JOB_TYPE,
"Project3".JOB_STATE,
"Project3".WORK_ORDER_ID,
"Project3".G3E_PLACED,
"Project3".COMPANY_OWNERSHIP,
"Project3".G3E_CREATION,
"Project3".G3E_POSTED,
"Project3".G3E_CLOSED,
"Project3".G3E_ADDJOBATTR,
"Project3".G3E_FIELDUSER,
"Project3".G3E_JOBCLASS,
"Project3".G3E_ID,
"Project3".G3E_PROCESSINGSTATUS,
"Project3".G3E_POSTFLAG,
"Project3".C45 AS C2,
"Project3".C4 AS C3,
"Project3".C2 AS C4,
"Project3".C3 AS C5,
"Project3".JOB_ID,
"Project3".JOB_STATE1,
"Project3".FEATURE_STATE,
"Project3".PLAN_ID,
"Project3".SWITCH_CENTRE_CLLI,
"Project3".OWNERSHIP,
"Project3".YEAR_PLACED,
"Project3".MIC,
"Project3".MIN_MATERIAL,
"Project3".SERVICE_CODE,
"Project3".IMAP_FEATURE_ID,
"Project3".LTT_STATUS,
"Project3".SAP_WRK_ID,
"Project3".G3E_ID1,
"Project3".G3E_FNO,
"Project3".G3E_CNO,
"Project3".G3E_CID,
"Project3".LTT_TID,
"Project3".LTT_DATE,
"Project3".C5 AS C6,
"Project3".C6 AS C7,
"Project3".C7 AS C8,
"Project3".C8 AS C9,
"Project3".C9 AS C10,
"Project3".C10 AS C11,
"Project3".C11 AS C12,
"Project3".C12 AS C13,
"Project3".C13 AS C14,
"Project3".C14 AS C15,
"Project3".C15 AS C16,
"Project3".C16 AS C17,
"Project3".C17 AS C18,
"Project3".C18 AS C19,
"Project3".C19 AS C20,
"Project3".C20 AS C21,
"Project3".C21 AS C22,
"Project3".C22 AS C23,
"Project3".C23 AS C24,
"Project3".C24 AS C25,
"Project3".C25 AS C26,
"Project3".C26 AS C27,
"Project3".C27 AS C28,
"Project3".C28 AS C29,
"Project3".C29 AS C30,
"Project3".C30 AS C31,
"Project3".C31 AS C32,
"Project3".C32 AS C33,
"Project3".C33 AS C34,
"Project3".C34 AS C35,
"Project3".C35 AS C36,
"Project3".C36 AS C37,
"Project3".C37 AS C38,
"Project3".C38 AS C39,
"Project3".C39 AS C40,
"Project3".C40 AS C41,
"Project3".C41 AS C42,
"Project3".C42 AS C43,
"Project3".C43 AS C44,
"Project3".C44 AS C45
FROM (
SELECT
"Extent1".G3E_IDENTIFIER,
"Extent1".G3E_DESCRIPTION,
"Extent1".G3E_OWNER,
"Extent1".G3E_STATUS,
"Extent1".JOB_TYPE,
"Extent1".JOB_STATE,
"Extent1".WORK_ORDER_ID,
"Extent1".G3E_PLACED,
"Extent1".COMPANY_OWNERSHIP,
"Extent1".G3E_CREATION,
"Extent1".G3E_POSTED,
"Extent1".G3E_CLOSED,
"Extent1".G3E_ADDJOBATTR,
"Extent1".G3E_FIELDUSER,
"Extent1".G3E_JOBCLASS,
"Extent1".G3E_ID,
"Extent1".G3E_PROCESSINGSTATUS,
"Extent1".G3E_POSTFLAG,
1 AS C1,
"Join1".G3E_FID1 AS C2,
"Join1".LTT_ID1 AS C3,
"Join1".JOB_ID,
"Join1".JOB_STATE AS JOB_STATE1,
"Join1".FEATURE_STATE, "Join1".PLAN_ID,
"Join1".SWITCH_CENTRE_CLLI,
"Join1".OWNERSHIP,
"Join1".YEAR_PLACED,
"Join1".MIC,
"Join1".MIN_MATERIAL,
"Join1".SERVICE_CODE,
"Join1".IMAP_FEATURE_ID,
"Join1".LTT_STATUS,
"Join1".SAP_WRK_ID,
"Join1".G3E_ID AS G3E_ID1,
"Join1".G3E_FNO,
"Join1".G3E_CNO,
"Join1".G3E_CID,
"Join1".LTT_TID,
"Join1".LTT_DATE,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_CHAR(NULL) WHEN "Join1".C18 = 1 THEN '2X0X' ELSE '2X1X' END AS C4,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".DIAMETER END AS C5,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".TOTAL_LENGTH END AS C6,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".TOTAL_SIZE END AS C7,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".GAUGE END AS C8,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".PERCENT_AERIAL END AS C9,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".PERCENT_BURIED END AS C10,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".COPPER_SIZE END AS C11,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".NUMBER_OF_VIDEO_PAIRS END AS C12,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".NUMBER_OF_COAX_TUBES END AS C13,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".CMP_NUMBER_OF_COAX_TUBES END AS C14,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".G3E_PAIRCOUNTPREFIX END AS C15,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN "Join1".FIBER_SIZE END AS C16,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".ALPHA_CODE END AS C17,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".ARMOUR END AS C18,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".COMPOSITION END AS C19,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".SHEATH END AS C20,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".USAGE END AS C21,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".OTHER_ID END AS C22,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".STUB_LABEL END AS C23,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".COUNT_ANNOTATION END AS C24,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".FIBER_TAG_ID END AS C25,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".DESIGN_TYPE END AS C26,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN "Join1".FIBER_MODE END AS C27,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C1 END AS C28,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C2 END AS C29,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C3 END AS C30,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C4 END AS C31,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C5 END AS C32,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C6 END AS C33,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C7 END AS C34,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) WHEN "Join1".C18 = 1 THEN TO_NUMBER(NULL) ELSE "Join1".C8 END AS C35,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C9 END AS C36,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C10 END AS C37,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C11 END AS C38,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C12 END AS C39,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C13 END AS C40,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C14 END AS C41,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C15 END AS C42,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C16 END AS C43,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NCHAR(NULL) WHEN "Join1".C18 = 1 THEN TO_NCHAR(NULL) ELSE "Join1".C17 END AS C44,
CASE WHEN "Join1".G3E_FID1 IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C45 FROM GCOMM.G3E_JOB "Extent1"
LEFT OUTER JOIN (
SELECT
"UnionAll1".G3E_FID AS G3E_FID1,
"UnionAll1".LTT_ID AS LTT_ID1,
"UnionAll1".DIAMETER,
"UnionAll1".TOTAL_LENGTH,
"UnionAll1".TOTAL_SIZE,
"UnionAll1".GAUGE,
"UnionAll1".PERCENT_AERIAL,
"UnionAll1".PERCENT_BURIED,
"UnionAll1".COPPER_SIZE,
"UnionAll1".NUMBER_OF_VIDEO_PAIRS,
"UnionAll1".NUMBER_OF_COAX_TUBES,
"UnionAll1".CMP_NUMBER_OF_COAX_TUBES,
"UnionAll1".G3E_PAIRCOUNTPREFIX,
"UnionAll1".FIBER_SIZE,
"UnionAll1".ALPHA_CODE,
"UnionAll1".ARMOUR,
"UnionAll1".COMPOSITION,
"UnionAll1".SHEATH,
"UnionAll1".USAGE,
"UnionAll1".OTHER_ID,
"UnionAll1".STUB_LABEL,
"UnionAll1".COUNT_ANNOTATION,
"UnionAll1".FIBER_TAG_ID,
"UnionAll1".DESIGN_TYPE,
"UnionAll1".FIBER_MODE,
"UnionAll1".C1,
"UnionAll1".C2,
"UnionAll1".C3,
"UnionAll1".C4,
"UnionAll1".C5,
"UnionAll1".C6,
"UnionAll1".C7,
"UnionAll1".C8,
"UnionAll1".C9,
"UnionAll1".C10,
"UnionAll1".C11,
"UnionAll1".C12,
"UnionAll1".C13,
"UnionAll1".C14,
"UnionAll1".C15,
"UnionAll1".C16,
"UnionAll1".C17,
"UnionAll1".C18,
"Extent4".G3E_FID AS G3E_FID2,
"Extent4".LTT_ID AS LTT_ID2,
"Extent4".JOB_ID,
"Extent4".JOB_STATE,
"Extent4".FEATURE_STATE,
"Extent4".PLAN_ID,
"Extent4".SWITCH_CENTRE_CLLI,
"Extent4".OWNERSHIP,
"Extent4".YEAR_PLACED,
"Extent4".MIC,
"Extent4".MIN_MATERIAL,
"Extent4".SERVICE_CODE,
"Extent4".IMAP_FEATURE_ID,
"Extent4".LTT_STATUS,
"Extent4".SAP_WRK_ID,
"Extent4".G3E_ID,
"Extent4".G3E_FNO,
"Extent4".G3E_CNO,
"Extent4".G3E_CID,
"Extent4".LTT_TID,
"Extent4".LTT_DATE
FROM
(SELECT
"Extent2".G3E_FID,
"Extent2".LTT_ID,
"Extent2".DIAMETER,
"Extent2".TOTAL_LENGTH,
"Extent2".TOTAL_SIZE,
"Extent2".GAUGE,
"Extent2".PERCENT_AERIAL,
"Extent2".PERCENT_BURIED,
"Extent2".COPPER_SIZE,
"Extent2".NUMBER_OF_VIDEO_PAIRS,
"Extent2".NUMBER_OF_COAX_TUBES,
"Extent2".CMP_NUMBER_OF_COAX_TUBES,
"Extent2".G3E_PAIRCOUNTPREFIX,
"Extent2".FIBER_SIZE,
"Extent2".ALPHA_CODE,
"Extent2".ARMOUR,
"Extent2".COMPOSITION,
"Extent2".SHEATH,
"Extent2".USAGE,
"Extent2".OTHER_ID,
"Extent2".STUB_LABEL,
"Extent2".COUNT_ANNOTATION,
"Extent2".FIBER_TAG_ID,
"Extent2".DESIGN_TYPE,
"Extent2".FIBER_MODE,
TO_NUMBER(NULL) AS C1,
TO_NUMBER(NULL) AS C2,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NUMBER(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_NCHAR(NULL) AS C9,
TO_NCHAR(NULL) AS C10,
TO_NCHAR(NULL) AS C11,
TO_NCHAR(NULL) AS C12,
TO_NCHAR(NULL) AS C13,
TO_NCHAR(NULL) AS C14,
TO_NCHAR(NULL) AS C15,
TO_NCHAR(NULL) AS C16,
TO_NCHAR(NULL) AS C17,
1 AS C18
FROM GCOMM."B$GC_CBL" "Extent2"
UNION ALL
SELECT
"Extent3".G3E_FID,
"Extent3".LTT_ID,
TO_NUMBER(NULL) AS C1,
TO_NUMBER(NULL) AS C2,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NUMBER(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_NUMBER(NULL) AS C9,
TO_NUMBER(NULL) AS C10,
TO_NUMBER(NULL) AS C11,
TO_NUMBER(NULL) AS C12,
TO_NCHAR(NULL) AS C13,
TO_NCHAR(NULL) AS C14,
TO_NCHAR(NULL) AS C15,
TO_NCHAR(NULL) AS C16,
TO_NCHAR(NULL) AS C17,
TO_NCHAR(NULL) AS C18,
TO_NCHAR(NULL) AS C19,
TO_NCHAR(NULL) AS C20,
TO_NCHAR(NULL) AS C21,
TO_NCHAR(NULL) AS C22,
TO_NCHAR(NULL) AS C23,
"Extent3".TERMINAL_SYS_ID,
"Extent3".TOTAL_SIZE,
"Extent3".QUANTITY,
"Extent3".NUM_INSTALLED_BP,
"Extent3".STUB_LENGTH,
"Extent3".STUB_SIZE,
"Extent3".STUB_GAUGE,
"Extent3".G3E_PAIRCOUNTPREFIX,
"Extent3".FEATURE_TYPE,
"Extent3".MODEL,
"Extent3".SYSTEM_NUMBER,
"Extent3".STUB_TYPE,
"Extent3".RDP,
"Extent3".COUNT_ANNOTATION,
"Extent3".TAPER_CODE,
"Extent3".TERM_DATA_ANNOTATION,
"Extent3".TERM_DATA_ANNOTATION_FRNH,
0 AS C24
FROM GCOMM."B$GC_TERM" "Extent3") "UnionAll1"
INNER JOIN GCOMM."B$GC_NETELEM" "Extent4" ON ("UnionAll1".G3E_FID = "Extent4".G3E_FID)
AND ("UnionAll1".LTT_ID = "Extent4".LTT_ID) ) "Join1" ON "Extent1".G3E_IDENTIFIER = "Join1".JOB_ID
WHERE (("Extent1".WORK_ORDER_ID = 'SAPXTCT-59')
AND ( NOT (("Extent1".WORK_ORDER_ID IS NULL) OR ('SAPXTCT-59' IS NULL))))
OR (("Extent1".WORK_ORDER_ID IS NULL) AND ('SAPXTCT-59' IS NULL)) ) "Project3"
ORDER BY "Project3".G3E_IDENTIFIER ASC, "Project3".C45 ASC