March 20, 2008 at 12:47 pm
Guys....
I have a table. I also have a view for that table.
I did a comparison on both tables and found that some data which are in the table is not showing in the view.
I have been troubleshooting and found no end. What are the possibilities for this madness?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 20, 2008 at 1:06 pm
Do you have schema data for the table and code for the view?
DAB
March 20, 2008 at 1:19 pm
Without the relevant code, here are some general things to look for:
A) Is there a "Where" clause in the view that limits the rows it returns?
B) Are there columns in the table that aren't in the view?
C) Is there a join in the view that might limit the rows returned?
Those are the three main things that would cause "missing data" in a view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 20, 2008 at 1:30 pm
Guys..... I know it would be easier to just give away the code. But it is the company policy that I should not. Let me try to modify somethings and mimic the code so that nobody gets in trouble. I will have it shortly. Please continue to help though.
Yes, as for the questions above, unfortunately I do have some joins and also some of the column, we don;t want to have in the view. Wait for my code. I know that will ease up a little bit.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 20, 2008 at 1:49 pm
Here is the modified code:
CREATE VIEW [dbo].[AAA] with SCHEMABINDING as
SELECT
I.ID as ID,
I.RootID as RootID,
BI.Age as Age,
I.Sub as Sub,
I.Usage as Usage,
I.Report as Report,
LI.Location as Location,
SL.SubLocation as SubLocation,
from
dbo.Item as I
inner join dbo.BackItem BI on
BI.ID = I.ID
inner join dbo.LocationIn LI on
LI.Location = BI.Location
inner join dbo.SubLocation SL on
SL.SubLocation = LI.SubLocation
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 20, 2008 at 2:04 pm
ok..
Table1 has M columns with X as primary key, Table2 has N columns with Y that relates to Table1
The following examples are based on number of rows, not some key equality
If M.X > N.Y then
Table1 INNER JOIN Table2 on X=Y will return at most N.Y rows
If M.X = N.Y then
Table1 INNER JOIN Table2 on X=Y will return N.Y rows
If M.X < N.Y then
Table1 INNER JOIN Table2 on X=Y will return at most M.X number of rows
This is before any predicates are applied. This should explain why the view can return fewer rows than in the table itself.
DAB
March 20, 2008 at 2:12 pm
I see what you are saying, but I think my view is proper in that case right? Do you think? Any better way to accomplish synchronization between both table and view?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 20, 2008 at 2:19 pm
Syntactically, yes, the view is correct. But is you want ALL the rows from both tables where the keys match PLUS all the rows from the first table (regardless) the use a LEFT OUTER JOIN like this...
dbo.Item as I
LEFT OUTER JOIN dbo.BackItem BI on
BI.ID = I.ID
etc..
This will give you Items with a BackItem AND Items without which should be all the rows in the Item table.
DAB
March 20, 2008 at 2:27 pm
Thanks Buddy! I am gonna try it out. Hopefully it works!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply