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: adrian.ha...@itvision.com.au 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
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
