PowerBuilder 19数据窗口SQL执行报错ORA-00933，求排查原因

在PowerBuilder 19的DataWindow对象中执行以下SQL时出现ORA-00933错误，核心原因是混合使用旧式逗号表连接与ANSI JOIN语法，导致Oracle解析器无法正确识别命令结构，同时部分逻辑括号的优先级也可能引发解析问题。

原SQL代码：

SELECT * from ( 
  SELECT 
    '                                                            ' as data_hora_documento, 
    '     ' as ind_horario_verao, 
    '     ' as ind_evento_retroativo, 
    '       ' as atendimento, 
    '                                                                                                                                                                                                                                          ' as justificava_retroativa, 
    '                                                                                                                                                                                                                                          ' as pac_nome_social, 
    psv.psv_nome, pac.PDC_COR, pac.PDC_NATURALIDADE, cde.cde_ufe_sigla, pdc_r.PDC_R_ROT, pro.pro_nome, 
    pac.PAC_OBS, pac.pac_est_civil, pac.pac_nome_mae, pac.pac_reg, pac.pac_nome, pac.pac_pront, pac.pac_sexo, pac.pac_nasc, pac.pac_fone, 
    cnv.cnv_nome, pac.pac_mcnv, pac.pac_cnv, pac.pac_end, pac.pac_comp, pac.pac_cep, pac.pac_cid, pac.pac_uf, 
    pac.pac_numrg, pac.pac_numcpf, pac.pac_numrg_org, pac.pac_numrg_uf, 
    '                                                                                                                                                                                                                                          ' as mensagem_aprovacao 
  FROM tbh pac 
  left join pro on pac.PAC_PRO_COD = pro.PRO_COD 
  left join psv on psv.psv_Cod = pac.pac_medico 
  left join pdc_r on pac.PDC_RELIGIAO = pdc_r.PDC_R_COD 
  LEFT JOIN cde ON pac.pdc_naturalidade_cde_cod = cde.cde_cod, 
  cnv 
  WHERE (pac.pac_reg = 3475184) 
  AND ( (pac_versao = 0) OR ( ( (0 = 0) OR (0 IS NULL) ) AND NOT EXISTS ( SELECT 1 FROM pdc WHERE pdc_pac_reg = 3475184 ) ) AND pac_versao = ( SELECT max(pac_versao) FROM tbh t WHERE t.pac_reg = 3475184 ) ) 
  AND (cnv.cnv_cod = 'BRA') 
  UNION 
  SELECT 
    '                                                            ' as data_hora_documento, 
    '     ' as ind_horario_verao, 
    '     ' as ind_evento_retroativo, 
    '       ' as atendimento, 
    '                                                                                                                                                                                                                                          ' as justificava_retroativa, 
    '                                                                                                                                                                                                                                          ' as pac_nome_social, 
    psv.psv_nome, pdc.PDC_COR, pdc.PDC_NATURALIDADE, cde.cde_ufe_sigla, pdc_r.PDC_R_ROT, pro.pro_nome, 
    PAC_OBS, pac_est_civil, pac.pac_nome_mae, pac.pac_reg, pac.pac_nome, pac.pac_pront, pac.pac_sexo, pac.pac_nasc, pac.pac_fone, 
    cnv.cnv_nome, pac.pac_mcnv, pac.pac_cnv, pac.pac_end, pac.pac_comp, pac.pac_cep, pac.pac_cid, pac.pac_uf, 
    pac.pac_numrg, pac.pac_numcpf, pac.pac_numrg_org, pac.pac_numrg_uf, 
    '                                                                                                                                                                                                                                          ' as mensagem_aprovacao 
  FROM pac 
  left join pro on pac.PAC_PRO_COD = pro.PRO_COD 
  left join psv on psv.psv_Cod = pac.pac_medico, 
  cnv, 
  pdc left join pdc_r on pdc.PDC_RELIGIAO = pdc_r.PDC_R_COD 
  LEFT JOIN cde ON pdc.pdc_naturalidade_cde_cod = cde.cde_cod 
  WHERE (pac.pac_reg = 3475184) 
  And ( 0 = 0 or 0 is null ) 
  and (cnv.cnv_cod = 'BRA') 
  and (pdc.pdc_pac_reg = pac.pac_reg) 
) as pac 

具体问题点及修正方案： #

• 混合连接语法问题：Oracle对旧式逗号连接和ANSI JOIN的混合写法解析逻辑严格，容易触发语法错误。需将所有逗号连接替换为显式JOIN，并明确连接条件。
  • 第一个SELECT中，cnv表的逗号连接需改为LEFT JOIN cnv ON 1=1（因原条件仅过滤cnv.cnv_cod = 'BRA'，无关联字段，用1=1表示笛卡尔积，若有实际业务关联字段请替换此处）。
  • 第二个SELECT中，cnv和pdc的逗号连接需调整为显式JOIN，将pdc的连接逻辑移至正确位置，通过pdc.pdc_pac_reg = pac.pac_reg建立关联。
