使用CASE和迭代在Oracle视图中转换Coldfusion脚本 [英] Convert Coldfusion script in Oracle view with CASE and iteration
问题描述
我创建了一个Coldfusion脚本，允许从数组中获取对象单元".
I created a coldfusion script allowing to get an object "unit" from an array.
我的数据库有关于单位的视图.对于相同的单位键"ORG_ID"，它可以存在多行(字段"origin"不同).字段来源"可以是当前"，历史"或不同".
My DB has a view about units. For a same unit key "ORG_ID", it can exist several rows (with a difference on a field "origin"). the field "origin" can be "current", "history" or "different".
+---------+---------+------------+------------+----------|
| ORG_ID | TITLE | VALID_FROM | VALID_TO | ORIGIN |
+---------+---------+------------+------------+----------|
| 1234 | A.1 | 01/03/2016 | 31/12/9999 | other |
| 1234 | A.1 | 01/03/2016 | 31/12/3333 | current |
| 1234 | A.1 | 01/03/2016 | 31/12/9999 | history |
| 5420 | A.2 | 01/01/2014 | 31/12/3333 | other |
| 9876 | A.3 | 01/03/2016 | 31/12/3333 | current |
| 9876 | B.3 | 01/03/2016 | 31/12/9999 | history |
| 5527 | A.1 | 01/03/2016 | 31/12/2199 | current |
| 5527 | D.2 | 01/01/2010 | 31/12/2015 | history |
| 5527 | A.1 | 01/01/2016 | 31/12/2199 | history |
| 6699 | E.5 | 01/01/2016 | 31/12/2017 | history |
| 6699 | A.4 | 01/01/2017 | 31/12/2018 | history |
+---------+---------+------------+------------+----------|
例如，在这种情况下，我想要得到的结果是
In this case for instance here the result that I would like to get:
+---------+---------+------------+------------+----------|--------------|
| ORG_ID | TITLE | VALID_FROM | VALID_TO | ORIGIN | CORRECT_VERS |
+---------+---------+------------+------------+----------|--------------|
| 1234 | A.1 | 01/03/2016 | 31/12/9999 | other | 0 |
| 1234 | A.1 | 01/03/2016 | 31/12/3333 | current | 1 |
| 1234 | A.1 | 01/03/2016 | 31/12/9999 | history | 0 |
| 5420 | A.2 | 01/01/2014 | 31/12/3333 | other | 1 |
| 9876 | A.3 | 01/03/2016 | 31/12/3333 | current | 1 |
| 9876 | B.3 | 01/03/2016 | 31/12/9999 | history | 0 |
| 5527 | A.1 | 01/03/2016 | 31/12/2199 | current | 1 |
| 5527 | D.2 | 01/01/2010 | 31/12/2015 | history | 0 |
| 5527 | A.1 | 01/01/2016 | 31/12/2199 | history | 0 |
| 6699 | E.5 | 01/01/2016 | 31/12/2017 | history | 0 |
| 6699 | A.4 | 01/01/2017 | 31/12/2018 | history | 0 |
+---------+---------+------------+------------+----------+--------------|
我的Coldfusion脚本: dataUnitArray包含数组中单元的列表
My Coldfusion script: dataUnitArray contains the list of units in an array
<cftry>
<cfset hist = 0/>
<cfset unit = structNew() />
<cfloop index="i" from="1" to="#ArrayLen(dataUnitArray)#">
<cfif #dataUnitArray[i].ORIGIN# EQ "current">
<!--- Unit is current --->
<cfscript>
unit.ORG_ID = #dataUnitArray[i].ORG_ID#;
unit.TITLE = #dataUnitArray[i].TITLE#;
unit.UNIT_VALID_FROM = #dateFormat(dataUnitArray[i].UNIT_VALID_FROM, 'DD/MM/YYYY')#;
unit.UNIT_VALID_TO = #dateFormat(dataUnitArray[i].UNIT_VALID_TO, 'DD/MM/YYYY')#;
unit.ORIGIN = #dataUnitArray[i].ORIGIN#;
return unit;
</cfscript>
<cfelse>
<cfif #dataUnitArray[i].ORIGIN# EQ "history">
<!--- Unit is history --->
<cfscript>
unit.ORG_ID = #dataUnitArray[i].ORG_ID#;
unit.TITLE = #dataUnitArray[i].TITLE#;
unit.UNIT_VALID_FROM = #dateFormat(dataUnitArray[i].UNIT_VALID_FROM, 'DD/MM/YYYY')#;
unit.UNIT_VALID_TO = #dateFormat(dataUnitArray[i].UNIT_VALID_TO, 'DD/MM/YYYY')#;
unit.ORIGIN = #dataUnitArray[i].ORIGIN#;
</cfscript>
<cfset hist++ >
<cfelse>
<!--- Unit is different (other) --->
<cfif hist EQ 0>
<cfscript>
unit.ORG_ID = #dataUnitArray[i].ORG_ID#;
unit.TITLE = #dataUnitArray[i].TITLE#;
unit.UNIT_VALID_FROM = #dateFormat(dataUnitArray[i].UNIT_VALID_FROM, 'DD/MM/YYYY')#;
unit.UNIT_VALID_TO = #dateFormat(dataUnitArray[i].UNIT_VALID_TO, 'DD/MM/YYYY')#;
unit.ORIGIN = #dataUnitArray[i].ORIGIN#;
</cfscript>
</cfif>
</cfif>
</cfif>
</cfloop>
<cfscript>
return unit;
</cfscript>
<cfcatch type="any">
<cfscript>
.....
</cfscript>
</cfcatch>
</cftry>
我的脚本正常工作.但是当我在很多数据上使用它时，我会遇到加载时间问题.这就是为什么我想直接在ORACLE中使用CASE ... WHEN这样做:
My script is correctly working. But I have loading time problem when I used it on a lot of data. That's why I would like to do that directly in ORACLE, with CASE...WHEN as:
CASE
when ORIGIN = 'current' THEN 1
WHEN ORIGIN = 'history' THEN
CASE hist = 0 THEN ....
END
ELSE
0
END AS "IS_CORRECT_VERSION"
我想在视图中添加新列"CORRECT_VERSION"(当版本正确时为0或1)，以检索正确的单元版本.
I would like to add a new column "CORRECT_VERSION" ( value 0 or 1 when the version is correct) in a view in order to retrieve the correct unit version.
但是我不知道该怎么做，你能帮我吗?
But I don't know how to do that, could you please help me with that?
预先感谢您的帮助.
Seb
推荐答案
我不知道ColdFusion，但是我想我了解逻辑.优先级是当前>历史>不同.当有两个current行或只有different行时，不清楚哪一行是正确的，因此在这种情况下，我用最小的valid_from标记行.如果您不在乎，则可以忽略此参数(从row_number的order by子句中删除unit_valid_from):
I do not know ColdFusion, but I think I understood the logic. Priority is current > history > different. It is not clear which row is correct when there are two current rows or only different rows, so I mark row with minimum valid_from in such case. If you don't care You can omit this parameter (remove unit_valid_from from row_number's order by clause):
select units.*,
case when 1 =
row_number() over (
partition by org_id
order by case origin when 'current' then 1 when 'history' then 2 else 3 end,
unit_valid_from ) then 1 else 0 end as is_correct_version
from units
这篇关于使用CASE和迭代在Oracle视图中转换Coldfusion脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
