Re: [sqlite] Sorting by month with two dates input

[Keith Medcalf]

update LSOpenProjects
   set billdate = null
 where len(rtrim(billdate)) < 10;

>Thanks for this, but how do I set a value to null?  I thought null and ''
>were the same, but now I see it is not.

'' means that "the value is an empty string" whereas null means there is no 
value/the value is unknown/the value is not determined yet.

You can "fix" all your dates by doing an:

update 
   set  = (julianday(

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Igor Tandetnik" wrote...


On 9/1/2014 12:02 AM, jic wrote:

SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t

But this is not working correctly, as the beginning of the result, there
is no number because billdate is empty.  So, I get a list like this:

Co01|13016.16|20024.46|


How is "absence of billdate" represented in the database - as NULL or as 
empty string? I suspect it's the latter. coalesce() only treats nulls as 
"special", not empty strings. See how the answer changes if you replace 
coalesce(billdate,bdate) with


(case when billdate != '' then billdate else bdate end)
Yep, you're right.  It's the NULL value that is not working.  Your 
suggestion works.  But, I have to test the various results to make sure that 
all deliver the same.  Thanks.. 


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

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Darren Duncan" wrote...


On 2014-09-01, 6:50 PM, jose isaias cabrera wrote:

Thanks for this, but how do I set a value to null?


insert into foo (myfield) values (null);

That's one way.  If you're using some wrapper API, then the host 
language's analagy of an undefined value, eg undef in Perl, should do it.



I thought null and '' were
the same, but now I see it is not.


Have you used Oracle before?


Written a few lines here and there, but, that is not where I got it from, 
though.  And I agree, NULL and '' is not the same.




Oracle treats NULL and '' as the same, but that's a problem with Oracle 
which gives its users no end of headaches.


The SQL standard and basically every other SQL DBMS treats NULL as being 
distinct from every other value, which is how it is supposed to be.


-- Darren Duncan

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


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

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Darren Duncan" wrote...

A common logical error that may be affecting you is, do your dates include 
a time portion or are they just year-month-day?  If they include a time 
portion, then records from Dec 31 likely won't be counted as your 'between' 
arguments may be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan


No, they are just -MM-DD.  I think that Igor hit the spot.  It is NULL 
vs. ''.




On 2014-09-01, 6:41 PM, jose isaias cabrera wrote:


"Darren Duncan" wrote...

On 2014-08-31, 9:35 PM, Darren Duncan wrote:

On 2014-08-31, 9:10 PM, jose isaias cabrera wrote:
SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t 
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;


I'm surprised that even compiles, since AFAIK the SELECT clause is 
evaluated
after the GROUP BY clause, and t doesn't exist yet at the GROUP BY 
stage (unless

you have another one in LSOpenProjects).

Maybe try using a subquery to force correct evaluation order?

Kind of like this:

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY substr(t,1,7), cust;

-- Darren Duncan


Actually, isn't this more what you want?

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t 
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;


This one also does the same thing as mine. Hmmm...


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


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

Re: [sqlite] Sorting by month with two dates input

[Darren Duncan]

On 2014-09-01, 6:50 PM, jose isaias cabrera wrote:

Thanks for this, but how do I set a value to null?


insert into foo (myfield) values (null);

That's one way.  If you're using some wrapper API, then the host language's 
analagy of an undefined value, eg undef in Perl, should do it.



I thought null and '' were
the same, but now I see it is not.


Have you used Oracle before?

Oracle treats NULL and '' as the same, but that's a problem with Oracle which 
gives its users no end of headaches.


The SQL standard and basically every other SQL DBMS treats NULL as being 
distinct from every other value, which is how it is supposed to be.


-- Darren Duncan

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

Re: [sqlite] Sorting by month with two dates input

[Darren Duncan]

A common logical error that may be affecting you is, do your dates include a 
time portion or are they just year-month-day?  If they include a time portion, 
then records from Dec 31 likely won't be counted as your 'between' arguments may 
be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan


On 2014-09-01, 6:41 PM, jose isaias cabrera wrote:


"Darren Duncan" wrote...

On 2014-08-31, 9:35 PM, Darren Duncan wrote:

On 2014-08-31, 9:10 PM, jose isaias cabrera wrote:

SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;


I'm surprised that even compiles, since AFAIK the SELECT clause is evaluated
after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage (unless
you have another one in LSOpenProjects).

Maybe try using a subquery to force correct evaluation order?

Kind of like this:

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY substr(t,1,7), cust;

-- Darren Duncan


Actually, isn't this more what you want?

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;


This one also does the same thing as mine. Hmmm...


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

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Stephen Chrzanowski" wrote...



Untested, but first whack at it;

select * from yourtables order by ifnull(billdate,bdate)

Criteria met:
- A date posted prioritizing billdate and then bdate
- Sorted based on date


yeah, I had tried that before and no cigar... :-)  Thanks, though.

josé 


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

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Keith Medcalf" wrote...

I have to create some reporting and I need to report on customers amount1
and amount2 two based on the whole year of 2013.  There are two dates 
that

are to be used as input: billdate and bdate.  I can do one or the other,
but, I can not seem to get the correct date when using both.  The 
criteria

is that if there is a billdate, that overides the bdate, so the billdate
should be the one used and reported on.  If there is no billdate, then,
bdate is used.  There is always a bdate as this is the date the project 
was

opened, so all projects have a bdate.  But, not all projects may have a
billdate.



SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR
bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;


is an improper interpretation of the problem statement.

Based on the problem statement I should think that:

 SELECT cust, sum(ProjFund), sum(Xtra8), reportdate
   FROM (SELECT cust, ProjFund, Xtra8, coalesce(billdate, bdate) as 
reportdate

   FROM LSOpenProjects
  WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
) as X
GROUP BY strftime('%m', reportdate), cust;

This one took a long time and did not return anything.


or, equivalently:

 SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate, bdate) as 
