help with UNION SQL

help with UNION SQL

Post by steelbh.. » Tue, 18 May 1999 04:00:00



I need help with data pulled from an Excel Spreadsheet that I need to
write in one column.  I am using ADO to connect to the excel sheet and
the data looks like this

Cost_Name          Account_detail

Expense       123 Car expense
Expense       345 Travel expense
Salary        567 Salary and Wages

I want to be able to return a single list that looks like

Expense
 123 Car Expense
 345 Travel Expense
Salary
 567 Salary and Wages

etc..

Sql i used was
select Cost_Name from Table
union
select Account_detail from Table

but it isnt ordered the way I need and I guess I dont understand the
nature of the UNION or how to tell it to order the way I need to do it.
Anyone out want to take a crack at it?  Thanks in advance

Asim Razvi
DataintoAction Corp

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

 
 
 

1. Need help with sql script using UNION

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)

2. version 7

3. SQL UNION Beginners Help

4. Problems installing 8i on NT

5. HELP: Unions in Delphi SQL

6. sql.iem??

7. SQL UNION HELP

8. Runtime SQL Server.

9. PL/SQL UNION ERROR : PLEASE HELP

10. sql query (UNION) help!!! Thanks

11. Help...SQL UNION Error !!!

12. a SQL Statement/Union Help

13. Diff. between UNION and UNION ALL