Not a direct answer but another option is to use the sqlexecdirect statement and column binding -that is how it is intended to be used.
Off the top of my head so may be slightly inaccurate: Dim cols(number) Ok = sqlexecdirect(@hstmt, 'select blah from foo'); For I = 1 to number Ok = sqlbindcol(@hstmt, number, SQL.B.DEFAULT,cols(number)) Next Loop Ok = sqlfetch(@hstmt) Until ok = SQL.NO.DATA.FOUND cols now contains the column data Repeat Ok = sqlfreestmt(@hstmt, SQL.DROP) Brian Sent from my iPad On 25 Jan 2012, at 01:02, Adrian Halid <[email protected]> wrote: > Hi All, > > I have just started to use SQL queries in universe instead of Retrieve. > > When I perform a SQL select against a Universe file and save it to a > &SAVEDLISTS& record each record is on a new field line and the column data is > separated by text marks. > > For example if I have a file with the following data > > File: MYFILE > > @ID COL1 COL2 COL3 > ----------------------------------- > 1 ABC 123 Moo > 2 DEF 456 Woof > 3 HIG 789 Meow > > > SELECT COL1, COL2, COL3 TO SLIST 1 FROM MYFILE; > > I would then get a saved list with 3 fields. > > ABC @TM 123 @TM Moo > DEF @TM 546 @TM Woof > HIG @TM 789 @TM Meow > > But if I add the ORDER BY to the end of my SQL query it does not use text > markers > > SELECT COL1, COL2, COL3 TO SLIST 1 FROM MYFILE ORDER BY COL1; > > My saved list is then made up of 9 fields. > > ABC > 123 > Moo > DEF > 456 > Woof > HIG > 789 > Meow > >> From reading the documentation I could not find anything specific about >> this. The closest thing I found was regarding multi column record id in SQL >> are separated by text marks. > > This makes it interesting when using SQL select statements in a Subroutine > when using READNEXT. > > In the first example there are 3 rows and each READNEXT returns all the data > you want separated by text markers. > ------------------------------------------------------------------------------------ > SQLCOM = "SELECT COL1, COL2, COL3 TO SLIST 1 FROM MYFILE;" > > EXECUTE SQLCOM SETTING NUMFOUND CAPTURING TRASH > LOOP WHILE READNEXT REC.ROW FROM 1 > CRT REC.ROW > REPEAT > ------------------------------------------------------------------------------------ > > > In the second example there are 9 rows and each you have to call READNEXT 3 > times to get all your column data for one record. > ------------------------------------------------------------------------------------ > SQLCOM = " SELECT COL1, COL2, COL3 TO SLIST 1 FROM MYFILE ORDER BY COL1;" > > EXECUTE SQLCOM SETTING NUMFOUND CAPTURING TRASH > LOOP WHILE READNEXT REC.ROW FROM 1 > CRT REC.ROW > REPEAT > ------------------------------------------------------------------------------------ > > Why does the ORDER BY statement change my SLIST? > > Regards > > Adrian Halid > Senior Analyst/Programmer > > > > IT Vision Australia Pty Ltd (ABN: 34 309 336 904) > PO Box 881, Canning Bridge WA 6153 > Level 3, Kirin Centre, 15 Ogilvie Road, Applecross, WA, 6153 > P: (08) 9315 7000 F: (08) 9315 7088 > E: [email protected] W: http://www.itvision.com.au > > ___________________________________________________________ > > NOTICE : This e-mail and any attachments are intended for the addressee(s) > only and may > contain confidential or privileged material. Any unauthorised review, use, > alteration, > disclosure or distribution of this e-mail (including any attachments) by an > unintended recipient > is prohibited. If you are not the intended recipient please contact the > sender as soon as > possible by return e-mail and then delete both messages. > ___________________________________________________________ > > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