reportdate

   FROM LSOpenProjects
  WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
GROUP BY strftime('%m', coalese(billdate, bdate)), cust;


This one, is looking like a winner.  Thanks.



is correct.

Proper operation requires that the billdate be NULL or contain a date.  If 
it was set to an empty or blank string rather than null, then of course 
the coalesce function will still return the empty or blank string that was 
stored there rather than the bdate.  If the data store is corrupted in 
this fashion, then you need to fix it first, and whatever applications 
caused the corruption of the data in the first place.


Alternatively you can fix such ill-conceived database contents/design by 
replacing each use of


coalesce(billdate, bdate) -> CASE WHEN billdate is null or 
length(rtrim(billdate) < 10)) THEN bdate ELSE billdate END


in which case fixing the broken application(s) and database design will 
not be required.


Thanks for this, but how do I set a value to null?  I thought null and '' 
were the same, but now I see it is not.


thanks.

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

Re: [sqlite] Sorting by month with two dates input

[jose isaias cabrera]

"Darren Duncan" wrote...

On 2014-08-31, 9:35 PM, Darren Duncan wrote:

On 2014-08-31, 9:10 PM, jose isaias cabrera wrote:
SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t 
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;


I'm surprised that even compiles, since AFAIK the SELECT clause is 
evaluated
after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage 
(unless

you have another one in LSOpenProjects).

Maybe try using a subquery to force correct evaluation order?

Kind of like this:

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY substr(t,1,7), cust;

-- Darren Duncan


Actually, isn't this more what you want?

SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t 
FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR 
bdate

BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;


This one also does the same thing as mine. Hmmm... 


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

Re: [sqlite] Sorting by month with two dates input

[Petite Abeille]

On Sep 1, 2014, at 9:19 PM, Keith Medcalf  wrote:

