We help IT Professionals succeed at work.

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

gram77
gram77 asked
on
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

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
but where is the col_dt column in your view. It does not have that right.

It has only these right col1","col2","col3","col4","col5","col6","col7","col8"
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
this should not work at all because there is no column with name "col_dt" in your view myvu :

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>CREATE OR REPLACE FORCE VIEW "UAT"."myVU" ("col1","col2","col3","col4","col5","col6","col7","col8")


using double quotes around object names in Oracle force case sensitivity so you aren't querying the view you think you are.

select count(*) from FROM uat.myVU;
and
select count(*) from FROM uat."myVU";

are two different views.  

Do no use double quotes on Oracle objects.

Author

Commented:
date comes from these columns:
load_ts is a function that returns todays date.
    RELEASE_DATE AS_OF_DT, -- RELEASE_DATE
    AS_OF_DT LOAD_TS       -- Processing DATE
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
run the query to check whether your view MYVU has a column with name COL_DT in it or not ?

select table_name, column_name
from dba_tab_columns
where tab_name ='MYVU';
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>date comes from these columns:

Look at the query:  You are using a column in the view called col_dt

The view text you posted shows these two colum naliases:  LOAD_TS and AS_OF_DT.

There is no col_dt.

re: http:#a37082622

you are likely using two different views:

select owner, view_name from all_views where lower(owner) = 'uat' and lower(view_name) = 'myvu';

Author

Commented:
there is only one view myvu
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Can you explain the column name differences in the original post and the double quote issues?

As posted, it doesn't match.

Author

Commented:
that was a typo sorry
the where clause of the view should be:

where TO_DATE(s.as_of_dt) = '3-nov-11';
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Check your view syntax in the original post.

You have a column called release_date that you are aliasing to 'AS_OF_DT'.  The column AS_OF_DATE you are aliasing to load_ts.

When you query as_of_date between the view and table, you are looking at two different columns.


...
RELEASE_DATE AS_OF_DT, -- RELEASE_DATE
AS_OF_DT LOAD_TS      
...
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
to avoid further confusion, can you give your full view query without mistakes etc ?

Author

Commented:
the view has been modified.

Author

Commented:
I've requested that this question be deleted for the following reason:

view code is erronious
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>view code is erronious

Many posts state the view must be wrong or you are not looking at what you think you are.

Please explain where it was wrong.  If no one here provided information that helped make this determination then I'll retract my objection.
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
my comments do help for the requested question. I am not saying others comments do not help. I am just talking about only my comments here.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>my comments do help for the requested question

I disagree.  you second post, http:#a37082606 is the first to point out that there is something wrong between what is posted and what is being selected.

http:#a37082697 helps show the 'typo' in the columns.

http:#a37082622  mentions not looking at the view they think they are.
http:#a37093948  points out a column variation that will cause data differences between the table and view.

I suggest split:
http:#a37082622 accept
http:#a37093948 assist
http:#a37082606 assist
http:#a37082697 assist

Explore More ContentExplore courses, solutions, and other research materials related to this topic.