I passed an oracle Query as an expression while creating a dataset in Report Builder. I got the following error while executing the report.
"ORA - 00933 : SQL command not properly ended"
Below is the Oracle Query I used to create the dataset.
= "SELECT
T.TRAY_NO ,
MAX(T.PBS) AS PBS,
MAX(T.SEG_CODE) AS SEG_CODE,
MAX(T.TYPE_CODE) AS TYPE_CODE,
MAX(T.WIDTH) AS WIDTH,
MAX(DEPTH) AS DEPTH,
MAX(LENGTH) AS LENGTH,
MAX(ALLOW_FILL) AS ALLOW_FILL,
MAX(TOTAL_FILL) AS TOTAL_FILL,
MAX(ALLOW_WEIGHT) AS ALLOW_WEIGHT,
MAX(TOTAL_WEIGHT) AS TOTAL_WEIGHT,
SUM(C.NO_OF_CABLE) AS SUM_NO_OF_CABLES,
MAX(IS_FILL_APPROVED) AS OVERFILL_APPROVED,
MAX(IS_WEIGHT_APPROVED) AS OVERWEIGHT_APPROVED,
MAX(FIRE_SEAL) AS FIREA_WRAPPING,
MAX(DESIGN_REF) AS ENOVIA_TREE_PATH,
MAX(LAYOUT_DWG_NO) AS WORKPACKAGE,
MAX(LAYOUT_DWG_REV) AS STATUS,
CASE
WHEN MAX(T.SEG_CODE) IN ('NL','AL','BL') THEN CEIL(SUM(TY.DIAMETER*1.4)/MAX(T.WIDTH))
WHEN MAX(T.SEG_CODE) IN ('NQ') THEN CEIL(SUM(TY.DIAMETER*1.1)/MAX(T.WIDTH))
WHEN MAX(T.SEG_CODE) IN ('NN','NS','NP','AS','BS','XR') THEN CEIL(SUM(TY.DIAMETER*1.1)/MAX(T.WIDTH))
ELSE CEIL(MAX(ROUTE_WIDTH)/MAX(P.WIDTH))
END AS NO_OF_LAYERS,
(SELECT
MAX(RRW.ROOM_NO)
FROM EL_CMS_RACEWAY_ROOM RRW
WHERE RRW.RACEWAY_NO = T.TRAY_NO
GROUP BY RRW.RACEWAY_NO) AS ROOM_NO
FROM EL_CMS_TRAY T, EL_CMS_CABLE_TYPE TY, EL_CMS_ROUTE R, EL_CMS_CABLE C,
(SELECT
TRAY_NO,
WIDTH,
CASE
WHEN SEG_CODE IN ('HV') THEN 30 + SUM_D + 45*(N-1) + 30
WHEN SEG_CODE IN ('MP') THEN 20 + SUM_D + 43*(N-1) + 20
WHEN SEG_CODE IN ('MS') THEN 20 + SUM_D + 56*(N-1) + 20
WHEN SEG_CODE IN ('N6','A6','B6') THEN 20 + SUM_D + 27.5*(N-1) + 20
WHEN SEG_CODE IN ('AH','BH') THEN 15 + SUM_D + 12*(N-1) + 15
WHEN SEG_CODE IN ('NH') AND D1=96 THEN 15 + SUM_D + 50*(N-1) + 15
WHEN SEG_CODE IN ('NH') AND D1=126 THEN 15 + SUM_D + 12*(N-1) + 15
END ROUTE_WIDTH
FROM
(
SELECT
RACEWAY_NO R2,
COUNT(R.CABLE_NO) N,
MAX(CASE WHEN CONDUCTORS=1 THEN DIAMETER*2 ELSE DIAMETER END) D1
FROM EL_CMS_ROUTE R, EL_CMS_CABLE C, EL_CMS_CABLE_TYPE TYPE
WHERE R.CABLE_NO = C.CABLE_NO
AND TYPE.TYPE_CODE=C.TYPE_CODE
GROUP BY RACEWAY_NO
) ,
(
SELECT
RACEWAY_NO R1,
SUM(CASE WHEN CONDUCTORS=1 AND NO_OF_CABLE=3 THEN DIAMETER*2 ELSE DIAMETER END) SUM_D
FROM EL_CMS_ROUTE R, EL_CMS_CABLE C, EL_CMS_CABLE_TYPE TYPE
WHERE R.CABLE_NO = C.CABLE_NO
AND TYPE.TYPE_CODE=C.TYPE_CODE
AND SUBSTR(RACEWAY_NO,5,2) IN ('A6', 'B6', 'N6', 'HV', 'MP', 'MS', 'NH', 'AH', 'BH')
GROUP BY RACEWAY_NO
),
(EL_CMS_TRAY
WHERE R1=R2 AND TRAY_NO=R2
ORDER BY ROUTE_WIDTH-WIDTH DESC) P
WHERE C.TYPE_CODE = TY.TYPE_CODE(+)
AND T.TRAY_NO = R.RACEWAY_NO(+)
AND R.CABLE_NO = C.CABLE_NO (+)
AND T.TRAY_NO = P.TRAY_NO(+)
GROUP BY T.TRAY_NO
ORDER BY TRAY_NO ;
"
Can someone advice how I can resolved this error (ORA - 00933).