> 
>> On Sep 1, 2014, at 6:00 PM, Igor Tandetnik  wrote:
>> 
>>> (case when billdate != '' then billdate else bdate end)
>> 
>> Or, more succinctly:
>> 
>> coalesce( nullif( billdate, '' ), bdate )
>> 
>> (To OP: empty strings are E V I L. Don't use them. Ever.)
> 
> Unless of course it is one or more spaces and not an empty string ...
> 
> coalesce(nullif(rtrim(billdate), ''), bdate)
> 
> an empty string is indistinguishable from a string of one or more spaces 
> unless one makes efforts to tell the difference.

(To OP: if this is the case, you get what you deserve! :D )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sorting by month with two dates input

[Keith Medcalf]

>On Sep 1, 2014, at 6:00 PM, Igor Tandetnik  wrote:
>
>> (case when billdate != '' then billdate else bdate end)
>
>Or, more succinctly:
>
>coalesce( nullif( billdate, '' ), bdate )
>
>(To OP: empty strings are E V I L. Don't use them. Ever.)

Unless of course it is one or more spaces and not an empty string ...

coalesce(nullif(rtrim(billdate), ''), bdate)

an empty string is indistinguishable from a string of one or more spaces unless 
one makes efforts to tell the difference.




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

Re: [sqlite] Sorting by month with two dates input

[Petite Abeille]

On Sep 1, 2014, at 6:00 PM, Igor Tandetnik  wrote:

> (case when billdate != '' then billdate else bdate end)

Or, more succinctly:

coalesce( nullif( billdate, ‘’ ), bdate )

(To OP: empty strings are E V I L. Don’t use them. Ever.)

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

Re: [sqlite] Sorting by month with two dates input

[Igor Tandetnik]

On 9/1/2014 12:02 AM, jic wrote:

SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t

But this is not working correctly, as the beginning of the result, there
is no number because billdate is empty.  So, I get a list like this:

Co01|13016.16|20024.46|


How is "absence of billdate" represented in the database - as NULL or as 
empty string? I suspect it's the latter. coalesce() only treats nulls as 
"special", not empty strings. See how the answer changes if you replace 
coalesce(billdate,bdate) with


(case when billdate != '' then billdate else bdate end)

--
Igor Tandetnik

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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: 01 September 2014 15:30
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has some problem
> > > Please try the patch at
> > >
> http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> > > 3 and
> > > see if that fixes the problem.
> > >
> >
> > I think that fixes unixDelete. Running on the vxWorks dosFs disk
> > everything works as before.
> >
> > If I use the host filing system, then I think the delete of the
> > non-existent file works, but it then fails in unixSync followed by a fail
> > in unixDelete
> >
> > os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
> >
> 
> 
> Error code 35 is ENOTSUP - fsync is apparently not supported on your
> filesystem.
> 

OK. So it sounds like the answer to Wang Qinggang at the moment is that the 
host filing system is not supported.

I'm slightly surprised fsync isn't supported, but a quick test here proves that 
it isn't. I will try and raise it with WindRiver and see what they say. It may 
be a general problem with any network filing system under vxWorks.

I'm happy that dosFs works. That's all I need for the time being.

Regards

Andy Ling


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

Re: [sqlite] Sorting by month with two dates input

[John McKown]

On Sun, Aug 31, 2014 at 11:02 PM, jic  wrote:
>
> Greetings!
>
> I have to create some reporting and I need to report on customers amount1
> and amount2 two based on the whole year of 2013.  There are two dates that
> are to be used as input: billdate and bdate.  I can do one or the other,
> but, I can not seem to get the correct date when using both.  The criteria
> is that if there is a billdate, that overides the bdate, so the billdate
> should be the one used and reported on.  If there is no billdate, then,
> bdate is used.  There is always a bdate as this is the date the project was
> opened, so all projects have a bdate.  But, not all projects may have a
> billdate. This is what I am trying...
>
> SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM
> LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
> BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;
>
> But this is not working correctly, as the beginning of the result, there is
> no number because billdate is empty.  So, I get a list like this:
>
> Co01|13016.16|20024.46|
> Co02|12280.0|110.0|
> Co03|550.0|0.0|
> Co04|1317.49|285.72|
> Co05|747.0|0.0|
> Co05|187330.3|39076.85|
> Co07|14148.39|0.0|
> Co08|156011.68|6725.09|
> Co09|10890.92|142.98|
> Co10|333.34|0.0|
> Co11|294.39|214.29|
> Co12|14857.29|0.0|
> Co02|402.16|522.28|2012-02-15
> Co04|3506.5|3976.11|2013-01-15
> Co05|270.29|351.03|2013-01-15
> Co06|1273.17|0.0|2013-01-15
> Co10|16337.75|21217.83|2013-01-15
>
> but there should always be a date.  Any help would be greatly appreciated.
> Thanks.
>
> josé

You'll likely get some better responses when other in the US and
Canada (not meaning to disparage the rest of the world) come back from
Labor Day weekend. Your select looks quite good to me. However, if I
were doing this, I think it might be better to use a CTE. This is
rather new in SQLite.
Ref: http://www.sqlite.org/lang_with.html

WITH tempTable AS (
   SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t
FROM LSOpenProjects )
SELECT cust, sum(ProjFund), sum(Xtra8), t FROM tempTable
   WHERE t BETWEEN '2013-01-01' AND '2013-12-31'
   GROUP BY SUBSTR(t,1,7), cust;

Now, I think you can emulate this in an older version of SQLite by
using a subquery. Perhaps something like (and you'll notice the
similarity to the above)

SELECT cust, sum(ProjFund), sum(Xtra8), t
   FROM ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) AS t
  FROM LSOpenProjects ) AS tempTable
