IF and CASE statement help 4

[maggielady]

I have a table that has an id # and a group description. The id # could be in there up to 148 times. I need to figure out a way to get, if the id# is in the table more than once with different group descriptions, into a field called church activites. Not sure how to get it to look at each record, and if the id# is found more than once, add that record to the church activities field. Here is my code to add church activities:

This puts things in one at a time, I need to put at least 148 possiblities in.
This is the way I started, with IF statements:
SELECT 25
SEEK(nperno)
DO while nperno=val(MBRID) .and. .not. eof()
IF nperno=VAL(MBRID)
CCHACTIV1=ALLTRIM(name) Skip
IF nperno=val(MBRID)
CCHACTIV2=ALLTRIM(name) Skip
And so on and on and on then I do this procedure to put in the the correct table:

PROCEDURE skchactiv
PARAMETERS cx1

if .not. isblank(cx1)
do lookfill with cx1,"CHACTIVITY",lastcode,lupkey
select 4
append blank
replace person_id with tperid
replace table_name with "CHACTIVITY"
replace table_id with lastcode
select 1
endif
Is there a better way the using IF or CASE statements 148 times??

 

[JimWWinter]

First, I would recommend using table aliases instead of work areas - its just as fast and makes your code much more readable.

So, if you're trying to move data from a table called ChActivity to a table called Activity, you might do something like the following:

SELECT ChActivity
SEEK nPerNo
SCAN WHILE nPerNo = VAL(m.MBrid)
* Not sure what this code does but I'm assuming it gets some variables for you
DO LookFill WITH cx1,"CHACTIVITY",LastCode,LUpKey
INSERT INTO Activity (Person_id, Table_Name, Table_ID) ;
VALUES (TPerid, "CHACTIVITY", LastCode)
ENDSCAN

HTH,
Jim

 

[Mike Lewis]

MaggieLady,

Not sure if I understand what you are trying to achieve, but I wonder if a SQL SELECT would be a good way to go.

Something like this:

SELECT ID, COUNT(ID) FROM MyTable GROUP BY ID HAVING COUNT(ID) > 1 INTO CURSOR Temp

That will give you a temporary cursor containing all the duplicated IDs. Each ID will appear in the cursor just once.

I'n not sure what you want to do with those duplicate records, but it should be simple enough to loop through the cursor, performing some action on each ID. Perhaps like this:

SELECT Temp
SCAN
SELECT MyTable
LOCATE FOR MyTable.ID = Temp.ID
* do whatever you want to do here
SELECT Temp
ENDSCAN

My apologies if I've misunderstood the requirements.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Stella740pl]

You don't need all these loops and cases, as far as I understand your task!

SELECT-SQL can do it.
I just didn't understand, the church activities field is in the same field or in a different one?

Let's assume it's in the same table.

SELECT ID, GroupDesc ;
   FROM PeopleTbl ;
   GROUP BY 1,2 ;
   INTO CURSOR Tmp 

* You grouped it on ID and Group Description 
* to make sure you selected only unique combinations

SELECT ID ;
   FROM Tmp ;
   GROUP BY 1 ;
   HAVING COUNT(*)>1 ;
   INTO ARRAY ActID 

* Now you selected only those IDs that have more than one activity

SELECT PeopleTbl
REPLACE ALL ChurchAct WITH "CHACTIVITY" FOR ASCAN(ActID,ID)>0

If you need to put the activities into a different table, then you can do it like this:

SELECT ID, GroupDesc ;
   FROM PeopleTbl ;
   GROUP BY 1,2 ;
   INTO CURSOR Tmp

SELECT ID, "CHACTIVITY" AS ChurchAct ;
   FROM Tmp ;
   GROUP BY 1 ;
   ORDER BY 1 ;
   HAVING COUNT(*)>1 ;
   INTO DBF ChActivity

Modify as needed.

 

[maggielady]

Jim,
The code you gave me works but it puts the same description in the table as many times as the personid matches, it doesn't change the description associated with the next instance of the person id #. Trying to figure out how to make it change the description each time???
IE:
Person id group name
1 Greeter
1 Host
1 Money counter
1 Crazy 8's

it puts the person id in 4 times but with Greeter for each one. Hope you understand this, and thanks for all the help everyone!!

 

[TamarGranor]

Can you give us a small example of the original data and the result you're looking for?

Tamar

 

[maggielady]

THANK you guys so much, I got the results I was looking for, just put a skip in and it works GREAT!! Thanks!!