SQL Select and order by

[Adrian Halid]

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:  [phone]      F:  [phone]
E:  adrian.halid@...        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@...
http://listserver.u2ug.org/mailman/listinfo/u2-users

[Brian Leach]

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.halid@...> 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:  [phone]      F:  [phone]
> E:  adrian.halid@...        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@...
> http://listserver.u2ug.org/mailman/listinfo/u2-users

_______________________________________________
U2-Users mailing list
U2-Users@...
http://listserver.u2ug.org/mailman/listinfo/u2-users