UNION in SQL with VFP7?
Gasman
Trying to create a table from data in an Excel spreadsheet. Data
cannot be appended as each column of data represents a row in a table
with other data.
I have tried to create a query that will select each column in turn
and place into cursor, this I will then load to the table, however all
my UNION syntax fails.
I've not used VFP7 for quite a while now, but sure I have done this in
the past, but for the life of me cannot see what is wrong.?
Initially I was trying to access the same table that held the excel
data then created 4 tables each with one column of Excel data in it.
This is where I have got to at the moment,
Googling I see code with ';' and without, neither work in my case.
Could someone please put me out of my misery and tell me what I am
missing.?
TIA
[code]
SELECT "DSRM" as rate, A as Age, ROUND(VAL(B),3) as &FieldPop FROM t1
UNION
SELECT "DSM" as rate, A as Age, ROUND(VAL(C,3) as &FieldPop FROM t2
UNION
SELECT "DSRF" as rate, A as Age, ROUND(VAL(F),3) as &FieldPop FROM t3
UNION
SELECT "DSF" as rate, A as Age, ROUND(VAL(G),3) as &FieldPop FROM t4
INTO CURSOR test
[/code]
Fieldpop = "M12"
Bernhard Sander
> Trying to create a table from data in an Excel spreadsheet. Data
> cannot be appended as each column of data represents a row in a table
> with other data.
>
> I have tried to create a query that will select each column in turn
> and place into cursor, this I will then load to the table, however all
> my UNION syntax fails.
>
> I've not used VFP7 for quite a while now, but sure I have done this in
> the past, but for the life of me cannot see what is wrong.?
>
> Initially I was trying to access the same table that held the excel
> data then created 4 tables each with one column of Excel data in it.
> This is where I have got to at the moment,
>
> Googling I see code with ';' and without, neither work in my case.
>
> Could someone please put me out of my misery and tell me what I am
> missing.?
What exactly is your problem?
Do you get some syntax error?
Or do you get a result other than you expected?
About the ";": in Foxpro it means the command will continue in the next line. So
you should add a ";" at every line of your code.
Pay attention: merely add a " ;" otherwise the fox will see something like
t1UNIONSELECT, this he cannot really understand.
> [code]
>
> SELECT "DSRM" as rate, A as Age, ROUND(VAL(B),3) as &FieldPop FROM t1
> UNION
> SELECT "DSM" as rate, A as Age, ROUND(VAL(C,3) as &FieldPop FROM t2
> UNION
> SELECT "DSRF" as rate, A as Age, ROUND(VAL(F),3) as &FieldPop FROM t3
> UNION
> SELECT "DSF" as rate, A as Age, ROUND(VAL(G),3) as &FieldPop FROM t4
> INTO CURSOR test
>
> [/code]
>
> Fieldpop = "M12"
Regards
Bernhard Sander
Gasman
Firstly thank you for the reply. I did not think these groups were
working anymore. The last post seemed so old and I could see no sign
of my original post, hence the test postings.
I've just basically rewritten it by reducing the number of fields and
then adding them, then removing the hard coded names for parameters
and functions and the damn thing works now. I have NO idea as to what
was wrong before and was even posting the error messages here before i
decided to do that.
Anyway, it is working now and the main thing is the syntax is how I
remember it. I do have ';' on the end of all lines but the last as
well.
Thank you for your reply and I'm glad these groups are still active.
Gasman
SELECT "DSRM" as rate, A as Age, ROUND(VAL(B),3) AS &FieldPop FROM
vfp5 ;
UNION ;
SELECT "DSM" as rate, A as Age, ROUND(VAL(C),3) AS &FieldPop FROM
vfp5 ;
UNION ;
SELECT "DSRF" as rate, A as Age, ROUND(VAL(F),3) AS &FieldPop FROM
vfp5 ;
UNION ;
SELECT "DSF" as rate, A as Age, ROUND(VAL(G),3) AS &FieldPop FROM vfp5
INTO CURSOR c1
It also works without the INTO clause.