Coldfusion Function convert into an Oracle view by using CASE WHEN

Question

My application manage meetings. Each meeting is linked to a unit. So I have one table in my DB: MEETINGS and a view UNITS linked with the key ORG_ID.

In the view 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  |    
+---------+---------+------------+------------+----------|

Thus for a meeting several records of the unit assigned can be retrieved. My goal would be to retrieve the correct record. That's why, I created a function in Coldfusion in order to get an object "Unit" of the correct version of the unit used when creating the meeting.

The parameter unitsArray contains all units of the Units table linked to the meeting.

Here my script:

<cffunction name="getUnitVersionFromMeetingOrgId" access="remote" returnformat="JSON" returntype="budgetting.class.ResponseVO" hint="This function get the correct unit version of a meeting.">
    <cfargument name="meetingOrgId" type="numeric" required="yes">
    <cfargument name="meetingId" type="numeric" required="yes">  
    <cfargument name="unitsArray" type="string" required="yes">  

    <cfset unit = "">

    <cfscript>      
        var dataUnitArray = deserializeJSON(arguments.unitsArray,true);         
    </cfscript>

    <cftry>         

        <cfset hist = 0/>
        <cfset unit = structNew() />    

        <cfloop index="i" from="1" to="#ArrayLen(dataUnitArray)#">  

            <cfif #dataUnitArray[i].REUN_NUMRREUN# EQ #meetingId#>

                <cfif #dataUnitArray[i].ORG_ID# EQ #meetingOrgId#>

                    <cfif #dataUnitArray[i].ORIGIN# EQ "current">
                        <!---  Unit is current  --->
                        <cfscript>

                            unit.REUN_NUMRREUN = #dataUnitArray[i].REUN_NUMRREUN#;          
                            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.REUN_NUMRREUN = #dataUnitArray[i].REUN_NUMRREUN#;          
                                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 other  --->
                            <cfif hist EQ 0>

                                <cfscript>
                                    unit.REUN_NUMRREUN = #dataUnitArray[i].REUN_NUMRREUN#;          
                                    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>

                </cfif>

            </cfif>             

        </cfloop>

        <cfscript>
            return unit;
        </cfscript>

        <cfcatch type="any">                
            <cfscript>
                ...
            </cfscript>         
        </cfcatch>
    </cftry>    

