We help IT Professionals succeed at work.
Get Started

View based on table, view contains null values but table does not have null values for col6.

gram77
gram77 asked
on
649 Views
Last Modified: 2013-12-07
I have a table mytable.

A view myVU is based on the table.

CREATE OR REPLACE FORCE VIEW "UAT"."myVU" ("col1","col2","col3","col4","col5","col6","col7","col8")
AS
  SELECT 'ABC' col1,
    'DEF' col2,
    'GHI' col3,
    'LMN' col4,
    'XYZ' col5,
    col6,
    col7,
    col8
    RELEASE_DATE AS_OF_DT, -- RELEASE_DATE
    AS_OF_DT LOAD_TS       -- Processing DATE
  FROM mytable a ;


view contains null values:
SQL> select count(*)
  2  FROM uat.myVU s
  3  WHERE to_date(s.col_dt) =  TO_DATE ('11/02/2011 00:00:00','mm/dd/yyyy hh24:mi:ss')
  4  and col6 is null;

  COUNT(*)
----------
      4


underlying table does not:
SQL> select count(*)
  2  from uat.mytable
  3  WHERE to_date(col_dt) =  TO_DATE ('11/02/2011 00:00:00','mm/dd/yyyy hh24:mi:ss')
  4  and col6 is null;

  COUNT(*)
----------
         0


How is this possibe.
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 4 Answers and 16 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE