>>>I have a two select statements that have the same number of columns and the columns are the same data type and length. In the first select I group by one column and use the sum function on a couple of fields. I then use a union select ie select * from first cursor union select * from second cursor. I get the error - selects are not union compatible. I don't understand why they arn't compatible. I don't use group by in the union select statement so the number of columns and their data types and lengths match so what is wrong? Any ideas would be much appreciated. Thanks.
>>>
>>>Rhea Urusky
>>>rheau@beaverplastics.com
>>Can you post some code? The restriction is that the result sets, not the source tables should have the same number of columns and data types.
>
>I have a select statement that sums a numeric field as one of the returned columns and the second select statement just returns a numeric field in the same column. I then select * from the first select union select * from the second select. If I remove the column that is summed in the first select and not in the second one the union select works. Is a summed numeric field not a numeric field anymore? I don't understand why the two columns are not compatible. For example:
>
>Select S.date, SUM(S.count) From Sample S Into Cursor One;
>Select E.date, E.number From Example E Into Cursor Two;
>Select * From One;
>Union;
>Select * From Two;
>Into Cursor Three
>Count and Number fields are both numeric fields. I am trying to retrieve records from a parent/child table with records from a table which doesn't have a child table.

First, you need to encapsulate the SELECT for the UNION clause within parentheses. Second, why do this in two steps?

Why not:

SELECT s.Date AS DateFld, SUM(S.Count) AS SumNum ;
  FROM SAMPLE S ;
  INTO CURSOR Three ;
UNION ALL (SELECT E.Date AS DateFld, E.Number AS SumNum ;
  FROM Example E )

I renamed fields because I'm allergic to using reserved names for fields - I'd avoid naming fields things like Count, Date, Number and the like to avoid confusing the interpreter, and avoid problems when going to another environment where the DML is pickier about reserved word collisions than VFP.