</cffunction>

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 by using CASE...WHEN (perhaps in a procedure but I'm not an expert for that):

CASE
    when ORIGIN = 'current' THEN 1 
    WHEN ORIGIN = 'history' THEN
        CASE  hist = 0 THEN ....

        END
ELSE 
   0   
END  AS "IS_CORRECT_VERSION"

Here I would like to have

+------------+--------+---------+------------+------------|----------|--------------|
| MEETING_ID | ORG_ID |  TITLE  | VALID_FROM | VALID_TO   |  ORIGIN  | CORRECT_VERS |
+------------+--------+---------+------------+------------|----------|--------------|
| 23600      | 1234   | A.1     | 01/03/2016 | 31/12/9999 | other    |      0       |
| 23600      | 1234   | A.1     | 01/03/2016 | 31/12/3333 | current  |      1       |
| 23600      | 1234   | A.1     | 01/03/2016 | 31/12/9999 | history  |      0       |
| 41500      | 5420   | A.2     | 01/01/2014 | 31/12/3333 | other    |      1       |
| 37800      | 9876   | A.3     | 01/03/2016 | 31/12/3333 | current  |      1       |
| 37800      | 9876   | B.3     | 01/03/2016 | 31/12/9999 | history  |      0       |
| 24200      | 5527   | A.1     | 01/03/2016 | 31/12/2199 | current  |      1       |
| 24200      | 5527   | D.2     | 01/01/2010 | 31/12/2015 | history  |      0       |
| 24200      | 5527   | A.1     | 01/01/2016 | 31/12/2199 | history  |      0       |
| 53690      | 6699   | E.5     | 01/01/2016 | 31/12/2017 | history  |      0       |
| 53690      | 6699   | A.4     | 01/01/2017 | 31/12/2018 | history  |      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 of a meeting by filtering on the new column.

I'm trying to that but It's not working each time.

Could you please help me with that?

Thanks in advance for your help.

Seb

Why did you ask this question [again](https://stackoverflow.com/questions/57460093/convert-coldfusion-script-in-oracle-view-with-case-and-iteration/57461644?noredirect=1#comment101418723_57461644)? Do You want only `case` without numbering rows? But you have to check all rows and find _best_. And row_number() / dense_rank() is the fastest solution. The alternative you mentioned, function in PLSQL, is much slower. — Ponder Stibbons, Aug 13 '19 at 10:04

score 1 · Answer 1 · answered Aug 13 '19 at 08:34

You can do this by using dense_rank to find the first row (when the order is by origin (current before history before other) and the latest date).

Then it's simply a matter of outputting 1 for the first row and 0 for the others, like so:

WITH your_table AS (SELECT 1234 org_id, 'A.1' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/9999', 'dd/mm/yyyy') valid_to, 'other' origin FROM dual UNION ALL
                    SELECT 1234 org_id, 'A.1' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/3333', 'dd/mm/yyyy') valid_to, 'current' origin FROM dual UNION ALL
                    SELECT 1234 org_id, 'A.1' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/9999', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 5420 org_id, 'A.2' title, to_date('01/01/2014', 'dd/mm/yyyy') valid_from, to_date('31/12/3333', 'dd/mm/yyyy') valid_to, 'other' origin FROM dual UNION ALL
                    SELECT 9876 org_id, 'A.3' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/3333', 'dd/mm/yyyy') valid_to, 'current' origin FROM dual UNION ALL
                    SELECT 9876 org_id, 'B.3' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/9999', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 5527 org_id, 'A.1' title, to_date('01/03/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/2199', 'dd/mm/yyyy') valid_to, 'current' origin FROM dual UNION ALL
                    SELECT 5527 org_id, 'D.2' title, to_date('01/01/2010', 'dd/mm/yyyy') valid_from, to_date('31/12/2015', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 5527 org_id, 'A.1' title, to_date('01/01/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/2199', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 6699 org_id, 'E.5' title, to_date('01/01/2016', 'dd/mm/yyyy') valid_from, to_date('31/12/2017', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 6699 org_id, 'A.4' title, to_date('01/01/2017', 'dd/mm/yyyy') valid_from, to_date('31/12/2018', 'dd/mm/yyyy') valid_to, 'history' origin FROM dual UNION ALL
                    SELECT 5588 org_id, 'C.2' title, to_date('01/02/2014', 'dd/mm/yyyy') valid_from, to_date('31/12/2012', 'dd/mm/yyyy') valid_to, 'other' origin FROM dual UNION ALL
                    SELECT 5588 org_id, 'C.2' title, to_date('01/02/2014', 'dd/mm/yyyy') valid_from, to_date('31/12/2017', 'dd/mm/yyyy') valid_to, 'other' origin FROM dual)
SELECT org_id,
       title,
       valid_from,
       valid_to,
       origin,
       CASE WHEN dense_rank() OVER (PARTITION BY org_id
                                    ORDER BY CASE WHEN origin = 'current' THEN 1
                                                  WHEN origin = 'history' THEN 2
                                                  WHEN origin = 'other' THEN 3
                                                  ELSE 4
                                             END, valid_to DESC) = 1
                 THEN 1
            ELSE 0
       END correct_vers
FROM   your_table;

    ORG_ID TITLE VALID_FROM  VALID_TO    ORIGIN  CORRECT_VERS
---------- ----- ----------- ----------- ------- ------------
      1234 A.1   01/03/2016  31/12/3333  current            1
      1234 A.1   01/03/2016  31/12/9999  history            0
      1234 A.1   01/03/2016  31/12/9999  other              0
      5420 A.2   01/01/2014  31/12/3333  other              1
      5527 A.1   01/03/2016  31/12/2199  current            1
      5527 A.1   01/01/2016  31/12/2199  history            0
      5527 D.2   01/01/2010  31/12/2015  history            0
      5588 C.2   01/02/2014  31/12/2017  other              1
      5588 C.2   01/02/2014  31/12/2012  other              0
      6699 A.4   01/01/2017  31/12/2018  history            1
      6699 E.5   01/01/2016  31/12/2017  history            0
      9876 A.3   01/03/2016  31/12/3333  current            1
      9876 B.3   01/03/2016  31/12/9999  history            0

the problem is that the current is not the first one for all units and meetings. But I think that I found the solution: `select meeting_id, units.*, case when 1 = row_number() over ( partition by org_id, meeting_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 meeting_unit_v` — coeurdange57, Aug 13 '19 at 10:08

Coldfusion Function convert into an Oracle view by using CASE WHEN

1 Answers1