SQL Anywhere database - Creating a special view

Author

Creating a special view

Pavel Karady

2005-12-20, 9:23 am

Hi experts,

ASA 7.0.4: I'd like to create a view, that will return such rows from table
"table1", which primary key values exists in "table2".

Table 1 (t1col1 PK, t1col2 PK, t1col3, t1col4, t1col5, t1col6);
Table 2 (t2col1, t2col2 (EVA t1col1), t2col3, t2col4 (EVA t1col2), t2col5,
t2col6 PK);

EVA means 'EXACT VALUE AS'. There is no foreign key reference between these
two tables.

I want to get the following VIEW:

CREATE VIEW myview AS
SELECT t1.* FROM "table1", "table2"
WHERE table1.t1col1 = table2.t2col2
AND table1.t1col2 = table2.t2col4

This does exactly what's stated in the first no-hello line of my post. This
view is fine; but it is intended for deleting rows, and since DELETE works
only on views that have exactly one table mentioned in the FROM clause, it's
unusable.

I have not found a way (yet) how to keep one table in the FROM clause but
still be able to match rows by more than 1 column (= when more than 1 column
needs to have the 'EXACT VALUE AS'). I've tried the following:

CREATE VIEW myview AS
SELECT * FROM "table1"
WHERE STRING(t1col1 || '-' || t1col2) IN (SELECT STRING(t2col2 || '-' ||
t2col4) FROM "table2")

but this solution takes an almost infinite time to complete, so it's
unusable.

I will remember any, ANY suggestions for the rest of my life.

Pavel

Michael Haensch

2005-12-20, 9:23 am

Hi Pavel,

what about :

CREATE VIEW myview AS
SELECT * FROM "table1" t1
WHERE exists (select * from "Table2" t2 where t1.t1col1 = t2.t2col2
and t1.col2 = t2.t2col4)

Michael

Pavel Karady schrieb:
> Hi experts,
>
> ASA 7.0.4: I'd like to create a view, that will return such rows from table
> "table1", which primary key values exists in "table2".
>
> Table 1 (t1col1 PK, t1col2 PK, t1col3, t1col4, t1col5, t1col6);
> Table 2 (t2col1, t2col2 (EVA t1col1), t2col3, t2col4 (EVA t1col2), t2col5,
> t2col6 PK);
>
> EVA means 'EXACT VALUE AS'. There is no foreign key reference between these
> two tables.
>
> I want to get the following VIEW:
>
> CREATE VIEW myview AS
> SELECT t1.* FROM "table1", "table2"
> WHERE table1.t1col1 = table2.t2col2
> AND table1.t1col2 = table2.t2col4
>
> This does exactly what's stated in the first no-hello line of my post. This
> view is fine; but it is intended for deleting rows, and since DELETE works
> only on views that have exactly one table mentioned in the FROM clause, it's
> unusable.
>
> I have not found a way (yet) how to keep one table in the FROM clause but
> still be able to match rows by more than 1 column (= when more than 1 column
> needs to have the 'EXACT VALUE AS'). I've tried the following:
>
> CREATE VIEW myview AS
> SELECT * FROM "table1"
> WHERE STRING(t1col1 || '-' || t1col2) IN (SELECT STRING(t2col2 || '-' ||
> t2col4) FROM "table2")
>
> but this solution takes an almost infinite time to complete, so it's
> unusable.
>
> I will remember any, ANY suggestions for the rest of my life.
>
> Pavel
>
>

Paul Horan[TeamSybase]

2005-12-20, 9:23 am

Try using the EXISTS clause:

CREATE VIEW myview AS
SELECT t1.* FROM "table1" T1
WHERE EXISTS (
SELECT * from "table2" T2
WHERE T1.t1col1 = T2.t2col2
AND T1.t1col2 = T2.t2col4 ) ;