WHERE t BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY SUBSTR(t,1,7), cust;

the AS tempTable isn't really needed, I just put it in there because I
can, and to help document the relationship with the CTE example.

I will also point out that the above may be acceptable to SQLite but
likely will fail when used in a data base which exactly conforms to
the SQL standard. I am fairly sure that a compliant SQL system will
complain that the variable "t" is not the object of an aggregate
function and is not in the GROUP BY clause. You are grouping by a
temporary variable: SUBSTR(t,1,7) and not the entire variable t. To
me, it seems you are grouping by the "-mm" portion of the date.
Perhaps you should use the same SUBSTR(t,1,7) in the upper level
SELECT as well? That would satisfy the SQL standard. Oh well, I'm no
expert and may be wrong on this.

Unfortunately, I do not have any data to test the above to try to fix
any problems such as syntax errors. Or even to verify that I'm even
close to correct. But I do hope that it was of some help to you.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser v3.3.0 released

[Simon Slavin]

On 1 Sep 2014, at 3:32pm, John McKown  wrote:

> On Mon, Sep 1, 2014 at 2:21 AM,   wrote:
> 
>> v3). "Fluffy Pink Rabbits of Doom for SQLite" has a certain
>> appeal at this point... ;)
> 
> You might want to do a Google, or other, search on that.
> 
> SteamOS: http://steamcommunity.com/groups/pinkfluffybunniesofdoom#
> poem: http://www.fuzzy-fish.com/wordpress-he/archives/74
> Komic: http://thekamics.thecomicseries.com/comics/517/
> Book: "Revenge of the Fluffy Bunnies" by Craig Shaw Gardner

"Revenge of" is the codename for version 4.0.0.

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

Re: [sqlite] SQLite Database Browser v3.3.0 released

[John McKown]

On Mon, Sep 1, 2014 at 2:21 AM,   wrote:
> On 2014-08-31 16:07, Richard Hipp wrote:
>>
>> On Sun, Aug 31, 2014 at 11:49 AM,  wrote:
>
> 
>>>
>>> We've updated everything we control, and just pushed out a v3.3.1
>>> release that uses the new project naming (with no functional
>>> changes).
>
>
> ... and it turns out there's already a project called "Database
> Browser".  Which supports SQLite (and others).
>
>   http://www.etl-tools.com/database-browser/overview.html
>
> Looks like we'll need to do some thinking about new name v2 (or
> v3). "Fluffy Pink Rabbits of Doom for SQLite" has a certain
> appeal at this point... ;)

