使用Full Outer Join与Union All创建视图时出现缺失关键字错误

你的脚本存在三个核心语法问题，导致了"缺失关键字"错误：

1. CREATE VIEW语句缺少AS关键字，WITH子句位置错误 #

Oracle创建视图的标准语法是CREATE OR REPLACE VIEW <视图名> AS <查询语句>，你的脚本在视图名称后直接使用WITH子句，漏掉了关键的AS，且WITH子句必须作为查询语句的起始部分。

2. 连接条件存在拼写错误 #

连接条件里的pp.StorrDBKEy是笔误，正确写法应为pp.StoreDBKey（与右侧的lp.storedbkey对应），拼写错误会导致Oracle无法识别该列，触发语法解析错误。

3. 视图定义中ORDER BY的合规性 #

虽然你的ORDER BY放在了语句末尾，但Oracle视图中使用ORDER BY时，需确保整个联合查询结构完全正确，否则会伴随语法问题放大错误。

修正后的完整脚本 #

CREATE OR REPLACE FORCE VIEW "VW_RANGE_REPORT_Jul2023"
AS -- 新增缺失的AS关键字
WITH live_str AS (
    SELECT ------- from ---------- 
),
pend_str AS (
    SELECT ----------from -----------
),
pend_only AS (
    Select --------from ---------
)
SELECT  
NVL(lp.StoreNumber,pp.StoreNumber) AS StoreNumber, 
NVL(lp.StoreName,pp.StoreName) AS StoreName, 
lp.floorplanDBkey AS LiveFLRkey, 
lp.floorplanName As LiveFloorplanName, 
lp.FLRPENDDATE As LiveFLRPendDate, 
lp.FLRLIVEDATE AS LiveFLRLiveDate, 
lp.PlanogramKey AS LivePOGKey, 
lp.PlanogramName AS LivePOGName, 
lp.PlanogramPendingDate AS LivePOGPendDate, 
lp.PlanogramLiveDate AS LivePOGLiveDate, 
pp.floorplanDBkey AS PendFLkey, 
pp.floorplanName As PendFloorplanName, 
pp.FLRPENDDATE As PendFLRPendDate, 
pp.FLRLIVEDATE AS PendFLRLiveDate, 
pp.PlanogramKey AS PendPOGKey, 
pp.PlanogramName AS PendPOGName, 
pp.PlanogramPendingDate AS PendPOGPendDate, 
pp.PlanogramLiveDate AS PendPOGLiveDate
FROM live_str lp
FULL OUTER JOIN pend_str pp 
    ON pp.PlanogramVersionKey = lp.PlanogramVersionKey 
    AND pp.floorplanversionkey = lp.floorplanversionkey         
    AND pp.StoreNumber = lp.StoreNumber
    AND pp.StoreDBKey = lp.storedbkey -- 修正拼写错误
UNION ALL
SELECT  
po.StoreNumber,
po.StoreName,
NULL AS LiveFLRKey,
NULL AS LiveFloorplanName,
NULL AS LiveFLRPendDate,
NULL AS LiveFLRLiveDate,
NULL AS LivePOGKey,
NULL AS LivePOGName,
NULL AS LivePOGPendDate,
NULL AS LivePOGLiveDate,
po.FloorplanDBKey AS PendFLKey,
po.FloorplanName as PendFloorplanName,
po.FLRPENDDATE as PendFLRPendDate,
po.FLRLIVEDATE as PendFLRLiveDate,
po.PlanogramKey AS PendPOGKey,
po.PlanogramName AS PendPOGName,
po.PlanogramPendingDate AS PendPOGPendDate,
po.PlanogramLiveDate AS PendPOGLiveDate
FROM pend_only po
ORDER BY 1,3;

内容的提问来源于stack exchange，提问作者Rufina