(Note: it's never a good idea to define a view with wildcards in the select
list. Even though it's more typing, explicitly reference each column that
you'll need)

Paul Horan[TeamSybase]

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43a8166f$1@foru
ms-1-dub...
> Hi experts,
>
> ASA 7.0.4: I'd like to create a view, that will return such rows from
> table "table1", which primary key values exists in "table2".
>
> Table 1 (t1col1 PK, t1col2 PK, t1col3, t1col4, t1col5, t1col6);
> Table 2 (t2col1, t2col2 (EVA t1col1), t2col3, t2col4 (EVA t1col2), t2col5,
> t2col6 PK);
>
> EVA means 'EXACT VALUE AS'. There is no foreign key reference between
> these two tables.
>
> I want to get the following VIEW:
>
> CREATE VIEW myview AS
> SELECT t1.* FROM "table1", "table2"
> WHERE table1.t1col1 = table2.t2col2
> AND table1.t1col2 = table2.t2col4
>
> This does exactly what's stated in the first no-hello line of my post.
> This view is fine; but it is intended for deleting rows, and since DELETE
> works only on views that have exactly one table mentioned in the FROM
> clause, it's unusable.
>
> I have not found a way (yet) how to keep one table in the FROM clause but
> still be able to match rows by more than 1 column (= when more than 1
> column needs to have the 'EXACT VALUE AS'). I've tried the following:
>
> CREATE VIEW myview AS
> SELECT * FROM "table1"
> WHERE STRING(t1col1 || '-' || t1col2) IN (SELECT STRING(t2col2 || '-' ||
> t2col4) FROM "table2")
>
> but this solution takes an almost infinite time to complete, so it's
> unusable.
>
> I will remember any, ANY suggestions for the rest of my life.
>
> Pavel
>

Glenn Paulley

2005-12-20, 9:23 am

You have two options:

- use the TSQL form of the DELETE statement that permits *two* FROM
clauses - one to identify the table whose rows are to be deleted, and the
other to define the complete join table expression. Be very careful of
how you write this DELETE statement so that it does what you want it to.

- Rewrite the (inner) join as a nested query with the subquery containing
outer references, eg

CREATE VIEW myview AS
SELECT t1.* FROM "table1"
WHERE EXISTS( SELECT * FROM "table2"
WHERE table1.t1col1 = table2.t2col2
AND table1.t1col2 = table2.t2col4 )

Glenn

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in
news:43a8166f$1@foru
ms-1-dub:

> Hi experts,
>
> ASA 7.0.4: I'd like to create a view, that will return such rows from
> table "table1", which primary key values exists in "table2".
>
> Table 1 (t1col1 PK, t1col2 PK, t1col3, t1col4, t1col5, t1col6);
> Table 2 (t2col1, t2col2 (EVA t1col1), t2col3, t2col4 (EVA t1col2),
> t2col5, t2col6 PK);
>
> EVA means 'EXACT VALUE AS'. There is no foreign key reference between
> these two tables.
>
> I want to get the following VIEW:
>
> CREATE VIEW myview AS
> SELECT t1.* FROM "table1", "table2"
> WHERE table1.t1col1 = table2.t2col2
> AND table1.t1col2 = table2.t2col4
>
> This does exactly what's stated in the first no-hello line of my post.
> This view is fine; but it is intended for deleting rows, and since
> DELETE works only on views that have exactly one table mentioned in
> the FROM clause, it's unusable.
>
> I have not found a way (yet) how to keep one table in the FROM clause
> but still be able to match rows by more than 1 column (= when more
> than 1 column needs to have the 'EXACT VALUE AS'). I've tried the
> following:
>
> CREATE VIEW myview AS
> SELECT * FROM "table1"
> WHERE STRING(t1col1 || '-' || t1col2) IN (SELECT STRING(t2col2 ||
> '-' ||
> t2col4) FROM "table2")
>
> but this solution takes an almost infinite time to complete, so it's
> unusable.
>
> I will remember any, ANY suggestions for the rest of my life.
>
> Pavel
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Pavel Karady

2005-12-20, 9:23 am

Thank you Paul and Glenn,

your way of creating the view using EXISTS clause has proven to be a
success.

> Paul wrote:
> (Note: it's never a good idea to define a view with wildcards in the
> select
> list. Even though it's more typing, explicitly reference each column that
> you'll need)

I agree.

Thank you very much again,

Pavel

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns97326553A457
paulleyianywherecom@
10.22.241.106...
> You have two options:
>
> - use the TSQL form of the DELETE statement that permits *two* FROM
> clauses - one to identify the table whose rows are to be deleted, and the
> other to define the complete join table expression. Be very careful of
> how you write this DELETE statement so that it does what you want it to.
>
> - Rewrite the (inner) join as a nested query with the subquery containing
> outer references, eg
>
> CREATE VIEW myview AS
> SELECT t1.* FROM "table1"
> WHERE EXISTS( SELECT * FROM "table2"
> WHERE table1.t1col1 = table2.t2col2
> AND table1.t1col2 = table2.t2col4 )
>
> Glenn
>
>
> "Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in
> news:43a8166f$1@foru
ms-1-dub:
>
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Sponsored Links