我使用以下代码填充名为TIMES的维度表与来自OLTP表中的数据（称为SALES ：
CREATE TABLE TIMES (saleDay DATE PRIMARY KEY, dayType VARCHAR(50) NOT NULL); BEGIN FOR rec IN (SELECT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday' WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend' ELSE 'Weekday' END dayType FROM SALES s LEFT JOIN (SELECT '01.01' hd FROM DUAL UNION ALL SELECT '15.01' FROM DUAL UNION ALL SELECT '19.01' FROM DUAL UNION ALL SELECT '28.05' FROM DUAL UNION ALL SELECT '04.07' FROM DUAL UNION ALL SELECT '08.10' FROM DUAL UNION ALL SELECT '11.11' FROM DUAL UNION ALL SELECT '22.11' FROM DUAL UNION ALL SELECT '25.12' FROM DUAL) h ON h.hd = TO_CHAR(s.saleDate, 'dd.mm')) LOOP INSERT INTO TIMES VALUES rec; END LOOP; END; /当我运行这个，我得到了错误ORA-00001（唯一约束违规）和ORA-06512。 我相信这是因为代码尝试将多个日期（其中一些相同）输入到我的TIMES维度表（ saleDay ）的PK中。 我将如何在此循环中实施一个条款，因此它只会将每个saleDate的一个实例填充到saleDay PK中，因此没有违规行为？
例如，如果SALES saleDate为2015-10-10的SALES表中有saleDate ，则代码应该只将2015-10-10的一个实例填充到saleDay PK中。 我正在考虑我应该领导的方向是实施WHILE子句，但是我不能100％确定这将如何工作，因为此代码也使用CASE来确定saleDay日是假期，工作日还是周末，以及将结果填充到dayType列中。
I am populating a dimension table named TIMES with data from an OLTP Table called SALES with the following code:
CREATE TABLE TIMES (saleDay DATE PRIMARY KEY, dayType VARCHAR(50) NOT NULL); BEGIN FOR rec IN (SELECT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday' WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend' ELSE 'Weekday' END dayType FROM SALES s LEFT JOIN (SELECT '01.01' hd FROM DUAL UNION ALL SELECT '15.01' FROM DUAL UNION ALL SELECT '19.01' FROM DUAL UNION ALL SELECT '28.05' FROM DUAL UNION ALL SELECT '04.07' FROM DUAL UNION ALL SELECT '08.10' FROM DUAL UNION ALL SELECT '11.11' FROM DUAL UNION ALL SELECT '22.11' FROM DUAL UNION ALL SELECT '25.12' FROM DUAL) h ON h.hd = TO_CHAR(s.saleDate, 'dd.mm')) LOOP INSERT INTO TIMES VALUES rec; END LOOP; END; /When I run this, I'm getting the errors ORA-00001 (Unique Constraint Violation) and ORA-06512. I believe this is happening because the code is trying to input multiple dates (some of which are the same) into PK for my TIMES Dimension Table (saleDay). How would I implement a clause into this loop so it will only populate one instance of each saleDate into the saleDay PK so there isn't a violation?
For instance, If there are three rows in the SALES table where the saleDate is 2015-10-10, the code should only populate ONE instance of 2015-10-10 into the saleDay PK. I'm thinking the direction I should head is to implement a WHILE clause, however I'm not 100% sure on how that would work since this code is also using CASE to determine whether the saleDay was a Holiday, Weekday, or Weekend and populating the result into the dayType column.
最满意答案
按照您的问题下面的评论中的建议添加DISTINCT是解决问题的一种方法。
以下方法可能更有效：
for rec in (select distinct saledate from sales) loop insert into times (saleday, daytype) values (rec.saledate, CASE .......); end loop;即：将CASE表达式放在INSERT语句中，而不是放在（隐式）游标的定义中。 没有理由在同一日期多次计算CASE表达式，这在SALES表中可能会出现很多次。 CASE表达式也没有理由成为游标的一部分。 CASE表达式可以使用IN条件（ case when to_char(rec.saledate, 'dd.mm') in ('01.01', '15.01', ....) then 'Holiday' when ....... ）
当然，除非作业问题明确指示您使用左外连接........--(
Adding DISTINCT resolved this. Originally thought DISTINCT would negatively impact the CASE but it doesn't. Thanks to I3rutt for pointing this out.
BEGIN FOR rec IN (SELECT DISTINCT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday' WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend' ELSE 'Weekday' END dayType FROM SALES s LEFT JOIN (SELECT '01.01' hd FROM DUAL UNION ALL SELECT '15.01' FROM DUAL UNION ALL SELECT '19.01' FROM DUAL UNION ALL SELECT '28.05' FROM DUAL UNION ALL SELECT '04.07' FROM DUAL UNION ALL SELECT '08.10' FROM DUAL UNION ALL SELECT '11.11' FROM DUAL UNION ALL SELECT '22.11' FROM DUAL UNION ALL SELECT '25.12' FROM DUAL) h ON h.hd = TO_CHAR(s.saleDate, 'dd.mm')) LOOP INSERT INTO TIMES VALUES rec; END LOOP; END; /更多推荐
发布评论