Board index » delphi » Need help with UNION statement

Need help with UNION statement

Who would like to help with the UNION statement.

The situation:
 I have two tables - one with orders, which have already been carried
 out and one with the actual ones (o_order and a_order). In both
 there are fields with the number of the order and the number of the
 articel (order_nr and art_nr). In o_order there are also fields with
 the date we told the customer he can get his articel and a field
 about when the articel is carried out by us. In a_order the last
 field  is missing and there is also a field with the current state
 of the order.

The problem:
 I tried to display fields from both the o_order and the a_order in
 one dbgrid. It displayed the order_nr twice. So I tried to union the
 order_nr, but it did not work. I recieved a "not supported" error.

The statement:
 SELECT o.*
   FROM "o_order.db" o
   WHERE some_restrictions
   UNION SELECT a.* FROM "a_order.db" a WHERE some_restrictions;

In the manual there is no example using union nor a description of
the syntax.
Many thanks in advanced.

Thomas

 

Re:Need help with UNION statement


On 10 Jun 1996 10:43:33 GMT, t...@informatik.tu-chemnitz.de (Thomas

Quote
Szcs) wrote:
>Who would like to help with the UNION statement.
[...]
>The problem:
> I tried to display fields from both the o_order and the a_order in
> one dbgrid. It displayed the order_nr twice. So I tried to union the
> order_nr, but it did not work. I recieved a "not supported" error.
[...]
>In the manual there is no example using union nor a description of
>the syntax.

As far as I know (and that is little), Local SQL (i.e. Paradox) does
NOT support UNION statements.

I would love to be proved wrong, though, as I am having a similar
problem...
--
Stefan Hoffmeister  (Stefan.Hoffmeis...@Uni-Passau.de)
University of Passau, Bavaria, Germany

Re:Need help with UNION statement


Quote
t...@informatik.tu-chemnitz.de (Thomas Szcs) wrote:
>Who would like to help with the UNION statement.
>The situation:
> I have two tables - one with orders, which have already been carried
> out and one with the actual ones (o_order and a_order). In both
> there are fields with the number of the order and the number of the
> articel (order_nr and art_nr). In o_order there are also fields with
> the date we told the customer he can get his articel and a field
> about when the articel is carried out by us. In a_order the last
> field  is missing and there is also a field with the current state
> of the order.
>The problem:
> I tried to display fields from both the o_order and the a_order in
> one dbgrid. It displayed the order_nr twice. So I tried to union the
> order_nr, but it did not work. I recieved a "not supported" error.
>The statement:
> SELECT o.*
>   FROM "o_order.db" o
>   WHERE some_restrictions
>   UNION SELECT a.* FROM "a_order.db" a WHERE some_restrictions;

It seems the BDE doesn't support UNION.  TQuery only works with UNION
when it's used again SQL Server table (it appears the BDE just pass
the sql statement to Interbase and let Interbase do the job).

Even if you are using it against SQL table, please make sure :

1.  The select statements are requesting SAME no of fields with
COMPATIBLE  field types  from the tables.  (Some one name it UNION
COMPATIBLE in the textbook)

2.  if you don't mind duplicates in the result sets, UNION ALL is
better since it would not sort/order the intermediate result sets and
perform MUCH faster if you union a few more tables.

Quote
>In the manual there is no example using union nor a description of
>the syntax.

I learn a lot about SQL statements from the book
"SQL for Smarties : Advanced SQL Programming" by Joe Celko.
Publisher : Morgan Kaufmann Publishers.

You may surf to their web page and place the order on line. (I do it
this way)
I learn a lot about SQL statements and SQL table behaviour from it.
Highly recommended.

BTW.  : I have no interest in Morgan Kaufmann nor do I know Mr Joe
Celko.

Best Regards,

Martin Fu

Re:Need help with UNION statement


In article <4pl62h$...@bull.hkstar.net>, marti...@hkstar.com says...

Quote
>t...@informatik.tu-chemnitz.de (Thomas Szcs) wrote:
>>Who would like to help with the UNION statement.
>>The statement:
>> SELECT o.*
>>   FROM "o_order.db" o
>>   WHERE some_restrictions
>>   UNION SELECT a.* FROM "a_order.db" a WHERE some_restrictions;
>It seems the BDE doesn't support UNION.  TQuery only works with UNION
>when it's used again SQL Server table (it appears the BDE just pass
>the sql statement to Interbase and let Interbase do the job).
>Even if you are using it against SQL table, please make sure :
>1.  The select statements are requesting SAME no of fields with
>COMPATIBLE  field types  from the tables.  (Some one name it UNION
>COMPATIBLE in the textbook)