• 逻辑括号优先级：第一个SELECT的WHERE子句中，OR与AND的嵌套括号存在优先级歧义，重新梳理括号分组，确保逻辑判断清晰。

修正后的SQL示例：

SELECT * from ( 
  SELECT 
    '                                                            ' as data_hora_documento, 
    '     ' as ind_horario_verao, 
    '     ' as ind_evento_retroativo, 
    '       ' as atendimento, 
    '                                                                                                                                                                                                                                          ' as justificava_retroativa, 
    '                                                                                                                                                                                                                                          ' as pac_nome_social, 
    psv.psv_nome, pac.PDC_COR, pac.PDC_NATURALIDADE, cde.cde_ufe_sigla, pdc_r.PDC_R_ROT, pro.pro_nome, 
    pac.PAC_OBS, pac.pac_est_civil, pac.pac_nome_mae, pac.pac_reg, pac.pac_nome, pac.pac_pront, pac.pac_sexo, pac.pac_nasc, pac.pac_fone, 
    cnv.cnv_nome, pac.pac_mcnv, pac.pac_cnv, pac.pac_end, pac.pac_comp, pac.pac_cep, pac.pac_cid, pac.pac_uf, 
    pac.pac_numrg, pac.pac_numcpf, pac.pac_numrg_org, pac.pac_numrg_uf, 
    '                                                                                                                                                                                                                                          ' as mensagem_aprovacao 
  FROM tbh pac 
  LEFT JOIN pro ON pac.PAC_PRO_COD = pro.PRO_COD 
  LEFT JOIN psv ON psv.psv_Cod = pac.pac_medico 
  LEFT JOIN pdc_r ON pac.PDC_RELIGIAO = pdc_r.PDC_R_COD 
  LEFT JOIN cde ON pac.pdc_naturalidade_cde_cod = cde.cde_cod 
  LEFT JOIN cnv ON 1=1 
  WHERE pac.pac_reg = 3475184 
    AND (
      pac_versao = 0 
      OR (
        (0 = 0 OR 0 IS NULL) 
        AND NOT EXISTS (SELECT 1 FROM pdc WHERE pdc_pac_reg = 3475184) 
        AND pac_versao = (SELECT MAX(pac_versao) FROM tbh t WHERE t.pac_reg = 3475184)
      )
    ) 
    AND cnv.cnv_cod = 'BRA' 
  UNION 
  SELECT 
    '                                                            ' as data_hora_documento, 
    '     ' as ind_horario_verao, 
    '     ' as ind_evento_retroativo, 
    '       ' as atendimento, 
    '                                                                                                                                                                                                                                          ' as justificava_retroativa, 
    '                                                                                                                                                                                                                                          ' as pac_nome_social, 
    psv.psv_nome, pdc.PDC_COR, pdc.PDC_NATURALIDADE, cde.cde_ufe_sigla, pdc_r.PDC_R_ROT, pro.pro_nome, 
    PAC_OBS, pac_est_civil, pac.pac_nome_mae, pac.pac_reg, pac.pac_nome, pac.pac_pront, pac.pac_sexo, pac.pac_nasc, pac.pac_fone, 
    cnv.cnv_nome, pac.pac_mcnv, pac.pac_cnv, pac.pac_end, pac.pac_comp, pac.pac_cep, pac.pac_cid, pac.pac_uf, 
    pac.pac_numrg, pac.pac_numcpf, pac.pac_numrg_org, pac.pac_numrg_uf, 
    '                                                                                                                                                                                                                                          ' as mensagem_aprovacao 
  FROM pac 
  LEFT JOIN pro ON pac.PAC_PRO_COD = pro.PRO_COD 
  LEFT JOIN psv ON psv.psv_Cod = pac.pac_medico 
  LEFT JOIN cnv ON 1=1 
  LEFT JOIN pdc ON pdc.pdc_pac_reg = pac.pac_reg 
  LEFT JOIN pdc_r ON pdc.PDC_RELIGIAO = pdc_r.PDC_R_COD 
  LEFT JOIN cde ON pdc.pdc_naturalidade_cde_cod = cde.cde_cod 
  WHERE pac.pac_reg = 3475184 
    AND (0 = 0 OR 0 IS NULL) 
    AND cnv.cnv_cod = 'BRA' 
) as pac 

验证步骤： #

1. 将修正后的SQL在Oracle客户端（如PL/SQL Developer）中单独执行，确认无语法错误。
2. 再将SQL导入PowerBuilder的DataWindow对象中测试运行。

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