You might want to do a Google, or other, search on that.

SteamOS: http://steamcommunity.com/groups/pinkfluffybunniesofdoom#
poem: http://www.fuzzy-fish.com/wordpress-he/archives/74
Komic: http://thekamics.thecomicseries.com/comics/517/
Book: "Revenge of the Fluffy Bunnies" by Craig Shaw Gardner
   http://books.google.com/books/about/Revenge_of_the_Fluffy_Bunnies.html

>
> + Justin

--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Richard Hipp]

On Mon, Sep 1, 2014 at 10:15 AM, Andy Ling  wrote:

> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: 01 September 2014 14:39
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> >
> > Please try the patch at
> > http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> > 3 and
> > see if that fixes the problem.
> >
>
> I think that fixes unixDelete. Running on the vxWorks dosFs disk
> everything works as before.
>
> If I use the host filing system, then I think the delete of the
> non-existent file works, but it then fails in unixSync followed by a fail
> in unixDelete
>
> os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
>


Error code 35 is ENOTSUP - fsync is apparently not supported on your
filesystem.



> Error deleting file /tgtsvr/testdb.sql-journal errno 13
> os_unix.c:30167: (13) unlink(/tgtsvr/testdb.sql-journal) -  (2570)
> Error executing statement CREATE TABLE t (id INTEGER PRIMARY KEY ASC) -
> disk I/O error
> Error message disk I/O error
>
> Regards
>
> Andy Ling
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: 01 September 2014 14:39
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> Please try the patch at
> http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> 3 and
> see if that fixes the problem.
> 

I think that fixes unixDelete. Running on the vxWorks dosFs disk everything 
works as before.

If I use the host filing system, then I think the delete of the non-existent 
file works, but it then fails in unixSync followed by a fail in unixDelete

os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
Error deleting file /tgtsvr/testdb.sql-journal errno 13
os_unix.c:30167: (13) unlink(/tgtsvr/testdb.sql-journal) -  (2570)
Error executing statement CREATE TABLE t (id INTEGER PRIMARY KEY ASC) - disk 
I/O error
Error message disk I/O error

Regards

Andy Ling


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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Richard Hipp]

On Mon, Sep 1, 2014 at 7:04 AM, Andy Ling  wrote:

>
> This can be fixed by changing the patch to something like...
>
> #if OS_VXWORKS
> }else if( errno==0x380003  || errno == 13){ /* ==
> S_dosFsLib_FILE_NOT_FOUND */
>   rc = SQLITE_IOERR_DELETE_NOENT;
> #endif
>
> Although this doesn't feel right having to keep extending the list of
> error codes that are checked.
> I'm not sure what else can be done though. Maybe some compile options that
> let you choose.
>

Please try the patch at
http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb4833 and
see if that fixes the problem.

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

Re: [sqlite] How should i found time difference between two events in sqlite3 table , like on off status

[Clemens Ladisch]

sanjeev wrote:
>  sqlite> select sdata ,timestamp from tbl_sensor where nid=4 and timestamp
>> =date('now','-4 days') order by timestamp ASC ;0|2014-08-28
> 04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28
> 04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28
> [...]

This is unreadable.

> plz help me out ...

At the right side of your keyboard is the Enter key.
It allows you to insert line breaks.


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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jan Nijtmans
> Sent: 01 September 2014 12:29
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> 2014-09-01 13:04 GMT+02:00 Andy Ling :
> > Although this doesn't feel right having to keep extending the list of error
> codes that are checked.
> > I'm not sure what else can be done though. Maybe some compile options
> that let you choose.
> 
> Here is a list of possible error-codes I found:
> 
>  >

That list is from an older version of vxWorks (5.5). So some things have 
changed slightly.

> 
> The value 13 is listed here as EACCES, I really doubt this one
> should be added: If there really is an access problem, I
> think that shouldn't be masked.
> 

That code is still as listed. I suspect the problem is vxWorks can't tell the 
difference between
a file not there and not being able to access the file. Although see later...

> There are two entries that end with _FILE_NOT_FOUND, the other one is:
> 1310723  0x140003  S_rt11FsLib_FILE_NOT_FOUND
> This suggests that for VxWorks an additional check for 0x140003
> might work if you use the rt11 filesystem (whatever that is .)
> 

rt11 is not supported any longer. It harks back to the days of the LSI11 and 
DEC. rt11 was the
OS DEC used then. It was a bit like a cut down version of VMS.

> Other possible candidates:
> 2686989  0x29000d  S_netDrv_NO_SUCH_FILE_OR_DIR
> 2949130  0x2d000a  S_nfsDrv_NO_SUCH_FILE_OR_DIR
> 7602186  0x74000a  S_cdromFsLib_NO_SUCH_FILE_OR_DIRECTORY
> 

They are still current.

vxWorks now includes the TrueFlashFilingSystem which has the error code

13303810  0xcb0002  S_tffsLib_FILE_NOT_FOUND

I have done some more investigation accessing the host file system.
First, running unlink from the command line. Calling unlink for a file that 
exists works correctly. It deletes the file and returns OK. Calling unlink for 
a file that doesn't exist returns error and sets errno to EACCES. So this may 
be a bug or vxWorks may not be able to tell when talking over a network. If I 
get time I will put in a call to WindRiver to see what the official line is.

So I added the check in unixDelete for errno 13 to see what happens.

The open does create a database file on my host PC. The create table still 
fails with a disk I/O error, but it gets a bit further.
I've added a print in unixLogError and it prints the following...

os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)

