Re: [sqlite] sqlite4 completion status

[Simon Slavin]

On 31 May 2014, at 6:24pm, big stone  wrote:

> I see a lot of commits on SQlite3 recently, but SQlite4 branch has nearly
> nothing since 3 months.
> 
> Is there a problem  ?

SQLite3 is in daily use on billions of devices.  Bugs must be fixed.

SQLite4 is an experimental tool, not released for production use.  Nobody 
should be using it for 'real' work yet.  Don't worry about it.

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

Re: [sqlite] sqlite4 completion status

[big stone]

Hi,

I see a lot of commits on SQlite3 recently, but SQlite4 branch has nearly
nothing since 3 months.

Is there a problem  ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with SELECTing CASE problem

[Luuk]

On 31-5-2014 06:03, jose isaias cabrera wrote:


"Luuk" wrote...


On 30-5-2014 19:29, jose isaias cabrera wrote:

"Igor Tandetnik" wrote...


On 5/30/2014 12:41 PM, jose isaias cabrera wrote:

What should be returned is
the value of vEmail of the first record that has Xtra4='y'


What do you mean by "first record"? Records are processed in no
particular order.


Good point.  I was, wrongly, thinking that it was top to bottom with the
id. So, the idea is that once Xtra4='y' has provided a value, that is
what I want.  So, I think that because I am always getting 'noemail'
then, it is caused because the last record read had Xtra4!='y'.  So,
this is not properly giving the result desired.  So, how can I change
this,

   CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END,

so that once I have a value from Xtra4='y', I want to keep that value as
the result.  Thanks.


sqlite> create table t(x);
sqlite> insert into t values(1),(2),(3);
sqlite> select x from t;
1
2
3
sqlite> select x, case when (select 1 from t where x=2) then 'Y' else
'N' end from t;
1|Y
2|Y
3|Y
sqlite>


Not quite, as I would like to grab vEmail the first time Xtra4='y'.
Here is a sample test:
create table t
(
   id integer primary key,
   ProjID integer,
   invoice,
   ProjFund,
   vEmail,
   Xtra4
);
insert into t values(1,1,70,100,'a','n');
insert into t values(2,1,30,50,'b','n');
insert into t values(3,1,25,40,'c','y');
insert into t values(4,1,55,80,'d','y');
insert into t values(5,2,30,75,'e','n');
insert into t values(6,2,10,100,'f','y');
insert into t values(7,2,40,68,'g','y');
insert into t values(8,2,30,55,'h','n');
insert into t values(9,3,25,40,'i','y');
insert into t values(10,3,30,100,'j','n');

select ProjID,
   case Xtra4 when 'y' then vEmail else 'noemail' end,
   sum(ProjFund),
   sum(ProjFund) - sum(case Xtra4 when 'y' then invoice else 0 end),
   sum(case Xtra4 when 'y' then invoice else 0 end)
   FROM t GROUP BY ProjID
   HAVING sum(case Xtra4 when 'y' then invoice else 0 end) > 0;

which gives me this result,
1|d|270|190|80
2|noemail|298|248|50
3|noemail|140|115|25

But I want,

1|d|270|190|80
2|f|298|248|50
3|i|140|115|25



add an extra MAX():

select ProjID,
  case MAX(Xtra4) when 'y' then vEmail else 'noemail' end,
  sum(ProjFund),
  sum(ProjFund) - sum(case Xtra4 when 'y' then invoice else 0 end),
  sum(case Xtra4 when 'y' then invoice else 0 end)
  FROM t GROUP BY ProjID
  HAVING sum(case Xtra4 when 'y' then invoice else 0 end) > 0;





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