Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 Tags
Subject: RE: Build string with SQL?
Thread ID: 311134 Message ID: 311149 # Views: 67 # Ratings: 3
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Wednesday, June 29, 2011 8:49:11 AM         
   


> Hi all,
>
> I have a situation where I want to build a string representing a single column in a cursor.
>
>
SELECT TRANSKEY, ;
>        FP, ;
>        NAME, ;
>        MKTWPROB, ;
>        CAST(0 AS N(8,6)) AS MKTPPROB ;
>   FROM TRANSTABLE ;
>  WHERE TRANSKEY = rKeyId ;
>  ORDER BY FP ;
>   INTO CURSOR baseCursor READWRITE NOFILTER
> 
> ** Notice the cavalier but well-informed use of NOFILTER here. :)
> 
> probString = ''
> SCAN
>    probString = probString + STR(MKTWPROB,8,6) + '-'
> ENDSCAN

> The SCAN works perfectly well but it is within a loop traversing a very large table and, in the interests of achieving the highest possible speed, I was wondering whether it was possible to construct the string using a single SQL SELECT each time rather than using a SCAN hundreds of thousands of times. The '-' delimiter is not necessary - I've just been using that in testing for readability purposes.
>
> Any ideas would be most appreciated.
>
> TIA.
>
> Russell.

You could do that, but:
1. You must create a function where you should do the string additions
2. The variable you want to expand must be at least private (NOT local)
or you should pass it by reference to the function
3. I doubt that this will be faster than SCAN ENDSCAN :-)

CLEAR
CREATE CURSOR crsTest (Test C(20))
INSERT INTO crsTest VALUES ([111111111111111])
INSERT INTO crsTest VALUES ([222222222222222])
INSERT INTO crsTest VALUES ([333333333333333])
lcTest = []
SELECT AddStr(Test) FROM crsTest INTO ARRAY _asdf
WAIT WINDOW lcTest

FUNCTION AddStr(lcNewStr, lcTest)
    lcTest = lcTest + lcNewStr
RETURN ""


or

CLEAR
CREATE CURSOR crsTest (Test C(20))
INSERT INTO crsTest VALUES ([111111111111111])
INSERT INTO crsTest VALUES ([222222222222222])
INSERT INTO crsTest VALUES ([333333333333333])
LOCAL lcTest
lcTest = []
SELECT AddStr(Test, @lcTest) FROM crsTest INTO ARRAY _asdf
WAIT WINDOW lcTest

FUNCTION AddStr(lcNewStr, lcTest1)
    lcTest1 = lcTest1 + lcNewStr
RETURN ""


-----------------
Borislav Borissov

Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.

ENTIRE THREAD

Build string with SQL? Posted by Russell Hill @ 6/29/2011 3:31:04 AM
RE: Build string with SQL? Posted by Samir H. @ 6/29/2011 4:41:13 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:28:45 PM
RE: Build string with SQL? Posted by Stefan Wuebbe @ 6/29/2011 8:13:05 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:30:12 PM
RE: Build string with SQL? Posted by Borislav Borissov @ 6/29/2011 8:49:11 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:32:48 PM
RE: Build string with SQL? Posted by Mike Yearwood @ 6/29/2011 4:30:02 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 1:01:10 AM
RE: Build string with SQL? Posted by Mike Yearwood @ 6/30/2011 4:46:23 PM
RE: Build string with SQL? Posted by Samir H. @ 6/30/2011 6:29:30 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 10:53:32 PM
RE: Build string with SQL? Posted by Samir H. @ 7/1/2011 5:24:32 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:56 AM
RE: Build string with SQL? Posted by Michel Levy @ 7/1/2011 11:00:28 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:11 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 10:51:19 PM
RE: Build string with SQL? Posted by Leonid Lepin @ 6/29/2011 4:44:30 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 12:56:49 AM
RE: Build string with SQL? Posted by Tom Saddul @ 6/30/2011 3:36:58 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 7:20:40 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 12:40:07 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 2:43:58 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 1:31:36 PM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 2:23:04 PM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 3:31:26 PM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:12 PM
RE: Build string with SQL? Posted by Anders Altberg @ 7/2/2011 12:51:58 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/2/2011 2:44:25 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/2/2011 2:05:49 PM
RE: Build string with SQL? Posted by Stefan Wuebbe @ 7/3/2011 10:40:49 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/3/2011 2:19:55 PM