The 1034 on the end is errno.

So it is failing in unixSync. It has created the testdb.sql-journal file and it 
is 512 bytes long. So I suspect things are working, but the error checks don't 
think they are. At this point I probably need more help to save me wasting too 
much time.

Personally I'm not sure how much effort needs to be put into this if it is only 
the host file system that is a problem. Most vxWorks systems would only be 
using the host filing system during development.

Regards

Andy Ling

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

[sqlite] How should i found time difference between two events in sqlite3 table , like on off status

[sanjeev]

 sqlite> select sdata ,timestamp from tbl_sensor where nid=4 and timestamp
>=date('now','-4 days') order by timestamp ASC ;0|2014-08-28
04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28
04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28
05:23:410|2014-08-28 05:24:410|2014-08-28 05:25:41255|2014-08-28
05:26:39255|2014-08-28 05:26:41255|2014-08-28 05:27:410|2014-08-28
05:28:030|2014-08-28 05:28:410|2014-08-28 05:29:410|2014-08-28
05:30:410|2014-08-28 05:41:410|2014-08-28 05:42:410|2014-08-28
05:43:41255|2014-08-28 05:44:35255|2014-08-28 05:44:41255|2014-08-28
05:45:410|2014-08-28 06:34:580|2014-08-28 06:35:410|2014-08-28
06:36:41255|2014-08-28 06:37:10/* here sdata =0 Means ,off, and if sdata
=255 means on  is there *//* I want total time in ON and total time they are
OFF *//* so i query to  make time difference between two events  ,than i
plan to add  */sqlite> select sdata,timestamp , strftime('%M',timestamp) -
strftime('%M',ifnull((select timestamp from tbl_sensor where sdata =0 and
nid =4 and timestamp >= date('now','-4 days') order by timestamp asc
),timestamp ))from tbl_sensor where  sdata =255 and nid =4 and timestamp >=
date('now','-4 days')  order by timestamp ASC  ;255|2014-08-28
05:26:39|26255|2014-08-28 05:26:41|26255|2014-08-28
05:27:41|27255|2014-08-28 05:44:35|44255|2014-08-28
05:44:41|44255|2014-08-28 05:45:41|45255|2014-08-28 06:37:10|37  /here it
does not work as expected , all the time it's subtracted  from this only
0|2014-08-28 04:00:32i want 1. subtraction is happen only for first event
(0|2014-08-28 04:00:32) - (255|2014-08-28 05:26:39)output 255|2014-08-28
05:26:39|26and rest of the time it just add the even time255|2014-08-28
05:26:41255|2014-08-28 05:27:41Again After ,255 sdata, it goes to next
one255|2014-08-28 05:26:41255|2014-08-28 05:27:410|2014-08-28
05:28:030|2014-08-28 05:28:410|2014-08-28 05:29:410|2014-08-28
05:30:410|2014-08-28 05:41:410|2014-08-28 05:42:410|2014-08-28
05:43:41255|2014-08-28 05:44:35255|2014-08-28 05:44:41255|2014-08-28
05:45:410|2014-08-28 06:34:58 then it subtract (0|2014-08-28 05:28:03) - 
(255|2014-08-28 05:44:35)out put should be255|2014-08-28 05:44:35|16 not
athis one255|2014-08-28 05:44:35|44plz help me out ...



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-should-i-found-time-difference-between-two-events-in-sqlite3-table-like-on-off-status-tp77617.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sorting by month with two dates input

