help on cursor

[angelleynes]

Hello,

I need help on how create a cursor base on values example:
table1.record has 4 records and i want to create a cursor to have fields names based on the records, like record1 becomes a field name on the new cursor then record2 being the second field name on the new cursor and so on.

thanks!

 

[jimoo]

Here is one way, but check out ramani's home page because he has nice samples that may assist you with an Xtable.

Just copy this code into a prg and run it.


CREATE CURSOR boo (MyId C(4))

APPEND BLANK
REPLACE MyID WITH "0001"
APPEND BLANK
REPLACE MyID WITH "0002"
APPEND BLANK
REPLACE MyID WITH "0003"
APPEND BLANK
REPLACE MyID WITH "0004"

lcCommand = "CREATE CURSOR hoo ("

SELECT boo
COUNT TO lnTotal && RECCOUNT() if no deleted records
GO TOP

lnCtr = 0
DO WHILE NOT EOF()
lnCtr = lnCtr + 1
lcCommand = lcCommand + "F" + boo.myID + " C(4)"
IF lnCtr < lnTotal
lcCommand = lcCommand + &quot;, &quot;
ELSE
lcCommand = lcCommand + &quot&quot;
ENDIF
SKIP
ENDDO

&lcCommand
BROW
MESSAGEBOX(lcCommand)


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[baltman]

This code is a bit hacked together, but should do what you're asking for, and maybe more...

Brian

&&&Test Table
CREATE TABLE test (field1 C(12), field2 n(3), field3 c(1),field88 b)
FOR x = 1 TO 20
APPEND BLANK
ENDFOR
REPLACE ALL field1 WITH &quot;Field&quot;+ALLTRIM(STR(RECNO()))
REPLACE ALL field2 WITH ROUND(RAND()*1000,0)
REPLACE ALL field3 WITH CHR(MIN(90,MAX(65,ROUND(RAND()*100,0))))
REPLACE ALL field88 WITH ROUND(RAND()*100000,2)

&&&Start of Code
ColumnsToOutput=RECCOUNT()
FieldsToOutput=AFIELDS(temparray)-1

IF ColumnsToOutput>254
?&quot;Can't export this many columns: &quot;+ALLTRIM(STR(RECCOUNT()))
RETURN
ENDIF

sqlcmd=&quot;&quot;
scan
sqlcmd=sqlcmd+&quot;,&quot;+ALLTRIM(EVALUATE(FIELD(1)))+&quot; C(20)&quot;
ENDSCAN

sqlcmd=RIGHT(sqlcmd,LEN(sqlcmd)-1)
CREATE TABLE Test_Output(&sqlcmd)
FOR x = 1 TO FieldsToOutput
APPEND BLANK
ENDFOR

SELECT test
SCAN
VarThisRec=RECNO()
FOR x=2 TO FieldsToOutput+1
VarDataString=&quot;DataString&quot;+ALLTRIM(STR(x))
IF VARTYPE(EVALUATE(FIELD(x)))=&quot;N&quot; &&Number
&VarDataString=ALLTRIM(STR(EVALUATE(FIELD(x)),temparray(x,3),temparray(x,4)))
ELSE
&VarDataString=ALLTRIM(EVALUATE(FIELD(x)))
ENDIF
ENDFOR

SELECT Test_Output
FOR x=2 TO FieldsToOutput+1
VarDataString=&quot;DataString&quot;+ALLTRIM(STR(x))
GO X-1
REPLACE (FIELD(VarThisRec)) WITH &VarDataString
ENDFOR
ENDSCAN

GO top
BROWSE nowait


SELECT 2
GO top
BROWSE nowait

 

[baltman]

Support added for more datatypes.

Brian

&&&Test Table
CREATE TABLE test (field1 C(12), field2 n(3), field3 c(1), ;
field53 l,field42 y,field88 d,field89 t,field9 f(20,18),field99 b(10))

FOR x = 1 TO 20
APPEND BLANK
ENDFOR
REPLACE ALL field1 WITH &quot;Field&quot;+ALLTRIM(STR(RECNO()))
REPLACE ALL field2 WITH ROUND(RAND()*125,0)
REPLACE ALL field9 WITH RAND()*3333
REPLACE ALL field3 WITH IIF(CHR(MIN(90,MAX(65,ROUND(RAND()*100,0))))=&quot;Z&quot;,&quot;1&quot;,CHR(MIN(90,MAX(65,ROUND(RAND()*100,0)))))
REPLACE ALL field53 WITH IIF(RAND()>.5,.t.,.f.)
REPLACE ALL field42 WITH ROUND(RAND()*100000,2)
REPLACE ALL field88 WITH DATE()+ROUND(RAND()*100,0)
REPLACE ALL field89 WITH DATE()+ROUND(RAND()*100,4)
REPLACE ALL field99 WITH ROUND(RAND()*1000,7)

