How to perform validation of sum based on a factor value from a function?

The below insert merges data from 2 different sources.  

I need to perform a validation check and modify this insert statement, such that, the insert should only happen if sum of totals of Saleschannel is within 0.005 of sum(totals) for the  Energy channel.   The validation factor of 0.005 is obtained from a call to a function returning the factor of 0.005.

Any suggestion on how to perform this check inside the Insert statement?


INSERT INTO regionsales
      (-- 
       PARTITION_KEY,
       FIRSTDAY,
       LASTDAY,
       STARTTIME,
       STOPTIME,
       SALESID,
       INTV1,INTV2,INTV3,TOTALS
       --
      ) 
       SELECT          
              PARTITION_KEY,
              FIRSTDAY,
              LASTDAY,
              STARTTIME,
              STOPTIME,
              SALESID,
              INTV1, INTV2, INTV3,
              TOTALS,
              SUBTRACT_TOTAL,
              (TOTALS - SUBTRACT_TOTAL) AS ADJ_TOTAL 
       FROM
       (--    
        SELECT             
              u.PARTITION_KEY,
              u.FIRSTDAY,
              u.LASTDAY,
              u.STARTTIME,
              u.STOPTIME,
              u.SALESID,
              u.INTV1,  u.INTV2, u.INTV3,
              NVL(SUM(CASE WHEN u.intervaldatasource ='SALESCHANNEL' THEN TOTALS END),0) AS TOTALS,  
              NVL(SUM(CASE WHEN u.intervaldatasource ='ENERGYCHANNEL' THEN SUBTRACT_TOTAL END),0)AS SUBTRACT_TOTAL                     
         FROM
         (--
          SELECT 
              l.partition_key AS PARTITION_KEY,
              l.firstday AS FIRSTDAY,
              l.lastday AS LASTDAY,
              l.STARTTIME AS STARTTIME,
              l.STOPTIME AS STOPTIME,
              l.SALESID AS SALESID,
              l.INTV1, l.INTV2, l.INTV3,
              SUM(INTV1+l.INTV2+l.INTV3) AS TOTALS
              FROM REGIONSALES l 
           WHERE l.intervaldatasource =  'SALESCHANNEL' and l.partition_key = '20170101_20170331'
           GROUP BY l.partition_key ,
              l.firstday,
              l.lastday,
              l.STARTTIME,
              l.STOPTIME,
              l.SALESID,
              l.INTV1, l.INTV2, l.INTV3
         UNION ALL
           SELECT
             e.partition_key AS PARTITION_KEY,
             e.firstday AS FIRSTDAY,
             e.lastday AS LASTDAY,
             e.starttime AS STARTTIME,
             e.stoptime AS STOPTIME,
             e.salesid AS SALESID,
             e.INTV1,e.INTV2,e.INTV3,
             SUM (e.INTV1+ e.INTV2+e.INTV3) AS TOTALS 
           FROM REGIONSALES e 
            WHERE E.intervaldatasource = 'ENERGYCHANNEL' and e.partition_key = '20170101_20170331'
            GROUP BY e.partition_key,
             e.firstday,
             e.lastday,
             e.starttime,
             e.stoptime,
             e.salesid,
             e.INTV1,e.INTV2,e.INTV3
         --
         ) U
         GROUP BY u.PARTITION_KEY,
                  u.FIRSTDAY,
                  u.LASTDAY,
                  u.STARTTIME,
                  u.STOPTIME,
                  u.SALESID,
                  u.INTV1,  u.INTV2, u.INTV3
       --             
       )X                  
       order by salesid;

Open in new window

steve2312Asked:
Who is Participating?
 
awking00Commented:
What does the function for the validation factor look like? It may be that you only need a having clause added to your group by statement.
1
 
slightwv (䄆 Netminder) Commented:
>>Any suggestion on how to perform this check inside the Insert statement?

Sure.  Add it to the where clause:
INSERT INTO regionsales
      (-- 
...
)
where some_function_call(param1,param2) <= .005 

Open in new window

or however you  wish to use it.

Also:  Remove the order by on the insert.  It serves no purpose and does an unnecessary sort.

and as I post in most of your Oracle questions:
If you want anything more specific, please provide sample data and expected results.  That way we can post  tested code.

The more specific your example, the more exact our code.
0
 
steve2312Author Commented:
Thanks for suggestions slightvw and awking.  

The selection criteria within the function would look something below where the factor value (v_factor_value ) is returned back the function.