[jic]

Greetings!

I have to create some reporting and I need to report on customers amount1 
and amount2 two based on the whole year of 2013.  There are two dates that 
are to be used as input: billdate and bdate.  I can do one or the other, 
but, I can not seem to get the correct date when using both.  The criteria 
is that if there is a billdate, that overides the bdate, so the billdate 
should be the one used and reported on.  If there is no billdate, then, 
bdate is used.  There is always a bdate as this is the date the project was 
opened, so all projects have a bdate.  But, not all projects may have a 
billdate. This is what I am trying...


SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM 
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate 
BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;


But this is not working correctly, as the beginning of the result, there is 
no number because billdate is empty.  So, I get a list like this:


Co01|13016.16|20024.46|
Co02|12280.0|110.0|
Co03|550.0|0.0|
Co04|1317.49|285.72|
Co05|747.0|0.0|
Co05|187330.3|39076.85|
Co07|14148.39|0.0|
Co08|156011.68|6725.09|
Co09|10890.92|142.98|
Co10|333.34|0.0|
Co11|294.39|214.29|
Co12|14857.29|0.0|
Co02|402.16|522.28|2012-02-15
Co04|3506.5|3976.11|2013-01-15
Co05|270.29|351.03|2013-01-15
Co06|1273.17|0.0|2013-01-15
Co10|16337.75|21217.83|2013-01-15

but there should always be a date.  Any help would be greatly appreciated. 
Thanks.


josé 


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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Jan Nijtmans]

2014-09-01 13:04 GMT+02:00 Andy Ling :
> Although this doesn't feel right having to keep extending the list of error 
> codes that are checked.
> I'm not sure what else can be done though. Maybe some compile options that 
> let you choose.

Here is a list of possible error-codes I found:
   

The value 13 is listed here as EACCES, I really doubt this one
should be added: If there really is an access problem, I
think that shouldn't be masked.

There are two entries that end with _FILE_NOT_FOUND, the other one is:
1310723  0x140003  S_rt11FsLib_FILE_NOT_FOUND
This suggests that for VxWorks an additional check for 0x140003
might work if you use the rt11 filesystem (whatever that is .)

Other possible candidates:
2686989  0x29000d  S_netDrv_NO_SUCH_FILE_OR_DIR
2949130  0x2d000a  S_nfsDrv_NO_SUCH_FILE_OR_DIR
7602186  0x74000a  S_cdromFsLib_NO_SUCH_FILE_OR_DIRECTORY

The list is not so long, if it helps then it is doable to just add them
all. But feedback on behavior on different file systems is
crucial, I wouldn't just add all of them blindly.

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> -Original Message-
> From: Andy Ling
> Sent: 29 August 2014 16:15
> To: '???'; sqlite-users@sqlite.org
> Subject: RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> I would add a printf statement in unixDelete rather than rely on a break point
> working.
> 


I finally had 5 minutes to try this out. I added the following to unixDelete

}else{
  printf ("Error deleting file %s error %d\n", zPath, errno) ;
  rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
}

