select-oracle創建視圖 報錯 [Err] ORA-00905: missing keyword

oracle創建視圖 報錯 [Err] ORA-00905: missing keyword

SELECT
0 AS cpxx_id,
t_ckgl_wlcc.ccdbh AS djbh,
t_ckgl_wlcc.xgsj AS xgsj,
0 AS ycl_dhd_id,
t_ckgl_wlccmx.ccdmx_id AS ccdmx_id,
0 AS sctl_id,
0 AS zt,
t_ckgl_wlcc.zzjgdm AS zzjgdm,
(
SELECT
t_jygl_crcjy.zt AS jyjl
FROM
t_jygl_crcjy
WHERE
(
t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id
)
) AS jyjl,
t_ckgl_wlccmx.wl_id AS sccp_id,
t_ckgl_wlccmx.wlzl_bj AS cpbj,
t_ckgl_wlccmx.jydj_id AS crcjy_id,
t_ckgl_wlccmx.pch AS pch,
t_ckgl_wlccmx.txm AS txm,
t_cpgl_cpxx.cp_mc AS mc,
(
SELECT
t_jygl_crcjy.jydbh AS jydbh
FROM
t_jygl_crcjy
WHERE
(
t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id
)
) AS jydbh,
(
SELECT
t_jygl_crcjy.sfly AS sfly
FROM
t_jygl_crcjy
WHERE
(
t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id
)
) AS sfly,
(
CASE t_ckgl_wlccmx.cc_bj
WHEN 0 THEN
0
WHEN 1 THEN
0
WHEN 2 THEN
1
WHEN 3 THEN
1
WHEN 4 THEN
1
END
) AS jybj,
(
SELECT
t_jygl_crcjy.djyh_mc AS djyh_mc
FROM
t_jygl_crcjy
WHERE
(
t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id
)
) AS djyh_mc,
(
SELECT
t_jygl_crcjy.jysj AS jysj
FROM
t_jygl_crcjy
WHERE
(
t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id
)
) AS jysj,
t_ckgl_wlccmx.sc_bj AS sc_bj,
t_ckgl_wlcc.zf_bj AS zf_bj
FROM
(
(
t_ckgl_wlccmx
JOIN t_cpgl_cpxx
)
JOIN t_ckgl_wlcc
)
WHERE
(
(
t_ckgl_wlccmx.ccd_id = t_ckgl_wlcc.ccd_id
)
AND (
t_ckgl_wlccmx.wl_id = t_cpgl_cpxx.cp_id
)
AND (
t_ckgl_wlccmx.wlzl_bj = 2
)
AND (t_ckgl_wlccmx.cc_bj <> 0)
AND (t_ckgl_wlccmx.sc_bj <> 1)
AND (t_cpgl_cpxx.sc_bj <> 1)
AND (t_ckgl_wlcc.tjzt = 1)
)

最佳回答：

改成這樣試試，應該是你的join用錯了

 SELECT 0 AS cpxx_id,
       t_ckgl_wlcc.ccdbh AS djbh,
       t_ckgl_wlcc.xgsj AS xgsj,
       0 AS ycl_dhd_id,
       t_ckgl_wlccmx.ccdmx_id AS ccdmx_id,
       0 AS sctl_id,
       0 AS zt,
       t_ckgl_wlcc.zzjgdm AS zzjgdm,
       (SELECT t_jygl_crcjy.zt AS jyjl
          FROM t_jygl_crcjy
         WHERE (t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id))
          AS jyjl,
       t_ckgl_wlccmx.wl_id AS sccp_id,
       t_ckgl_wlccmx.wlzl_bj AS cpbj,
       t_ckgl_wlccmx.jydj_id AS crcjy_id,
       t_ckgl_wlccmx.pch AS pch,
       t_ckgl_wlccmx.txm AS txm,
       t_cpgl_cpxx.cp_mc AS mc,
       (SELECT t_jygl_crcjy.jydbh AS jydbh
          FROM t_jygl_crcjy
         WHERE (t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id))
          AS jydbh,
       (SELECT t_jygl_crcjy.sfly AS sfly
          FROM t_jygl_crcjy
         WHERE (t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id))
          AS sfly,
       (CASE t_ckgl_wlccmx.cc_bj
           WHEN 0 THEN 0
           WHEN 1 THEN 0
           WHEN 2 THEN 1
           WHEN 3 THEN 1
           WHEN 4 THEN 1
        END)
          AS jybj,
       (SELECT t_jygl_crcjy.djyh_mc AS djyh_mc
          FROM t_jygl_crcjy
         WHERE (t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id))
          AS djyh_mc,
       (SELECT t_jygl_crcjy.jysj AS jysj
          FROM t_jygl_crcjy
         WHERE (t_jygl_crcjy."ID" = t_ckgl_wlccmx.jydj_id))
          AS jysj,
       t_ckgl_wlccmx.sc_bj AS sc_bj,
       t_ckgl_wlcc.zf_bj AS zf_bj
  FROM t_ckgl_wlccmx, t_cpgl_cpxx, JOIN t_ckgl_wlcc
 WHERE (    (t_ckgl_wlccmx.ccd_id = t_ckgl_wlcc.ccd_id)
        AND (t_ckgl_wlccmx.wl_id = t_cpgl_cpxx.cp_id)
        AND (t_ckgl_wlccmx.wlzl_bj = 2)
        AND (t_ckgl_wlccmx.cc_bj <> 0)
        AND (t_ckgl_wlccmx.sc_bj <> 1)
        AND (t_cpgl_cpxx.sc_bj <> 1)
        AND (t_ckgl_wlcc.tjzt = 1))