Problem with UNION in SQL-Select 1

[SGLong]

This very simple SQL-Select clause is giving me a "Command is missing required clause" error. I've compared my command to the help file for construction but nothing jumps out as being missing. Does anybody know what I'm doing wrong?

cFnCode = 'A1'   && Bank account code reference
SELECT SPACE(13) as codeval, 'BLANK for ALL' as descript ;
	UNION select subaccount AS codeval, acctdesc AS descript ;
		FROM cashacct ;
			WHERE fncode=cFnCode ;
	ORDER BY codeval ASC ;
	INTO CURSOR cur_subacct

BTW, this is for VFP 7

Steve

 

[Mike Lewis]

Steve,

The "missing clause" is your FROM clause in the first SELECT.

You need something like this:

cFnCode = 'A1'   && Bank account code reference
SELECT SPACE(13) as codeval, 'BLANK for ALL' as descript ;
    [b]FROM SomeTable ;[/b]
    UNION select subaccount AS codeval, acctdesc AS descript ;
        FROM cashacct ;
            WHERE fncode=cFnCode ;
    ORDER BY codeval ASC ;
    INTO CURSOR cur_subacct

Give it a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SGLong]

Thanks Mike... Since the first SELECT clause was not based upon information in a table I didn't think that the "FROM" clause was necessary. I added a reference to one of our other tables and DISTINCTs and it came up with exactly what I wanted.

Steve

 

[Mike Lewis]

Steve,

Glad it worked.

I didn't think that the "FROM" clause was necessary.

That would be true in SQL Server. But VFP is more fussy.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk