Union Flaw
-
That sounds strange. Can you post your DDL, and is possible a reproducable scenario with data. Otherwise just sample data and expected vs actual results.
-
You can say:
/////////////////////////////////////
For Basic View1: select 1,2,3 from tbl_Main where category=1
For Basic View2: select 1,2,3 from tbl_Main where category=2
For Basic View3: select 1,2,3 from tbl_Main where category=3
For Main:
select 1,2,3 from View1
union
select 1,2,3 from View2
union
select 1,2,3 from View3
/////////////////////////////////////
That's It...
May be there is something wrong...
-
I don't think that this is enough detail.
Can you also possibly include some sample source data and what you are seeing - highlighting the spurious rows.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
How do you mean? This example would return a single row with three columns, with the values 1, 2 and 3 in those columns.
-
This is the formula: Union = Union All + Distinct
When you use UNION it will display only the distinct records.
Please use UNION ALL if you need to get all records.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Following your own explanation, surely the formula is
UNION ALL = UNION + duplicates
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
May I add that this is by design and in no way a flaw
. -
I am really sorry, I just missed what I had to mention...
All the basic views include selective data and union is used to combine the three Views in 1 (That is the Main view).
Now:
For Basic View1: select 1,2,3 from tbl_Main where category=1
For Basic View2: select 1,2,3 from tbl_Main where category=2
For Basic View3: select 1,2,3 from tbl_Main where category=3
____________________________________
Create view Main
select 1,2,3 from View1
union
select 1,2,3 from View2
union
select 1,2,3 from View3
__________________________________
There are still some records where category is NULL but present in the
Main View.
I have gone through the checks and things but found no problem. No complex joins or etc.
-
Not sure I understand...
Are you asking why you are receiving data in the main view even though category is NULL ?
OR are you saying that you are not getting all of your data(This could be explained by your using UNION not UNION all..as explained above.)
OR are you saying you are getting data in the main View that you do don't have in any other views ?
Mathew J Kulangara
sqladventures.blogspot.com -
Jan, you are still not clear enough for us to be able to post anything helpful. Is this your real DDL? If so I would be surprised if you are receiving anything else than one row comprised of the three values 1, 2 and 3 in one column each.
It does sound strange that there are rows returned in the main view but not in any of the underlying views by themselves, so clearly there is something wrong. Either it is a bug of some sort in the productm which would be very interesting, or there is some mistake in your code. Either way we can only speculate if we do not have complete DDL of the base table(s), underlying views and the main view. Some sample data, preferable data that shows the error, would also be required. If the error is not easily reproducable then an example of the expected output vs the actual output (for instance some of the rows that should not show up but do) would do.
-
OR are you saying you are getting data in the main View that you do don't have in any other views ?
That is the problem as I understand it.
-
It sounds to me the difference was caused by ANSI_NULLS settings for the views.
Can you check the settings for all the four views by generating their scripts?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply