Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
"Igor Tandetnik" wrote... On 5/30/2014 4:43 PM, jose isaias cabrera wrote: But I want, 1|d|270|190|80 2|f|298|248|50 3|i|140|115|25 or 1|d|270|190|80 2|g|298|248|50 3|i|140|115|25 Does it matter from which row (among those with Xtra4='y') vEmail comes? You were talking about the row

Re: [sqlite] Storing opaque data, but would like sorted indexes on some fields

2014-05-30 Thread RSmith
On 2014/05/31 04:45, Hayden Livingston wrote: I have a binary format that is effectively structured data. I currently have multiple indexes but none of them are sorted, because in my toy system ORDER BY's are not supported, an implicit ORDER BY time of record inserted exists because it is a

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread Igor Tandetnik
On 5/30/2014 4:43 PM, jose isaias cabrera wrote: But I want, 1|d|270|190|80 2|f|298|248|50 3|i|140|115|25 or 1|d|270|190|80 2|g|298|248|50 3|i|140|115|25 Does it matter from which row (among those with Xtra4='y') vEmail comes? You were talking about the row with the smallest id, but your

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
"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

[sqlite] Storing opaque data, but would like sorted indexes on some fields

2014-05-30 Thread Hayden Livingston
I have a binary format that is effectively structured data. I currently have multiple indexes but none of them are sorted, because in my toy system ORDER BY's are not supported, an implicit ORDER BY time of record inserted exists because it is a single threaded application. My indexing story is

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
"Igor Tandetnik" wrote... On 5/30/2014 1:29 PM, 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

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread Igor Tandetnik
On 5/30/2014 1:29 PM, 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

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
"Igor Tandetnik" wrote... On 5/30/2014 1:29 PM, jose isaias cabrera wrote: 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,

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread Igor Tandetnik
On 5/30/2014 1:29 PM, jose isaias cabrera wrote: 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

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread Luuk
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.

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
"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

Re: [sqlite] Help with SELECTing CASE problem

2014-05-30 Thread Igor Tandetnik
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. -- Igor Tandetnik ___

[sqlite] Help with SELECTing CASE problem

2014-05-30 Thread jose isaias cabrera
Greetings! I have this SELECT, SELECT cust, ProjID, proj, A_No, bdate, 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

Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-30 Thread jose isaias cabrera
"Edward Lau" wrote... Hi Jose: In SQL, aggregate function cannot be used in the WHERE clause. Use the HAVING clause instead. Try this: SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END, sum(ProjFund),

Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-30 Thread jose isaias cabrera
"Richard Hipp" wrote... Do you want a HAVING clause? Thanks. Didn't know about this one. Now it works: SELECT cust, ProjID, proj, A_No, bdate, CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END, sum(ProjFund), sum(ProjFund)-sum(CASE Xtra4 WHEN 'y'

Re: [sqlite] Sqlite3 handler uses

2014-05-30 Thread J Decker
option D; use a single connection per thread. Take advantage of __thread ... and each thread can see if( !database_connection ) connect(). -or- make A thread that you queue all work to; but you'd still need a seperate connection for each reader... Open and close are a large hit to do per