Re: [sqlite] Ordering looks incorrect

[David Crayford]

On 14/09/2016 9:59 PM, R Smith wrote:
I think this is answered, but since you are a bit new to SQLite, and 
to be somewhat more informant...


SQLite is certainly a different experience to the enterprise class data 
bases that I'm used to but I like it. I'm still getting used to the duck 
typing aspect.
I've built it on a z/OS mainframe with very little changes and it works 
great in the z/OS UNIX environment. I'm wondering what it will take to 
get it to work

in the native file system.



On 2016/09/14 3:19 PM, David Crayford wrote:
Of course. How do I do something similar to DB2 decimal() function 
which will add trailing zeros to precision? I tried round(col,6) but 
it knocked off the trailing zero like 12.12345.


It's not so much the trailing zeroes you need, it's the leading 
spaces. But you should order by the original value, not by "column 2" 
because "column 2" contains RESULTS (or OUTPUT if you will), not 
original values, and you have stringified the results with your 
printf() statement, so they will now sort like strings.




Got it, the sort on original value was what I needed. And thank you to 
you chaps for pointing it out.


At least, this is how SQLite thinks of that statement - I'm unsure if 
this is in line (or not in line) with any standard. I think from a 
previous discussion we concluded that ordering, limiting and 
offsetting were all non-standard adaptions by various SQL 
implementations and so have no true conformance spec.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[R Smith]

I think this is answered, but since you are a bit new to SQLite, and to 
be somewhat more informant...


On 2016/09/14 3:19 PM, David Crayford wrote:
Of course. How do I do something similar to DB2 decimal() function 
which will add trailing zeros to precision? I tried round(col,6) but 
it knocked off the trailing zero like 12.12345.


It's not so much the trailing zeroes you need, it's the leading spaces. 
But you should order by the original value, not by "column 2" because 
"column 2" contains RESULTS (or OUTPUT if you will), not original 
values, and you have stringified the results with your printf() 
statement, so they will now sort like strings.


At least, this is how SQLite thinks of that statement - I'm unsure if 
this is in line (or not in line) with any standard. I think from a 
previous discussion we concluded that ordering, limiting and offsetting 
were all non-standard adaptions by various SQL implementations and so 
have no true conformance spec.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[David Crayford]

On 14/09/2016 9:23 PM, Clemens Ladisch wrote:

David Crayford wrote:

How do I do something similar to DB2 decimal() function which will add trailing 
zeros to precision?

You could use the standard SQL CAST() to convert the value back into a number,
by why not simply sort by the original number to begin with?

   ORDER BY max(cpu1)


+1 that's the answer. Thank you so much. As you can tell I come from a 
very different background WRT database technology ;)




Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Clemens Ladisch]

David Crayford wrote:
> How do I do something similar to DB2 decimal() function which will add 
> trailing zeros to precision?

You could use the standard SQL CAST() to convert the value back into a number,
by why not simply sort by the original number to begin with?

  ORDER BY max(cpu1)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[David Crayford]

Of course. How do I do something similar to DB2 decimal() function which 
will add trailing zeros to precision? I tried round(col,6) but it 
knocked off the trailing zero like 12.12345.



On 14/09/2016 9:10 PM, Clemens Ladisch wrote:

David Crayford wrote:

9.733366
595.509361
29.117646
28.607606
14.684294

The sort order of these strings looks correct.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Dominique Devienne]

On Wed, Sep 14, 2016 at 3:10 PM, Clemens Ladisch  wrote:

> The sort order of these strings looks correct.
>

As hinted by Clemens, you're sorting text values, not reals. See below. --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table stats (plan, cpu);
sqlite> insert into stats values ('pts46', 595.509361), ('pts46',
95.059669), ('DSNUTIL', 9.733366);
sqlite> .headers on
sqlite> .mode column
sqlite> select * from stats;
plancpu
--  --
pts46   595.509361
pts46   95.059669
DSNUTIL 9.733366
sqlite> select plan, max(cpu) from stats group by plan order by 2 desc;
planmax(cpu)
--  --
pts46   595.509361
DSNUTIL 9.733366
sqlite> select plan, printf("%.6f", max(cpu)) from stats group by plan
order by 2 desc;
planprintf("%.6f", max(cpu))
--  
DSNUTIL 9.733366
pts46   595.509361
sqlite>
sqlite> select typeof(max(cpu)), typeof(printf("%.6f", max(cpu))) from
stats group by plan;
typeof(max(cpu))  typeof(printf("%.6f", max(cpu)))
  
real  text
real  text
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Simon Slavin]

On 14 Sep 2016, at 2:10pm, Clemens Ladisch  wrote:

> The sort order of these strings looks correct.

Heh.  Heh heh.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Clemens Ladisch]

David Crayford wrote:
> 9.733366
> 595.509361
> 29.117646
> 28.607606
> 14.684294

