oracle 我是sql语言的新手,正在学习,这样我就可以帮助我的组织拉取数据,我该如何纠正连接错误?

xxe27gdn  于 2023-03-07  发布在  Oracle
关注(0)|答案(1)|浏览(104)

我已经尝试运行这个命令几天了,但没有成功。

train> SELECT i.bill_cyc_cd, i.store_cd,
  2    SUM(CASE WHEN p.past_due_tp# > 0 AND p.past_due_tp# <= 4 THEN i.sch_pmt_amt ELSE 0 END) AS sch_pmt_amt,
  c.fname, c.lname, c.home_phone
FROM inst_cntr i
JOIN cntr$past_due_tp p ON i.cust_cd = p.cust_cd AND i.cntr_cd = p.cntr_cd
JOIN cust c ON i.cust_cd = c.cust_cd
WHERE i.bill_cyc_cd IN ('300', '150', '015', '030', '922', '921', '920')
  AND p.past_due_tp# > 0 AND p.past_due_tp# <= 4
  AND EXISTS (SELECT 1 FROM cntr$past_due_tp pd WHERE pd.cust_cd = i.cust_cd AND pd.cntr_cd = i.cntr_cd AND pd.past_due_tp# > 0 AND pd.past_due_tp# <= 4 AND pd.past_due_tp# IS NOT NULL)
GROUP BY i.bill_cyc_cd, i.store_cd, c.fname, c.lname, c.home_phone;
  3    4    5    6    7    8    9   10  JOIN cntr$past_due_tp p ON i.cust_cd = p.cust_cd AND i.cntr_cd = p.cntr_cd
*
ERROR at line 5:
ORA-00933: SQL command not properly ended

您可以在底部看到我遇到的错误。
我尝试以不同的方式格式化join命令,但无法使其工作,因为它看起来是正确的。

w41d8nur

w41d8nur1#

您可以在结束组;然后继续

3    4    5    6    7    8    9   10

而随机连接

JOIN cntr$past_due_tp p ON i.cust_cd = p.cust_cd AND i.cntr_cd = p.cntr_cd

这两项可以提供当前误差
传递此查询的理想方法是

SELECT
 i.bill_cyc_cd,
 i.store_cd,
 c.fname,
 c.lname,
 c.home_phone
 SUM(CASE WHEN p.past_due_tp# > 0 AND p.past_due_tp# <= 4 THEN i.sch_pmt_amt ELSE 0 END) AS sch_pmt_amt,
FROM
 inst_cntr i
 JOIN cntr$past_due_tp p ON i.cust_cd = p.cust_cd AND i.cntr_cd = p.cntr_cd
 JOIN cust c ON i.cust_cd = c.cust_cd
WHERE
  i.bill_cyc_cd IN ('300', '150', '015', '030', '922', '921', '920')
  AND p.past_due_tp# > 0 AND p.past_due_tp# <= 4
  AND EXISTS (
    SELECT 1 FROM cntr$past_due_tp pd
    WHERE
      pd.cust_cd = i.cust_cd
      AND pd.cntr_cd = i.cntr_cd
      AND pd.past_due_tp# > 0
      AND pd.past_due_tp# <= 4
      AND pd.past_due_tp# IS NOT NULL
    )
GROUP BY
  i.bill_cyc_cd,
  i.store_cd,
  c.fname,
  c.lname,
  c.home_phone

相关问题