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 adrian.ha...@itvision.com.au 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
@IDCOL1COL2COL3
---
1ABC123Moo
2DEF456Woof
3HIG789Meow
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: adrian.ha...@itvision.com.auW: 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
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