The sort order of these strings looks correct.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ordering looks incorrect

[David Crayford]

Greetings,

I've just started using sqlite to (ironically) report on z/OS DB2 stats. 
I've written a query where the results look a bit strange. I group by 
the DB2 plan name and order by the maximum
CPU time. The sort order looks incorrect as you can see in the report 
below. I'm certainly not discounting the fact I've done something dumb.


Here's the query.

.headers on
.mode column
.width 0 -10 -10 -10
select plan,
   printf("%.6f", max(cpu1)) as 'Max CPU',
   printf("%.6f", avg(cpu1)) as 'Avg CPU',
   printf("%.6f", min(cpu1)) as 'Min CPU'
from db2
group by plan
order by 2 desc;
.width 0 0 -10 -10 -10 -10 -10 -10 -10 -10 -10 -10
-- list all Proteus plan performance
select time, plan,
   printf("%.6f",cpu1) as "CPU Time",
   "select", "insert", "update", "delete", "fetch", "open", "close",
   "prepare", "commits"
from db2 -- where plan = 'PTS46'
order by cpu1 desc;

davcra01@cervidae:~$ sqlite3 --version
3.14.2 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6


Plan   Max CPU Avg CPU Min CPU
--  --  -- --
DSNUTIL   9.7333660.251608 0.000503
PTS46   595.5093612.278950 0.000892
DSNREXX  29.1176460.015989 0.001210
?RRSAF   28.6076068.248447 0.131628
KO2PLAN  14.6842940.004192 0.000167
ADB   1.3510440.174945 0.002422
  1.2707120.130687 0.000216
DISTSERV  0.2301320.003751 0.000343
IPIUTILB  0.2064710.022027 0.006389
ADB2GEN   0.1639920.092727 0.021461
DSNTIAD   0.0859370.048863 0.001895
CSQ5L800  0.0666220.001047 0.000391
DSNESPCS  0.0612230.022873 0.003060
DSNBIND   0.0319470.009363 0.002422
CSQ5S800  0.0205560.000914 0.000383
CSQ5W800  0.0070230.002816 0.000673
ADB27SPC  0.0047100.004484 0.004258
CSQ5R800  0.0036980.000695 0.000263
CSQ5T800  0.0035390.002574 0.000695
FBODCP05  0.0024160.000428 0.000354
CSQ5U800  0.0022930.001106 0.000438
TIMEPlan  CPU Time SELECT  
INSERT  UPDATE  DELETE   FETCH OPEN   CLOSE PREPARE
--  --  --  -- 
--  --  --  --  -- --  
--
2016-09-12 09:33:54.761413  PTS46   595.509361 0   15968
4793   16769  233954 55343   553431223
2016-09-07 12:41:53.949671  PTS4695.516983 09501
11563334  101360 12034   120341378
2016-09-07 10:54:46.404475  PTS4695.059669 0   12598 
7802898  120602 12199   12199 751
2016-09-07 11:21:42.332159  PTS4658.592052 06007 
5081445   48193 61146114 287
2016-09-07 12:50:56.839200  PTS4658.303021 06007 
5081445   51395 62746274 287
2016-09-09 17:46:06.732869  DSNREXX  29.117646 0   
0   0 286 287 1   1   1
2016-09-11 17:01:02.454043  ?RRSAF   28.607606 0   0
9722  6124009722 97229722   0
2016-09-08 17:46:05.062529  DSNREXX  28.011428 0   
0   0   0   1 1   1   1
2016-09-12 17:46:14.052496  DSNREXX  27.694071 0   
0   0   0   1 1   1   1
2016-09-13 17:45:56.749485  DSNREXX  26.836652 0   
0   0   0   1 1   1   1
2016-09-07 17:45:57.286365  DSNREXX  26.765156 0   
0   0   0   1 1   1   1
2016-09-11 17:01:02.562077  ?RRSAF   16.733743 0   0
9718   09718 97189718   0
2016-09-08 12:11:25.626596  DSNREXX  11.265809 0   
0   0   09876 1   0   1
2016-09-08 12:10:55.707563  DSNREXX  11.196782 0   
0   0   09876 1   0   1
2016-09-08 12:12:37.436988  DSNREXX  11.190524 0   
0   0   09876 1   0   1
2016-09-08 17:18:03.434308  DSNUTIL   9.733366 0   
0   0   0   0 0   0   0
2016-09-09 17:18:03.481755  DSNUTIL   9.719033 0   
0   0   0   0 0   0   0
2016-09-12 17:18:23.088672  DSNUTIL   9.682909 0   
0   0   0   0 0   0   0
2016-09-13 17:17:58.083050  DSNUTIL   9.650545 0   
0   0   0   0 0   0   0
2016-09-12 09:49:23.324659  PTS46 9.645624 0 
150  24  84   23500 321 321  31
2016-09-08 17:46:17.707124  DSNREXX   9.593526 0   
0   0   4