Need help with sql script using UNION

Need help with sql script using UNION

Post by Niloufar Lame » Sun, 22 Feb 1998 04:00:00



I have a table that contains all the files that are on my
machine (inst_file) and another table (package_desc) that
describes software packages (e.g., Microsoft Word 7.0 contains
word.exe, word.dll, word.txt, I'll have three rows in this
table for package Microsoft Word 7.0).  Now, my goal is to
find out if this package exist on my machine by looking at
these two tables.

1- I have a view that joins these two tables and matches
   all the files that I have installed on the machine with
   all the files that exist in my package. (inst_file_view)
2- I now need to find if the package "Microsoft Word 7.0" is
   installed on my machine.  In order to do so, I create two
   more views (inst_comp_view and find_sw_view) that are described
   below at the end of the message. Col1, Col2 are attributes
   like file size, file name, package name, package version, that
   are used in the where clauses.
   I've been using the above views in Oracle and it works fine, but
   when I try to create view "inst_comp_view" in MS SQL I get the
   following errors:

   Msg 209, Level 16, State 1
   Ambiguous column name col1
   Msg 209, Level 16, State 1
   Ambiguous column name col2
      ...
   Msg 206, Level 16, State1
   Operand type clash: UNKNOWN TOKEN is incompatible with varchar
   Msg 206, Level 16, State1
   Operand type clash: UNKNOWN TOKEN is incompatible with int

I decomposed inst_comp_view even further and created a view that is
a union of two views (one view for each select statemnt).  This works
just fine and gives me the right result.  However, when I get to run
"select * from find_sw_view" I get:

The query and the views in it exceed the limit of 16 tables.

I'll appreciate any help.  I'd like to know why I can't create inst_comp_view
and also please let me know if you have a better approach to solve this problem.
Of course, this could easily be solved using a procedural language like PL/SQL
or T-SQL, but we're trying to see if we can solve it using just standard SQL.

Thanks in advance,
Niloufar

(inst_comp_view)
create view inst_comp_view as
select col1, col2, ..., 'T' FOUND from package_desc, inst_file_view
union
select col1, col2, ..., 'F' FOUND from package_desc, inst_file_view
where not exists (select 'X' from inst_file_view
                   where package_desc.col1 = inst_file_view.col1
                     and package_desc.col2 = inst_fiel_view.col2)

(find_sw_view as)

select distinct col1, col2, col3,... from inst_comp_view icv
where not exists (select 'X' from inst_comp_view icv1
                   where icv1.col1 = icv.col1
                     and icv1.col2 = icv.col2)