SELECT FVC.value
              INTO v_factor_value
              FROM (SELECT fv.starttime,
                           fv.val AS value,
                           MAX(fv.starttime) OVER (PARTITION BY fv.uidfactor) AS maxstarttime
                      FROM table_fct fv
                      JOIN fact      f
                        ON (f.uidfactor  = FV.uidfactor)
                       AND f.factorcode  = UPPER(p_factcode)
                       AND f.starttime <= p_asofdate) FVC
             WHERE fvc.starttime = fvc.maxstarttime;

Open in new window


Also, the variables  p_factcode and p_asofdate are the input parameters to the functi0n.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
OK, and what is that supposed to tell us?

As long as the function returns the value you want, you can use it in the where clause of the insert statement.

If what we've already posted doesn't help, we will need actual sample data and expected results.
0
 
steve2312Author Commented:
I posted the pseudo code as per awking comments.  

Meanwhile here's a sample illustration with some data

Query for saleschannel (lines 38 - 54 from original post)
gives the sample data below

","PARTITION_KEY","FIRSTDAY","LASTDAY","STARTTIME","STOPTIME","SALESID","INTV1","INTV2","INTV3","TOTALS"
"1","20100101_20101231","10/1/2014","12/31/2014","10/2/2014","10/2/2014 11:59:59 PM","NA","7017","6925","6809","20751"
"2","20100101_20101231","10/1/2014","12/31/2014","10/5/2014","10/5/2014 11:59:59 PM","NA","5181","5095","5029","15305"
"3","20100101_20101231","10/1/2014","12/31/2014","10/29/2014","10/29/2014 11:59:59 PM","NA","5451","5339","5274","16064"
"4","20100101_20101231","10/1/2014","12/31/2014","11/7/2014","11/7/2014 11:59:59 PM","NA","491","103","178","772"


query for energychannel (lines 56 -73 from original post)

gives the sample data below

","PARTITION_KEY","FIRSTDAY","LASTDAY","STARTTIME","STOPTIME","ESIID","INTV1","INTV2","INTV3","TOTALS"
"1","20100101_20101231","10/1/2014","10/5/2014","10/1/2014","10/1/2014 11:59:59 PM","10204049791733540","182","210","191","583"
"2","20100101_20101231","10/1/2014","10/5/2014","10/1/2014","10/1/2014 11:59:59 PM","10204049741293130","517","518","528","1563"
"3","20100101_20101231","10/1/2014","10/5/2014","10/1/2014","10/1/2014 11:59:59 PM","1008901000165480013100","252","253","249","754"
"4","20100101_20101231","10/1/2014","10/5/2014","10/1/2014","10/1/2014 11:59:59 PM","10443720007030034","135","126","126","774"

Expected results - based on value of ToTals column and the factor of 0.005
Data should be inserted if the sum(totals) from the 2 sets are within +/- 0.005 


So in this illustration  the rows selected for inserted would be the sets of 4th rows (as the sum(totals) are within 0.005). This value of 0.005 is obtained from a function.

"4","20100101_20101231","10/1/2014","12/31/2014","11/7/2014","11/7/2014 11:59:59 PM","NA","491","103","178","772"
"4","20100101_20101231","10/1/2014","10/5/2014","10/1/2014","10/1/2014 11:59:59 PM","10443720007030034","135","126","126","774"

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
We don't have your function and I personally don't understand your requirements.

Can you not add your function call to the WHERE clause like I have suggested.  If not, why?
0
 
steve2312Author Commented:
Is the where clause filtering out based on total columns within the query ( May be am missing something here)?

The count of records with and without the where condition is resulting to be same.
0
 
slightwv (䄆 Netminder) Commented:
You use a WHERE clause on a query to restrict the rows being returned.  Now, if you need to do this on the outer query to restrict what is inserted or on the individual queries you UNION or one of the other queries, I cannot say.

I don't understand your actual requirements so I don't think I can help with specifics.
0
 
steve2312Author Commented:
I used having clause after group by to perform the check...
[code]
 HAVING  (SUM(u.firsttotalcolumn) > (sum(u.secondtotalcolumn)- sum(u.secondtotalcolumn) * functioncall(factcode, date))) AND
                  (SUM(u.firsttotalcolumn) < (sum(u.secondtotalcolumn)+ sum(u.secondtotalcolumnL) * functioncall(factcode, date)))
[/code]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.