s?
> Using literals for one_type is not an acceptable option !
I'm not sure I understand. What else do you plan to use?
--
Igor Tandetnik
and not(boundary.VALUE between 10 and 20)), '')
) as COUNT_PRECEDING_IN_RANGE
from TEST T;
Performance will likely be, shall we say, less than stellar.
--
Igor Tandetnik
I humbly
offered, perchance?
--
Igor Tandetnik
ed from declared type, following the
rules described here: http://www.sqlite.org/datatype3.html#affname
> SQLite doesn't even have a varchar type.
... but it has TEXT column affinity, which "varchar" indicates.
--
Igor Tandetnik
On 11/16/2015 12:51 PM, Igor Korot wrote:
> Also, are you saying that if I have a integer field with the default value of
> 1,
> I will not be able to retrieve it with sqliteColumnText()?
You might be - I think SQLite will automatically convert it to the
string "1".
--
Igor Tandetnik
exists and is always a
string, and so it's OK to use sqliteColumnText unconditionally - either
assumption may not hold, and so it's not OK.
--
Igor Tandetnik
;t
> change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.
Because it's deliberately implemented this way, and documented to behave
this way. I suppose I don't quite understand the question.
--
Igor Tandetnik
"/system/device/com port/1" ) where the name is
> really sort of registry like and variable in length...
Color me dense, but I for one have no idea what you are trying to
achieve. Show table schema, sample data, and the desired outcome of the
query when run against that data.
--
Igor Tandetnik
ript - or commando to combine the tables excluding the
> overlapping rows?
Are you looking for something like
insert into TableOne
select * from TableTwo t2
where t2.timestamp > (select max(timestamp) from TableOne);
--
Igor Tandetnik
can choose just the
right order of operations to reduce the error, and choosing wrong means
errors accumulate and invalidate the result. See also:
https://en.wikipedia.org/wiki/Numerical_stability
--
Igor Tandetnik
ociated field (if any) that the value is associated with.
>
> Is there any way to retrieve that?
None that I know of.
--
Igor Tandetnik
oerces it to a
float, possibly losing precision). One of the problems in the OPs
example is that columns have no declared type, and thus BLOB affinity
(which pretty much means, anything goes and no conversions are performed).
--
Igor Tandetnik
https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
--
Igor Tandetnik
error
tends to accumulate.
See also: https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
--
Igor Tandetnik
ferent values for
net_non_pieces, and confirm that as many rows are being updated as
you've hoped. I suspect you may be disappointed.
--
Igor Tandetnik
clause be independent of the query it's limiting.
Now, one could argue that SQLite is too strict: it would be meaningful
for a LIMIT clause on a correlated subquery to have access to fields
from the enclosing query (if not the subquery itself). This doesn't
appear to be supported though.
--
Igor Tandetnik
rrt" - that is, always true. The actual limit value comes
from whichever row accidentally happens to be first.
--
Igor Tandetnik
st table T. Also, I'm breaking ties by rowid; your
original problem statement is underspecified unless there's a total
order on Addresses.
--
Igor Tandetnik
t
order by (CASE ...)
limit ifnull( (
select net_non_pieces from crrt_net_non net
where X.zip=net.zip and X.crrt=net.crrt
), 0)
);
Do you actually need NUM column in the resultset? That one would be
tricky to pull off.
--
Igor Tandetnik
e rowid in (
select x2.rowid from X x2 where X.zip=x2.zip and X.crrt=x2.crrt
order by someOrder
limit ifnull( (
select net_non_pieces from crrt_net_non net
where X.zip=net.zip and X.crrt=net.crrt
), 0)
);
--
Igor Tandetnik
esult of the query over that data. I, for one, have
difficulty following your description.
--
Igor Tandetnik
. Basically, any query could in principle become part
of the schema.
--
Igor Tandetnik
ng something.
> FYI the outcome is similar if I replace "NOT IN" with "IN". With:
>
>select * from item where itemcode in (select itemcode from tmp_salesitm)
>
> I get zero records
For me, it works the other way round (as I would expect). IN return all
records, since it's essentially equivalent to
select * from item where itemcode = itemcode;
NOT IN returns no records, since it's essentially equivalent to
select * from item where itemcode != itemcode;
Are you sure you are not mixing up the two?
--
Igor Tandetnik
erhaps:
create trigger no_delete before delete on log_table
begin
select raise(IGNORE);
end;
--
Igor Tandetnik
and update your
caches.
--
Igor Tandetnik
> on writing such a query I'd love to hear them!
Something along these lines (untested):
select group_concat(substr(word, length(:prefix)+1, 1), '')
from words where substr(word, 1, length(:prefix)) = :prefix
Not sure what recursive CTE has to do with it - what is there to recurse
over?
--
Igor Tandetnik
check for existence, and don't actually need the count.
--
Igor Tandetnik
t_id = t1.department_id
order by t2.salary desc limit 3
)
ORDER BY department_id, salary DESC, last_name;
--
Igor Tandetnik
s the existence of
SQLITE_DETERMINISTIC flag (which is a relatively recent invention).
--
Igor Tandetnik
On 9/9/2015 11:19 AM, Constantine Yannakopoulos wrote:
> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik wrote:
>
>> A comparison like this would not generally be a proper collation. The
>> equivalence relation it induces is not transitive - it's possible to have A
>&g
ison like this would not generally be a proper collation. The
equivalence relation it induces is not transitive - it's possible to
have A == B and B == C but A != C (when A is "close enough" to B and B
is "close enough" to C, but A and C are just far enough from each other).
--
Igor Tandetnik
. What does it mean to sort a list using a non-deterministic
collation - a collation that declares 'A' < 'B' sometimes, and 'A' > 'B'
other times?
--
Igor Tandetnik
s if you replace
"keyword" with "any sequence of characters", including spaces and
punctuation (except that a "naked", un-delimited sequence must be a
valid identifier).
--
Igor Tandetnik
On 8/27/2015 5:25 PM, Levente Kovacs wrote:
> Is there any way to get the 'id' of newly inserted row?
http://www.sqlite.org/c3ref/last_insert_rowid.html
http://www.sqlite.org/lang_corefunc.html#last_insert_rowid
--
Igor Tandetnik
ing
SQLite only with rigid type system, with which to compare.
--
Igor Tandetnik
On 8/26/2015 11:51 PM, Nicolas J?ger wrote:
> my error is obvious, but why sqlite doesn't return an error ?
http://sqlite.org/datatype3.html
--
Igor Tandetnik
ll" for all the calculations
necessary to produce a single row - not for the whole SELECT statement,
as you seem to have previously implied.
--
Igor Tandetnik
y to
understand SQLite database file format, or to know how to use SQLite API?
This is precisely the problem that ODBC was designed to solve. By making
"no ODBC driver" a requirement, you are painting yourself into a corner.--
Igor Tandetnik
er to a user data structure ?
Essentially, yes. It's an arbitrary value of your choice, that is passed
right back to your callback, as its first argument. From the
documentation: "The first argument to the callback is a copy of the
third argument to sqlite3_update_hook()"
--
Igor Tandetnik
r in their
interpretation of this line. There is no particular set of values that
the parser "must" end up with, assuming you use the word "must" with the
meaning specified in RFC 2119.
--
Igor Tandetnik
ot;".
False. You have conveniently neglected to mention the very next sentence
in the document: "If fields are not enclosed with double quotes, then
double quotes may not appear inside the fields."
Thus, "" is unambiguously an empty string, and """""" is unambiguously a
string consisting of two double quotes.
There are many real problems with CSV - no need to make up imaginary ones.
--
Igor Tandetnik
inserted, then
why do you declare it with INTEGER type? Make it TEXT.
> INSERT INTO 'test' (`testcol`) VALUES ('');
> SELECT * from 'test';
>
> and I get
> 1111.0
Can't reproduce. I get . The problem is somewhere in the part of the
setup you haven't shown.
--
Igor Tandetnik
work record. In other words, it's equivalent to
SELECT fpath
FROM home
WHERE fpath NOT IN (SELECT fpath FROM work)
AND home.ftype = 'f?;
--
Igor Tandetnik
27;B'
That's not quite the same. BETWEEN is inclusive of both ends. ('b'
BETWEEN 'a' and 'b') is true.
--
Igor Tandetnik
On 7/23/2015 10:17 AM, Staffan Tylen wrote:
> Please consider this:
>
> create table tbl1 (col1 text primary key);
> insert ...
> select * from tbl1 where substr(col1,1,1)='a';
Make it
WHERE col1>='a' and col1 < 'b'
This should use the index.
--
Igor Tandetnik
On 7/23/2015 9:10 AM, Bruno Schw?gli (CTModule AG) wrote:
> TZ is not used and is not set in our environment.
It is, however, used and set by C run-time, which is in turn used by
SQLite. In any case, _tzset() is how you tell C run-time to re-read time
zone settings from the OS.
--
I
ct time zone change, delete TZ environment variable (with _putenv or
SetEnvironmentVariable ) and call _tzset
--
Igor Tandetnik
On 7/21/2015 8:54 PM, Hayden Livingston wrote:
> I would like to compare two tables (of the same name) in two separate
> database files.
Are you looking for ATTACH DATABASE (
http://www.sqlite.org/lang_attach.html ) ?
--
Igor Tandetnik
y ignored.
So when you say you "switched to UTF-8" - you probably haven't.
--
Igor Tandetnik
s where keyword='animal' and rating > 3;
?
--
Igor Tandetnik
On 6/12/2015 5:33 AM, Simon Slavin wrote:
>
> On 12 Jun 2015, at 4:48am, Igor Tandetnik wrote:
>
>> http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20PostgreSQL%20Presentation.pdf
>> "With CTE and Windowing, SQL is Turing Complete."
>
&
queries that are beyond what it can do?
http://assets.en.oreilly.com/1/event/27/High%20Performance%20SQL%20with%20PostgreSQL%20Presentation.pdf
"With CTE and Windowing, SQL is Turing Complete."
--
Igor Tandetnik
On 6/2/2015 7:34 AM, Richard Warburton wrote:
> 2) Can I auto fill Id to UID on insert instead of having to do two
> operations?
Yes, with an AFTER INSERT trigger
--
Igor Tandetnik
unction, I'm pretty sure.
--
Igor Tandetnik
way special. It will
write to the database exactly as many bytes as you tell it to.
--
Igor Tandetnik
On 5/21/2015 3:51 PM, Baruch Burstein wrote:
> Does that mean that I can't prepare this: "SAVEPOINT :name"?
Yes.
> What would be the recommended method of preventing SQL injection for this?
Do you plan to run SAVEPOINT "user-provided-string"? What for, if you
On 5/21/2015 1:37 PM, Valentin Davydov wrote:
> Moreover, there are widespread examples of colliding UUIDs, say
> EBD0A0A2-B9E5-4433-87C0-68B6B72699C7.
This particular issue (
http://lists.gnu.org/archive/html/bug-parted/2011-06/msg00026.html )
didn't happen because two independent UUID generati
y delete the database file, then re-open
(which will create a new file) and re-create the schema.
--
Igor Tandetnik
On 4/21/2015 11:01 AM, Jay Smith wrote:
> Before I sent the last message I had signed up to become a user.
> My previous message was bounced. WHY
I, for one, have received both your original and this new message.
--
Igor Tandetnik
he query you are
preparing is not the query you think you are preparing. Print "command"
and strlen(command) right before sqlite3_prepare_v2 call, to double-check.
--
Igor Tandetnik
On 4/7/2015 8:04 PM, Kumar Suraj wrote:
> if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command),
> &newStmt, NULL) ) != SQLITE_OK )
command: undeclared identifier.
--
Igor Tandetnik
Could you show some sample data, in the order you want it to appear?
Perhaps the pattern would be easier to discern this way.
--
Igor Tandetnik
0.119 | 0.602
select SerialNumber,
max(case Stim when 'V0' then Resp else null end) V0,
max(case Stim when 'V5' then Resp else null end) V5
from MyTable group by SerialNumber;
--
Igor Tandetnik
n't it be strange to have
functions but no mechanism for them to take parameters? A field from an
outer query is to a correlated subquery what a parameter is to a
function in a traditional imperative language.
--
Igor Tandetnik
lect matter?
--
Igor Tandetnik
use columns from the containing statement. Read
about a "correlated subquery" in your favorite SQL textbook.
--
Igor Tandetnik
uch column? There are in fact two such columns,
mentioned elsewhere in the same query: d.emis_number and p.emis_number.
--
Igor Tandetnik
BETICS)
emis_number in the sub-select is DIABETIC_ISSUES_LAST.emis_number, not
DIABETICS.emis_number
> So, how should I do this?
First, you have to figure out *what* you are trying to do. In light for
the fact that DIABETICS doesn't have a column named emis_number, it's
not at all clear.
--
Igor Tandetnik
oes Clearbindings come indeed before finalize?
I have yet to find a reason to call sqlite3_clear_bindings. It's needed
very rarely, if ever. It's absolutely pointless right before finalize.
--
Igor Tandetnik
t you do afterwards with the
pointer it returns.
Also, do you eyeball the value in the debugger? The debugger would
assume that a variable of type char* points to a NUL-terminated string,
and display it as such.
--
Igor Tandetnik
On 3/7/2015 11:42 AM, Dave wrote:
> Now when trying to use the database I see that I should have made 1
> table with all the related data (I think) and am trying to copy one
> column of data at a time to the "main" table. Can that be done and if so
> how? The data in all the columns has to line u
On 3/5/2015 11:58 PM, Igor Tandetnik wrote:
> If the latter is OK, then you can have a single trigger doing something
> like this:
>
> insert or replace into Clean(X, Y, Z)
> select
> case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
> case when new.Y = ifnu
m Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid);
If you do need to translate a "no-op" update into setting a value in
Clean, then I don't see a way around one trigger per column. I can't
think of a way for a whole-table trigger to distinguish between a column
not
table which is then sorted. I'd like to avoid the memory
> consumption produced by this query plan...
If you "ORDER BY a, b desc" SQLite should be using index scan without an
explicit sort step. Does this not happen?
--
Igor Tandetnik
. There's ?N syntax you can use in your SQL statement to refer
to a parameter number N. It may be confusing if ?1 had to be bound with
index 1 in some contexts, and index 0 in others.
--
Igor Tandetnik
ileId, FileDirID ancestor, '' as path from Files
union all
select FileId, ParentID, '/' || DirName || path
from FileDirs join Dirs on (ancestor = DirId)
where DirID != 0
)
select FileId, FileName, path
from FileDirs join Files using (FileId)
where ancestor = 0;
--
Igor Tandetnik
in DirTree on (FileDirID=DirID);
It's a bit unfortunate that you made the root a parent of itself. Forces
the query to make an extra check to avoid infinite recursion.
--
Igor Tandetnik
row (sqlite3_step was called, and returned
SQLITE_ROW).
Why both are needed, I'm not sure.
--
Igor Tandetnik
7;a' table have indicators as to which table in b.db
> to use.
What failure mode do you envision that would be avoided by this design?
--
Igor Tandetnik
t would have an extra column holding the "original source"
indicator - a value that indicates which of the three tables this row
originated from. Now, in table "a" store this indicator where you
planned to store the table name.
--
Igor Tandetnik
On 2/24/2015 4:37 PM, russ lyttle wrote:
> I'm trying to create a field in a table to hold the name of a second
> table, then retrieve that name for use.
You can't. SQL doesn't work this way. Reconsider your design.
--
Igor Tandetnik
On 2/24/2015 12:42 PM, Jonathan Camilleri wrote:
> Unusual output when trying a SQL Select statement from the command line
So what's unusual about it? What specifically seems to be the problem?
--
Igor Tandetnik
p sees is the same data
that a regular SELECT issued on the same connection would see; it
reflects all changes made by any committed transactions. Some of that
data may be physically located in the main DB file, and some of it in
the WAL file.
--
Igor Tandetnik
On 2/21/2015 1:43 PM, russ lyttle wrote:
> I'm new to sqlite and would like some intermediate to advanced tutorial
> and reference material.
http://sqlite.org/docs.html
--
Igor Tandetnik
hen the
> log file will not be restarted?
"Begins a transaction" != "is making use of the WAL".
--
Igor Tandetnik
. To be more
selective, add conditions in the last WHERE clause to taste.
--
Igor Tandetnik
x27;t know how to grab that without this step.
Where is this information coming from? How do you know that RateType =
'10%' corresponds to 0.20 multiplier? How is your hypothetical trigger
supposed to know that?
--
Igor Tandetnik
bClass='PM' AND lang='DE-DE') condition was
good enough to retrieve a unique RateType in step 1, then it should be
good enough to identify a unique record to update in step 2. Why do you
believe an extra condition of (RateType = '10%') is necessary?
--
Igor Tandetnik
On 2/15/2015 6:54 PM, Bart Smissaert wrote:
> Result is shown below.
>
> *Drug**Sensitive*
> *Resistant**Ratio*Ertapenem 10201Meropenem301Pip/Tazobactam301
> Cefalexin/Cefdrxl4070.85Gentamicin310.75CiprofloxacinS1570.68Amoxicillin2115
> 0.58Ampi
Items join Descriptions
group by NAME;
-- or
select NAME,
(select count(*) from Descriptions where INSTR(FULL_TEXT,
DEFINITION1) > 0),
(select count(*) from Descriptions where INSTR(FULL_TEXT,
DEFINITION2) > 0)
from Items;
The first one would probably work faster.
--
Igor Tandetnik
e.
These approaches use stock capabilities of SQLite and don't require
heroic efforts (like patching source or implementing a virtual table).
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On 2/9/2015 7:55 AM, R.Smith wrote:
Which of course works fine if you have a predeterminable set of columns
to extract, such as Months in the above case. How can I do this sort of
thing (i'm only interested in similar results, the method is not
important) for an indetermintate set of columns?
Y
On 2/7/2015 8:47 AM, Abdul Aziz wrote:
Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?
When Tim said "Read this", he meant it.
http://www.sqlite.org/datatype3.html answers your questions (but only if
you read it).
--
Igor
tables
in joining operation.
The relationship between em and lo is one-to-one. The engine is most
likely looping over one, and looking up rows in the other by primary
key. Same with idv and mny.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite
ows, and all you
need is a quick count, then I would suggest adding an ON INSERT and ON
DELETE triggers that would update a count stored in a separate,
singleton table.
I'm not sure what you mean by "dump an approximate snapshot".
--
Igor Tandetnik
_
he necessity of myFunc(NEW.*) syntax follows from
this. Why can't the trigger call myFunc(new.colA, new.colB)?
You can write a variadic custom function (one that can be called with an
arbitrary number of arguments), if that's what you are asking.
--
Igor Tandetnik
#x27;s what makes it "signaling"),
depending on how FPU is configured; which in turn could happen merely by
passing it to a function, or returning from one. Quiet NaNs should
round-trip just fine.
--
Igor Tandetnik
___
sqlite-users mailing lis
, say, round-trip
through a string representation at any point?
I'm pretty sure that, if you put a value into a column with correct
affinity using sqlite3_bind_double, you'd get the exact same value out
using sqlite3_column_double (signalling NaN possibly excepted).
--
but 4 values were supplied
What I would like is to add t0 to t1 so that t1 now hasa|b|c|d|e|f
insert into t1
select 'a', 'b', 'c', *
from t0;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
order of the "value" column in Clemens example.
What do you mean by "order of the "value" column"? The order in which
rows happened to be inserted into the table? Why should that order
matter for anything?
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
201 - 300 of 3828 matches
Mail list logo
