CASE statement in MView wont refresh ?

[Exie]

Hi Folks,

This one is driving me nuts! I create a materialized view like so...

CREATE MATERIALIZED VIEW DWH.MERGESALESFIGURES_JS_MV 
TABLESPACE USERS
CACHE
LOGGING
NOCOMPRESS
PARALLEL ( DEGREE 2 INSTANCES 2 )
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT blah, blah
FROM table1, table2,table3
WHERE this = that;

... and it works great, my table is populated as expected and looks great.

I then refresh the view (either via oracle job, or manually) like so:

exec dbms_mview.refresh('DWH.MERGESALESFIGURES_JS_MV','C');

This is a complete refresh, and take a while to run, but comes back with all the rows, but not all the columns. What I mean is, the columns are there, but all the values are null. The columns in particular have case statements like so:

        case when iHead.invs1_doc_type = 2 then 
          case when iLine.invsqty < 0 then abs(iLine.invsqty) else (0-iLine.invsqty) end
          else iLine.invsqty end as Qty,

It's just a simple thing to work out if it's an invoice or a credit note to reverse it from a positive/negative value.

Is there any trick with this ? .... it gets the correct data when I create the view, just not when I refresh it.

 

[SantaMufasa]

Exie,

I don't know if there is some sort of a problem with "CASE" statements and materialized views, but to see if that is a problem, you can replace your CASE code with the following "DECODE" function that produces the results you want:

select * from iline;

INVS1_DOC_TYPE    INVSQTY
-------------- ----------
             1       -100
             2       -200
             2        300
             2       -150
             3        300

select decode(invs1_doc_type,2,invsqty * -1,invsqty) Qty from iline;

       QTY
----------
      -100
       200
      -300
       150
       300

Let us know if this alternative resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”