Append without UNION

select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
UNION
select * from GEt_lu_Lookup_UNION -- rows 423
I want to get the full 1,817,571 rows without using a Union Statement, can
anyone help?On Tue, 8 Mar 2005 02:35:04 -0800, marcmc wrote:

>select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
>UNION
>select * from GEt_lu_Lookup_UNION -- rows 423
>I want to get the full 1,817,571 rows without using a Union Statement, can
>anyone help?
Hi marcmc,
My first reaction can only be: why' Is there any particular reason why
you want to avoid UNION?
In case you're worried about performance: UNION will attempt to remove
duplicates. If you're sure there are no duplicates (or if you don't want
them removed), use UNION ALL. This should be lots faster, as the step to
remove duplicates is skipped.
In case you have another reason for not wanting to use UNION: here's one
possible way to get the same results without using UNION or UNION ALL:
-- Same as UNION
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND ....
AND a.ColumnN = b.ColumnN
-- Same as UNION ALL
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON 1 = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx Hugo. Beacause I am investigating the use of an indexed view. The new
code gets what I want but then i get...
CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
GO
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
disallowed constructs.
Any ideas...I've read the rules I just can't find why the workaround won't
work.|||Maybe its the full outer join in the design. The GEt_lu_Lookup_ST1 and
GEt_lu_Lookup_UNION are dummy temp tables.
CREATE VIEW GEv_lu_Lookup WITH SCHEMABINDING AS
SELECT COALESCE(a.Ge_lookup_id, b.Ge_lookup_id) AS Ge_lookup_id,
COALESCE(a.Ge_source_id, b.Ge_source_id) AS Ge_source_id,
COALESCE(a.Ge_lookup_code, b.Ge_lookup_code) AS Ge_lookup_code,
COALESCE(a.Ge_lookup_desc, b.Ge_lookup_desc) AS Ge_lookup_desc,
COALESCE(a.Ge_lookup_parent, b.Ge_lookup_parent) AS Ge_lookup_parent
FROM dbo.GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN dbo.GEt_lu_Lookup_UNION AS b
ON a.Ge_lookup_id = b.Ge_lookup_id
AND a.Ge_lookup_code = b.Ge_lookup_code
-- 1817512 rows in Time: 1:29|||On Tue, 8 Mar 2005 04:11:02 -0800, marcmc wrote:

>Thx Hugo. Beacause I am investigating the use of an indexed view. The new
>code gets what I want but then i get...
> CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
>[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
>[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
>GO
>Server: Msg 1936, Level 16, State 1, Line 1
>Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
>disallowed constructs.
>Any ideas...I've read the rules I just can't find why the workaround won't
>work.
Hi marcmc,
Can you post the CREATE VIEW statement used to create the view
GEv_lu_lookup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:

>Maybe its the full outer join in the design.
Hi marcmc,
Yes, that must be the reason. According to Books Online, outer joins are
not permitted in an indexed view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||any other ideas how I can get all the rows and index my view without the use
of UNION/OUTER JOIN or any other rule breakers?
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:
>
> Hi marcmc,
> Yes, that must be the reason. According to Books Online, outer joins are
> not permitted in an indexed view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 06:15:05 -0800, marcmc wrote:

>any other ideas how I can get all the rows and index my view without the us
e
>of UNION/OUTER JOIN or any other rule breakers?
Hi marcmc,
Not without knowing more about your actual business problem than I know
now. Please post the following:
1. Table structure of both tables, posted as CREATE TABLE statement. If
there are many columns, you may omit those that play no role of
importance. Do include all constraints and properties; especially
PRIMARY KEY constraint and UNIQUE constraints are important in this
case. (www.aspfaq.com/5006)
2. Some sample data to illustrate your situation. No need to post all
1.8 million rows, of course - just enoguh to give me a feeling for the
structure of your data. (http://vyaskn.tripod.com/code.htm#inserts)
3. Expected output from the posted sample data. Especially handling of
duplicates should be visible in the expected output.
4. A short but concise description of the actual business problem you're
trying to solve. Don't assume I know your business - I probably don't.
5. Also: what you are trying to accomplish with this indexed view. An
indexed view can never be a goal in itself; it can only be part of a way
to achieve some other goal. If you can elaborate on that goal, I can
help you find other, maybe even better ways to achieve the same goal.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)