I tried it with two test-tables. Each table had only one field. The types
were compatible (in fact they were the same, because I duplicated a table to
get the other one). UNION did not work.

Quote
>I learn a lot about SQL statements from the book
>"SQL for Smarties : Advanced SQL Programming" by Joe Celko.
>Publisher : Morgan Kaufmann Publishers.

Thanks I will read it.

Quote
>BTW.  : I have no interest in Morgan Kaufmann nor do I know Mr Joe
>Celko.

;)

Best regards,

Thomas

Re:Need help with UNION statement


Quote
Thomas Szcs wrote:

> Who would like to help with the UNION statement.

> The situation:
>  I have two tables - one with orders, which have already been carried
>  out and one with the actual ones (o_order and a_order). In both
>  there are fields with the number of the order and the number of the
>  articel (order_nr and art_nr). In o_order there are also fields with
>  the date we told the customer he can get his articel and a field
>  about when the articel is carried out by us. In a_order the last
>  field  is missing and there is also a field with the current state
>  of the order.

I am guessing that the problem is a mismatch in column types between
o_order and a_order:  In a UNION, the column select lists of each
select statement must match in type.  If you need columns from one
table which are not in the other, you have to supply a literal value
in its place, e.g.:

  select order_nr, art_nr, avail_date, actual_date, 0
  from o_order
  UNION
  select order_nr, art_nr, avail_date, '1900-01-01.5', state
  from a_order

I wouldn't swear to my date format being correct in this example, but hopefully you
get the idea.

I have assumed 'state' to be numeric, and subsituted zero for it in the o_order
query.

NOTE: Correlation names aren't necessary in this example since each query in the
union is treated individually by the RDBMS (or at least that is the case with ours -
SQLBase) so there is no ambiguity with the column names.

Hope this helps.
--
Jolyon Smith                         |  The views represented above are my own
Application Development Consultant   |  and do not necessarily represent the
PLATINUM Solutions (UK)              |  views of my employer.

Re:Need help with UNION statement


In A SQL Union statement you must have the same number of fields
in both data sets but the fields don't have to be the same size
or data type. However if the fields are'nt exactly matched the
results will probably be meaningless.
This is true for Access SQL.

In your case you may have to specify all the common fields
explicitly instead of using .*.

Hope this helps.
Colin

Re:Need help with UNION statement


Quote
t...@informatik.tu-chemnitz.de (Thomas Szcs) wrote:
>Who would like to help with the UNION statement.
>The situation:
> I have two tables - one with orders, which have already been carried
> out and one with the actual ones (o_order and a_order). In both
> there are fields with the number of the order and the number of the
> articel (order_nr and art_nr). In o_order there are also fields with
> the date we told the customer he can get his articel and a field
> about when the articel is carried out by us. In a_order the last
> field  is missing and there is also a field with the current state
> of the order.
>The problem:
> I tried to display fields from both the o_order and the a_order in
> one dbgrid. It displayed the order_nr twice. So I tried to union the
> order_nr, but it did not work. I recieved a "not supported" error.
>The statement:
> SELECT o.*
>   FROM "o_order.db" o
>   WHERE some_restrictions
>   UNION SELECT a.* FROM "a_order.db" a WHERE some_restrictions;

Thomas doesn't need a Union statement.  Union is rarely needed and
certainly not in his case.  H is trying to Join two tables and
suppress displaying the matching column twice.  It sounds like he is
doing something like this:

Select *  
from "o_order.db" o, "a_order.db" a
where a.order_nr = o.order_nr

Since order_nr appears in both tables it naturally appears in the
result set of the select twice.  The solution is to limit the result
set by not using Select * but rather using Select only on the
interested columns or instantiating all the columns and changing the
Visible property  of one of the order_nr to False.
-------------------------------
Thomas Paul
TOMG...@Pipeline.Com
Dean Witter, Discover & Co.
New York City, NY USA
----------------------------------

Other Threads