I then ran the following

func call _open "/tgtsvr/testdb.sql" &
func call _execute "CREATE TABLE t (id INTEGER PRIMARY KEY ASC)" &

Where /tgtsvr is a mount of the file system on my Windows PC.  This generates 
the following

Error deleting file /tgtsvr/testdb.sql-wal error 13
Error executing statement CREATE TABLE t (id INTEGER PRIMARY KEY ASC) - disk 
I/O error
Error message disk I/O error

So as suspected, the host file system is returning yet another error code when 
deleting a file that doesn't exist.

This can be fixed by changing the patch to something like...

#if OS_VXWORKS
}else if( errno==0x380003  || errno == 13){ /* == S_dosFsLib_FILE_NOT_FOUND 
*/
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif

Although this doesn't feel right having to keep extending the list of error 
codes that are checked.
I'm not sure what else can be done though. Maybe some compile options that let 
you choose.

Regards

Andy Ling

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

Re: [sqlite] Sorting by month with two dates input

[Stephen Chrzanowski]

Untested, but first whack at it;

select * from yourtables order by ifnull(billdate,bdate)

Criteria met:
- A date posted prioritizing billdate and then bdate
- Sorted based on date



On Mon, Sep 1, 2014 at 1:55 AM, Keith Medcalf  wrote:

>
> >>I have to create some reporting and I need to report on customers amount1
> >>and amount2 two based on the whole year of 2013.  There are two dates
> that
> >>are to be used as input: billdate and bdate.  I can do one or the other,
> >>but, I can not seem to get the correct date when using both.  The
> criteria
> >>is that if there is a billdate, that overides the bdate, so the billdate
> >>should be the one used and reported on.  If there is no billdate, then,
> >>bdate is used.  There is always a bdate as this is the date the project
> was
> >>opened, so all projects have a bdate.  But, not all projects may have a
> >>billdate.
>
> >SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
> >SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t
> >FROM
> >LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR
> >bdate
> >BETWEEN '2013-01-01' AND '2013-12-31'
> >) x GROUP BY t, cust;
>
> is an improper interpretation of the problem statement.
>
> Based on the problem statement I should think that:
>
>   SELECT cust, sum(ProjFund), sum(Xtra8), reportdate
> FROM (SELECT cust, ProjFund, Xtra8, coalesce(billdate, bdate) as
> reportdate
> FROM LSOpenProjects
>WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
>  ) as X
> GROUP BY strftime('%m', reportdate), cust;
>
> or, equivalently:
>
>   SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate, bdate) as
> reportdate
> FROM LSOpenProjects
>WHERE strftime('%Y', coalese(billdate, bdate)) = '2013'
> GROUP BY strftime('%m', coalese(billdate, bdate)), cust;
>
> is correct.
>
> Proper operation requires that the billdate be NULL or contain a date.  If
> it was set to an empty or blank string rather than null, then of course the
> coalesce function will still return the empty or blank string that was
> stored there rather than the bdate.  If the data store is corrupted in this
> fashion, then you need to fix it first, and whatever applications caused
> the corruption of the data in the first place.
>
> Alternatively you can fix such ill-conceived database contents/design by
> replacing each use of
>
> coalesce(billdate, bdate) -> CASE WHEN billdate is null or
> length(rtrim(billdate) < 10)) THEN bdate ELSE billdate END
>
> in which case fixing the broken application(s) and database design will
> not be required.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser v3.3.0 released

[justin]

On 2014-08-31 16:07, Richard Hipp wrote:

On Sun, Aug 31, 2014 at 11:49 AM,  wrote:



We've updated everything we control, and just pushed out a v3.3.1
release that uses the new project naming (with no functional
changes).


... and it turns out there's already a project called "Database
Browser".  Which supports SQLite (and others).

  http://www.etl-tools.com/database-browser/overview.html

Looks like we'll need to do some thinking about new name v2 (or
v3). "Fluffy Pink Rabbits of Doom for SQLite" has a certain
appeal at this point... ;)

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