&&&Start of Code
ColumnsToOutput=RECCOUNT()
FieldsToOutput=AFIELDS(temparray)-1

IF ColumnsToOutput>254
?&quot;Can't export this many columns: &quot;+ALLTRIM(STR(RECCOUNT()))
RETURN
ENDIF

sqlcmd=&quot;&quot;
scan
sqlcmd=sqlcmd+&quot;,&quot;+ALLTRIM(EVALUATE(FIELD(1)))+&quot; C(20)&quot;
ENDSCAN

sqlcmd=RIGHT(sqlcmd,LEN(sqlcmd)-1)
CREATE table Test_Output(&sqlcmd)
FOR x = 1 TO FieldsToOutput
APPEND BLANK
ENDFOR

SELECT test
SCAN
VarThisRec=RECNO()
FOR x=2 TO FieldsToOutput+1
VarDataString=&quot;DataString&quot;+ALLTRIM(STR(x))
DO case
CASE VARTYPE(EVALUATE(FIELD(x)))=&quot;N&quot; OR VARTYPE(EVALUATE(FIELD(x)))=&quot;Y&quot; OR VARTYPE(EVALUATE(FIELD(x)))=&quot;B&quot; &&Numbers
&VarDataString=ALLTRIM(STR(EVALUATE(FIELD(x)),temparray(x,3),temparray(x,4)))
CASE VARTYPE(EVALUATE(FIELD(x)))=&quot;D&quot; &&Date
&VarDataString=DTOC(EVALUATE(FIELD(x)))
CASE VARTYPE(EVALUATE(FIELD(x)))=&quot;T&quot; &&DateTime
&VarDataString=TTOC(EVALUATE(FIELD(x)))
CASE VARTYPE(EVALUATE(FIELD(x)))=&quot;L&quot; &&Logical
&VarDataString=IIF(EVALUATE(FIELD(x))=.t.,&quot;T&quot;,&quot;F&quot
OTHERWISE &&Text
&VarDataString=ALLTR(EVALUATE(FIELD(x)))
ENDCASE
ENDFOR

SELECT Test_Output
FOR x=2 TO FieldsToOutput+1
VarDataString=&quot;DataString&quot;+ALLTRIM(STR(x))
GO X-1
REPLACE (FIELD(VarThisRec)) WITH &VarDataString
ENDFOR
ENDSCAN

GO top
BROWSE nowait


SELECT 2
GO top
BROWSE nowait

 

[ramani]

Hi

This is more like crossTab..

SELECT DISTINCT mtField, &quot; &quot; ;
FROM myTable ORDER BY 1 INTO ARRAY la_CrossTab
*************************************************
** Check if total number of fields do not exceed 255
IF ALEN(la_CrossTab,1) > 255
=MESSAGEBOX(&quot;Number of columns exceed 255. Cannot process.&quot;,0+16,&quot;Error&quot
RETURN
ENDIF
*************************************************
** Check for bad characters and prepare for field names
lcField = ''
n = 1
FOR I=1 TO ALEN(la_crossTab,1)
la_CrossTab(i,2)=goodchars(la_CrossTab(i,1))
** checking to avoid duplication of such new names
IF la_CrossTab(i,2) == lcField
n = n+1
lcField = ;
LEFT(la_CrossTab(i,2), ;
(9-LEN(ALLTRIM(STR)))) ;
+ &quot;_&quot;+ ALLTRIM(STR)
la_CrossTab(i,2) = lcField
ELSE
n = 1
lcField = la_CrossTab(i,2)
ENDIF
ENDFOR

** Create the crossTab cursor to create our output file
CREATE CURSOR gsXtemp (dummy C(1))

FOR n= 1 TO ALEN(la_crossTab,1)
ALTER TABLE gsXtemp ADD COLUMN &la_crossTab(n,2) I
INSERT INTO gsXtemp (&pcColFld,(FIELD(n+1))) ;
VALUES (la_crossTab(n,1),1)
ENDFOR
ALTER TABLE gsXtemp DROP COLUMN dummy
*************************************************
BROW

I have just made and cut & paste and tingered a little.
If my above code does not work as it is.. for
More detailed on this.. go thru code in this link..

http://winnersoft.coolfreepages.com/vfp/gsxtab1.htm

ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/