SQL Statement confusion 1

[Scott24x7]

Hi All,
Trying to do something that I perceive as "simple" but I can't get the syntax in the SQL statement to work.
So, I have two tables, which have name values, but different field names, and in different tables. What I want to achieve is a single cursor that has the contents (distinctly) in one common field (called TESTNAMES). So my attempted syntax looks like this:

 SELECT DISTINCT CONTACTFIRSTNAME, UNION SELECT DISTINCT FNAME FROM CONTACT, CTFNAMES INTO CURSOR TESTNAMES

I had also tried:

 SELECT DISTINCT CONTACTFIRSTNAME, FNAME FROM CONTACT, CTFNAMES INTO CURSOR TESTNAMES

The first example fails, the second example gives me the cursor named TESTNAMES, but two columns, where first column has all the distinct values from CONTACT repeated with the second column that has the distinct values from CONTACT.

What am I doing wrong here?



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

The syntax in your first example is incorrect. When you use a UNION, each part of the statement must be a syntactically-correct SELECT in its own right. In particular, it must have its own FROM clause. So the following would be correct:

SELECT DISTINCT ContactFirstName FROM Contact ;
  UNION SELECT Fname FROM CtfNames ;
  INTO CURSOR etc.

However, just because that has the correct the syntax, I can't say for sure that it will do what you want, as I am not certain I understand what you are trying to achieve. But give it a try anyway.

As for your second example, that definitely won't work, Where you have multiple table names in the FROM clause, but without a WHERE condition, you will get a cartesian join, which I feel sure is not what you want.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Mike,
Awesome, that was it... I made a quick adjustment to UPPER() both of the clauses so that capitalization issues didn't result in two results the same. That worked perfect after that!
Many thanks.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Glad it worked, Scott. Thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Yeah, I added the AS TESTNAMES after each of the two FROM clauses so that the cursor combined it to a single field name (instead of Exp_1 as default returned), and then didn't have to modify any of my downstream code as the field names then matched. So in all it worked perfectly.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."