Invalid Subquery in VFP 8.0 1

[FoxAmateur]

Does anyone know why the following code generates an invalid subquery error in VFP 8.0?

SELECT * from tableA
WHERE phone in (SELECT phone, count(phone) as count FROM tableA GROUP BY phone HAVING count > 1)

I am trying to create a cursor of records where the phone number appears in tableA more than once.

Chris

 

[Mike Gagnon]

FoxAmateur

The SQL standards have tightened up with VFP8.0. Try using

SET ENGINEBEHAVIOR 70

Making your code work like it would have in VFP7.0. If you code work, take a look at the help file under SET ENGINEBEHAVIOR, it expalins the differences in the standards.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[FoxAmateur]

Mike,

That didn't work. I guess you can't use a having clause in a subquery.

Chris

 

[Stella740pl]

From my previous experience, I believe that you can't have 2 fields selected in a subquery when you use it with IN. Basically, FoxPro is confused on which column to search. You might have to preselect your subquery.

 

[Mike Lewis]

Chris,

Stella is right. Your subquery is returning to two columns, but the IN operator only works with singe columns.

The easiest solution would be to rewrite the whole thing using a join instead of a subquery.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MarciaAkins]

Hi Chris

I am trying to create a cursor of records where the phone number appears in tableA more than once.

You do not need the "IN" part of your query to do this:

SELECT TABLEA.Phone, COUNT( TABLEA.Phone ) AS PhoneCount FROM TABLEA GROUP BY Phone HAVING PhoneCount > 1 INTO CURSOR DupPhones

Marcia G. Akins

http://www.TightlineComputers.com

 

[FoxAmateur]

Stella740pl, Mike & Marcia - Thanks I forgot you can't return more than one column in a subquery using "IN".

I have a work around but I wanted to make the following code 1 SQL statement instead of 2.

SELECT phone, count(phone) as count ;
    FROM tableA ;
    GROUP BY phone ;
    HAVING count > 1 ;
    INTO TABLE phoncnt

SELECT a.* ;
    FROM tableA a ;
    JOIN phoncnt ON phoncnt.phone = a.phone ;
    ORDER BY zip, phone ;
    INTO CURSOR phone_detail

Chris

 

[Stella740pl]

I have a work around but I wanted to make the following code 1 SQL statement instead of 2.

I might be missing something, but didn't Marcia just showed you how to do it in 1 statement?

 

[FoxAmateur]

I might be missing something, but didn't Marcia just showed you how to do it in 1 statement?

Marcia's query returns only the phone and the count. I need to return the entire record from tableA (name, address, phone, account number, etc.)

 

[Stella740pl]

So modify her query for yourself. It was just an idea for you to use.

Say, SELECT *, COUNT(*) ... instead of SELECT phone ... Isn't it how it's done?

 

[FoxAmateur]

Unfortunately I need all occurrences of the phone number because there is different information on each record (i.e. time of each call, reason for call, etc.).

Thanks for all your help.

Chris

 

[Stella740pl]

Unfortunately I need all occurrences of the phone number because there is different information on each record

Oh, in this case of course you will need 2 statements. It looked like you were trying to find all duplicates.

 

[craigsboyd]

FoxAmateur,

If you have a primary ID field in your table you can do the following to get what you want:

SELECT a.phone, a.time, a.reason FROM MyTable a WHERE exists (select * FROM MyTable b WHERE b.phone = a.phone AND b.primaryid != a.primaryid) ORDER BY a.phone

...if a duplicate phone number exists it will be included.

http://www.sweetpotatosoftware.com

 

[Stella740pl]

FoxAmateur,

I just realized going through another thread that you can do it in one statement:

SELECT * ;
   FROM tableA ;
   WHERE phone IN ;
      (SELECT phone ;
      FROM tableA ;
      GROUP BY phone ;
      HAVING Count(Phone)>1)

The main idea is, you don't have to SELECT Count(phone) in order to filter on it.

 

[FoxAmateur]

Stella740pl,

Thank you so much! That is what I was looking for. I can't wait to go to work tomorrow and try it out.

Chris