[sqlite] Query help

[Martin Engelschalk]

Sorry, i was too fast.

Correction

SELECT engine,coalesce(groupname,'*') as 
groupname,databasename,key,value FROM EnginePreferences left join groups 
on (groups.groupid = EnginePreferences.groupid);

OR

SELECT engine,groupname,databasename,key,value
  FROM EnginePreferences
 left join (select groupid,groupname from groups
   union
 select 0, '*') g_helper  on ( g_helper.groupid = 
EnginePreferences.groupid);

Martin
Am 23.03.2015 um 10:04 schrieb Martin Engelschalk:
> Hi,
>
> SELECT engine,coalesce(groupname,*) as 
> groupname,databasename,key,value FROM EnginePreferences left join 
> groups on (groups.groupid = EnginePreferences.groupid);
>
> OR
>
> SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
>  FROM EnginePreferences
> left join (select groupid,groupname from groups
>   union
> select 0, '*') g_helper  on ( g_helper.groupid = 
> EnginePreferences.groupid);
>
> HTH
> Martin
>
> Am 23.03.2015 um 09:50 schrieb Marco Bambini:
>> I have a table EnginePreference:
>> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, 
>> databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value 
>> TEXT, groupid INTEGER, UNIQUE(engine,databasename,key))
>>
>> and a table Groups:
>> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT 
>> UNIQUE COLLATE NOCASE)
>>
>> I need to select from EnginePreferences replacing groupid with 
>> groupname and I can do that with:
>> SELECT engine,groupname,databasename,key,value FROM EnginePreferences 
>> left join groups on (groups.groupid = EnginePreferences.groupid);
>>
>> What I really need is ALSO to replace groupname with * if groupid is 0.
>> Please note that groupid 0 is never written into the Groups table so 
>> my original query would return NULL as groupname with groupid is 0 
>> instead of the required "*".
>>
>> Any idea?
>> -- 
>> Marco Bambini
>> http://www.sqlabs.com
>> http://twitter.com/sqlabs
>> http://instagram.com/sqlabs
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[Martin Engelschalk]

Hi,

SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value 
FROM EnginePreferences left join groups on (groups.groupid = 
EnginePreferences.groupid);

OR

SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
  FROM EnginePreferences
 left join (select groupid,groupname from groups
   union
 select 0, '*') g_helper  on ( g_helper.groupid = 
EnginePreferences.groupid);

HTH
Martin

Am 23.03.2015 um 09:50 schrieb Marco Bambini:
> I have a table EnginePreference:
> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT 
> COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, 
> UNIQUE(engine,databasename,key))
>
> and a table Groups:
> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE 
> COLLATE NOCASE)
>
> I need to select from EnginePreferences replacing groupid with groupname and 
> I can do that with:
> SELECT engine,groupname,databasename,key,value FROM EnginePreferences left 
> join groups on (groups.groupid = EnginePreferences.groupid);
>
> What I really need is ALSO to replace groupname with * if groupid is 0.
> Please note that groupid 0 is never written into the Groups table so my 
> original query would return NULL as groupname with groupid is 0 instead of 
> the required "*".
>
> Any idea?
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> http://instagram.com/sqlabs
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[Marco Bambini]

I have a table EnginePreference:
CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT 
COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, 
UNIQUE(engine,databasename,key))

and a table Groups:
CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE COLLATE 
NOCASE)

I need to select from EnginePreferences replacing groupid with groupname and I 
can do that with:
SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join 
groups on (groups.groupid = EnginePreferences.groupid);

What I really need is ALSO to replace groupname with * if groupid is 0.
Please note that groupid 0 is never written into the Groups table so my 
original query would return NULL as groupname with groupid is 0 instead of the 
required "*".

Any idea?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs

Re: [sqlite] Query help

[Joseph L. Casale]

> There are two ways to rewrite this query, with a correlated subquery:
> 
>  SELECT *
>  FROM table_a AS x
>  WHERE NOT EXISTS (SELECT 1
>FROM table_b AS y
>WHERE x.id  = y.id
>  AND x.col = y.col)
> 
> or with an outer join:
> 
>  SELECT x.*
>  FROM  table_a AS x
>  LEFT JOIN table_b AS y USING (id, col)
>  WHERE y.id IS NULL

Hi Clemens,
Totally missed the boat on those two.

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

Re: [sqlite] Query help

[Clemens Ladisch]

Joseph L. Casale wrote:
>  SELECT x.id, x.col
>   FROM table_a x
> EXCEPT
> SELECT y.id, y.col
>   FROM table_b y
> JOIN table_a .

This query is not complete, but as far as I can tell, it is intended to
return table_a rows that do not have a matching table_b row.  Is this
correct?

> now I need to return row ids for each record on the left and these
> certainly don't match the row ids from the right hand side.

There are two ways to rewrite this query, with a correlated subquery:

 SELECT *
 FROM table_a AS x
 WHERE NOT EXISTS (SELECT 1
   FROM table_b AS y
   WHERE x.id  = y.id
 AND x.col = y.col)

or with an outer join:

 SELECT x.*
 FROM  table_a AS x
 LEFT JOIN table_b AS y USING (id, col)
 WHERE y.id IS NULL


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

[sqlite] Query help

[Joseph L. Casale]

I have a query I am trying to rewrite as efficient as possible and not clear.

 SELECT x.id, x.col 
  FROM table_a x
EXCEPT
SELECT y.id, y.col 
  FROM table_b y
JOIN table_a .

The right hand part of the except performs several joins and already duplicates
the entire query on the left hand side, this works fine none the less, but now I
need to return row ids for each record on the left and these certainly don't 
match
the row ids from the right hand side. The above query would most certainly 
return
every record from the lhs.

What is the most efficient way to perform this without creating further queries
that re-evaluate the same data?

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

Re: [sqlite] Query help

[Simon Slavin]

> On 8 Jul 2014, at 9:01pm, Paul Sanderson  wrote:
> 
> 0, microsoft, mac
> 1, oracle, mac
> 2, oracle, pc

SELECT t1.recno, t2.name, t3.name FROM t1
 JOIN t2 ON t2.a = t1.a
 JOIN t3 ON t3.b = t1.b
 ORDER BY t1.recno

If you have lots of data on your tables this command will execute far more 
quickly if t1.recno, t2.a and t3.b are either primary keys or indexes.  The way 
your database is structured, they would all make excellent primary keys.

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

Re: [sqlite] Query help

[Igor Tandetnik]

On 7/8/2014 4:01 PM, Paul Sanderson wrote:

i have three tables

create table t1 (recno int, a int, b int)
create table t2 (a int, name text)
create table t3 (b int, name text)

I want to create a query that lists all rows in t1 but rather than the
integers a and b I want to display the associated names from t2 and t3


select recno, t2.name, t3.name
from t1 join t2 using (a) join t3 using (b);

--
Igor Tandetnik

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

[sqlite] Query help

[Paul Sanderson]

I suspect this is easy

i have three tables

create table t1 (recno int, a int, b int)

create table t2 (a int, name text)

create table t3 (b int, name text)

I want to create a query that lists all rows in t1 but rather than the
integers a and b I want to display the associated names from t2 and t3

so if t1 had

0, 1, 2
1, 2, 2
2, 2, 1

t2 is
1 microsoft
2 oracle

t3 is
1 pc
2 mac

a select * from t1  (using some sort of join)
would show

0, microsoft, mac
1, oracle, mac
2, oracle, pc

Hope this is clear.

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

Re: [sqlite] Query help

[Joseph L. Casale]

> Will the SQL 1969 "EXCEPT" compound operator not work for some reason?

Worked perfect, my sql is weak as I didn't even know of this one...
Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query help

[Keith Medcalf]

Will the SQL 1969 "EXCEPT" compound operator not work for some reason?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Saturday, 27 July, 2013 15:24
> To: 'sqlite-users@sqlite.org'
> Subject: [sqlite] Query help
> 
> Hey guys,
> I am trying to left join the results of two selects that both look
> exactly like this:
> 
>   SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM
> table_a
> 
> Both tables have the exact data type and format, I need to reformat
> each tables
> results, then join and return only what is in table_a and not in
> table_b.
> 
> Any guidance on how one might do this in sqlite?
> Thanks!
> jlc
> ___
> 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

[sqlite] Query help

[Joseph L. Casale]

Hey guys,
I am trying to left join the results of two selects that both look exactly like 
this:

  SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM table_a

Both tables have the exact data type and format, I need to reformat each tables
results, then join and return only what is in table_a and not in table_b.

Any guidance on how one might do this in sqlite?
Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

[Keith Medcalf]

select * 
  from tab
 where not exists (select * 
 from tab as tab2 
where num1 = t1.num2);

As you see, there are many ways to obtain the result you want ...

This is a direct translation of the English "I want to identify each row where 
num2 does not have a matching num1".

The most efficient way to do this will depend on the characteristics of the 
data.  For big tables

Select *
  From tab
 Where num2 not in (select distinct num1
  From tab);

Will be the most efficient ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Paul Sanderson
> Sent: Monday, 20 May, 2013 08:00
> To: General Discussion of SQLite Database
> Subject: [sqlite] query help
> 
> I have a table of the form
> 
> create table tab (num int1 unique, num2, int)
> 
> for each row for num2 there is usually a matching num1. But not always.
> 
> I want to identify each row where num2 does not have a matching num1
> 
> example data might be
> 
> num1  num2
> 1  3
> 2  3
> 3  2
> 4  1
> 5  11
> 6  3
> 7  9
> 
> in this example my query would return rows 5 and 7 as there is no match on
> num1 for 11 and 9
> 
> Any ideas, cheers.
> ___
> 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] query help

[Igor Tandetnik]

On 5/20/2013 7:59 AM, Paul Sanderson wrote:

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1


select * from tab where num2 not in (select num1 from tab);

--
Igor Tandetnik

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

Re: [sqlite] query help

[Jay A. Kreibich]

On Mon, May 20, 2013 at 12:59:45PM +0100, Paul Sanderson scratched on the wall:
> I have a table of the form
> 
> create table tab (num int1 unique, num2, int)
> 
> for each row for num2 there is usually a matching num1. But not always.
> 
> I want to identify each row where num2 does not have a matching num1
> 
> example data might be
> 
> num1  num2
> 1  3
> 2  3
> 3  2
> 4  1
> 5  11
> 6  3
> 7  9
> 
> in this example my query would return rows 5 and 7 as there is no match on
> num1 for 11 and 9

  Join the table to itself using an outer join on the condition that
  num2 == num1.  Look for rows where num1 is NULL, indicating no join
  was found.  Only works if num1 is never NULL in the DB.

  I need to run.  Perhaps someone else can provide an example if that's
  not making sense.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

[Michael Black]

I think this does what you want.  

create table tab (num1 int unique,num2 int);
insert into tab values(1,3);
insert into tab values(2,3);
insert into tab values(3,2);
insert into tab values(4,1);
insert into tab values(5,11);
insert into tab values(6,3);
insert into tab values(7,9);

sqlite> select t2.num1,t2.num2 from tab t2 where (select count(*) from tab
t1 where t2.num2 == t1.num1) == 0;
5|11
7|9

If you don't want to see num2 in the query answer:
sqlite> select num1 from (select t2.num1,t2.num2 from tab t2 where (select
count(*) from tab t1 where t2.num2 == t1.num1) == 0);
5
7





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Monday, May 20, 2013 7:00 AM
To: General Discussion of SQLite Database
Subject: [sqlite] query help

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1

example data might be

num1  num2
1  3
2  3
3  2
4  1
5  11
6  3
7  9

in this example my query would return rows 5 and 7 as there is no match on
num1 for 11 and 9

Any ideas, cheers.
___
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

[sqlite] query help

[Paul Sanderson]

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1

example data might be

num1  num2
1  3
2  3
3  2
4  1
5  11
6  3
7  9

in this example my query would return rows 5 and 7 as there is no match on
num1 for 11 and 9

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

Re: [sqlite] Query Help

[Navaneeth.K.N]

Hello,

Thanks for the help.

On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik  wrote:
> On 3/10/2013 11:06 AM, Navaneeth.K.N wrote:
>>
>> select  distinct(lower(pattern)) as pattern, id  from symbols where
>> value1 = ?1 or value2 = ?1  group by pattern
>>
>> This returns
>>
>> "cchu", "20907"
>> "chchu", "20879"
>> "chu", "20935"
>>
>> This is distinct set of patterns, but I am not getting the list
>> ordered by id. Even if I add a "order by id" to the above query, it
>> sorts only the above set. But what I need is to get in the following
>> order.
>>
>>
>> "chu", "20851"
>> "chchu", "20879"
>> "cchu", "20907"
>
>
> Why do you expect 'chu' to be accompanied by an ID of 20851, and not 20935?
> These seem to be equally valid choices?

More than the id, I care about order. When I use my first query, "chu"
comes at the end. But since it has a lower id, it should be first.
This order defined how my application behaves.

Doing min(id) did the trick. Thanks for the help.

Thanks to James also for the additional information.

>
> If you want, say, the smallest of the two, just say so:
>
> select lower(pattern) as pattern, min(id) as minid
>
> from symbols where value1 = ?1 or value2 = ?1
> group by pattern order by minid;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Query Help

[James K. Lowden]

On Sun, 10 Mar 2013 20:36:47 +0530
"Navaneeth.K.N"  wrote:

> select  distinct(lower(pattern)) as pattern, id  from symbols where
> value1 = ?1 or value2 = ?1  group by pattern

Igor's answer is correct.  I just want to point out what looks like a
misunderstanding on your part regarding "distinct" because it may help
you to understand Igor's answer better.  

You wrote 

select  distinct(lower(pattern))

as though "distinct" were a function.  It's not.  The "distinct" keyword
modifies the query such that distinct *rows* are returned.  The whole
row, that is, not a particular column.  

> "chu", "20851"
> "chchu", "20879"
> "cchu", "20907"
> "chu", "20935"

These are already distinct rows.  The two you're thinking about

"chu", "20851"
and
"chu", "20935"

are distinct because they differ in their "id" column.  

That leaves you with a small problem: you want a distinct set of
patterns, but for each pattern you need an id, and some patterns have
more than one id.  

Whenever you want "one of Y" or "something about Y" for every X, you
need a GROUP BY clause.  Your X is "pattern" and your Y is "id".  But
which id?  Igor suggests you take the minimum one, 

select lower(pattern) as pattern, min(id) as minid
...
group by pattern

which is certainly a fine choice if you don't care.  

HTH.  

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

Re: [sqlite] Query Help

[Igor Tandetnik]

On 3/10/2013 11:06 AM, Navaneeth.K.N wrote:

select  distinct(lower(pattern)) as pattern, id  from symbols where
value1 = ?1 or value2 = ?1  group by pattern

This returns

"cchu", "20907"
"chchu", "20879"
"chu", "20935"

This is distinct set of patterns, but I am not getting the list
ordered by id. Even if I add a "order by id" to the above query, it
sorts only the above set. But what I need is to get in the following
order.


"chu", "20851"
"chchu", "20879"
"cchu", "20907"


Why do you expect 'chu' to be accompanied by an ID of 20851, and not 
20935? These seem to be equally valid choices?


If you want, say, the smallest of the two, just say so:

select lower(pattern) as pattern, min(id) as minid
from symbols where value1 = ?1 or value2 = ?1
group by pattern order by minid;

--
Igor Tandetnik

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

[sqlite] Query Help

[Navaneeth.K.N]

Hi Guys,

I have a table named "symbols". I am writing the below query.

select  lower(pattern), id  from symbols where value1 = ?1 or value2 = ?1

This returned the following results.

"chu", "20851"
"chchu", "20879"
"cchu", "20907"
"chu", "20935"

>From this, I need only distinct patterns. So I tried this query.

select  distinct(lower(pattern)) as pattern, id  from symbols where
value1 = ?1 or value2 = ?1  group by pattern

This returns

"cchu", "20907"
"chchu", "20879"
"chu", "20935"

This is distinct set of patterns, but I am not getting the list
ordered by id. Even if I add a "order by id" to the above query, it
sorts only the above set. But what I need is to get in the following
order.


"chu", "20851"
"chchu", "20879"
"cchu", "20907"

This is ordered by id and only distinct patterns. I am not able to
come up with a query which does the above. Any help would be great.


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

Re: [sqlite] query help with inline conversion of hex data

[Richard Hipp]

On Wed, Apr 18, 2012 at 6:38 PM, Jim Sanders wrote:

>
> It's disappointing there isn't a simple "unhex()" function to do the
> reverse of the already existing function.
>

Code to implement such a function is contained in the SQLite test suite.
See it here:

   http://www.sqlite.org/src/artifact/6232d722a4d?ln=371-395



-- 
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] query help with inline conversion of hex data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/04/12 15:38, Jim Sanders wrote:
> It's disappointing there isn't a simple "unhex()" function to do the 
> reverse of the already existing function.   That would be a very
> elegant solution to this issue, as I am so close.

unhex isn't anywhere near as simple as you think.  For your specific case
it is, but in the general case it isn't.  For example what character set
is used?  What do you do when an error is encountered (eg an odd number of
digits, invalid hex)?

Also remember that SQLite is a library.  It is running directly inside the
app, and is not on the other end of a network connection with a surly
administrator.  You can modify it and enhance it in any way you deem fit.

> I will investigate the dynamic extension thing tomorrow.  I don't have
> any experience with that, so I'm not sure if it would work but it's
> worth looking into.

There is a very dated reference page here:

  http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

> In the worst case scenario I could add PHP into the mix and open the
> SQLite databases directly to do a query + host processing.   I just
> hate to break out of the confines of the Spiceworks application and
> it's built in reporting capabilities because I was hoping to share my
> finished report back with their community as it would be very useful to
> a lot more folks.

It looks like Spiceworks has a builtin extension mechanism.  At the very
least it has a wierd batch language, but presumably it can call external
binaries/scripts.

Maybe the user groups will have some advice on other approaches.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+PSyYACgkQmOOfHg372QQ9/gCgwOYadJdd5HaO+Vl3TatT85nW
PowAoJZXUHv/mwzBwM+IY7CvFRaoSnZu
=Kp6j
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help with inline conversion of hex data

[Jim Sanders]

Well yes I agree the stored format isn't ideal, but unfortunately I have no
control over that.   These are hard drive serial numbers and that is the
format that Microsoft WMI spits them out in.   It's possible that the
Spiceworks application could be modified to decode those hex strings it
gets back from WMI before it saves it in the database, but again, that
isn't in my control either. :(

It's disappointing there isn't a simple "unhex()" function to do the
reverse of the already existing function.   That would be a very elegant
solution to this issue, as I am so close.

I will investigate the dynamic extension thing tomorrow.  I don't have any
experience with that, so I'm not sure if it would work but it's worth
looking into.

In the worst case scenario I could add PHP into the mix and open the SQLite
databases directly to do a query + host processing.   I just hate to break
out of the confines of the Spiceworks application and it's built in
reporting capabilities because I was hoping to share my finished report
back with their community as it would be very useful to a lot more folks.

Thanks,

Jim

On Wed, Apr 18, 2012 at 5:47 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 18/04/12 14:39, Jim Sanders wrote:
> > I found that, but I need essentially the reverse of that.   The data
> > stored in SQL is 3061626364 and I need to convert it back to 0abcd
> > (from your example)
>
> The correct fix is to get them to stop storing strings that way :-)
> Strings can and should be stored as strings.
>
> You would need to register a user defined function with SQLite that takes
> the hex encoding and returns a string.  There is no other solution I can
> think of as you ruled out a 'host' language.
>
> It is possible to make dynamically loaded extensions that can provide the
> function although it will also require SQLite to have been called to
> enable them which is unlikely.
>
> If your final output can be html then you could output the field as is,
> and have some Javascript that finds them and converts to text.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk+PNowACgkQmOOfHg372QSCwgCgp9jYnBJ09zhrNNg6DYy9pBbI
> kuEAoKl0VFB5eghuYe6+14Xx6dO0cigE
> =4WcZ
> -END PGP SIGNATURE-
> ___
> 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] query help with inline conversion of hex data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/04/12 14:39, Jim Sanders wrote:
> I found that, but I need essentially the reverse of that.   The data
> stored in SQL is 3061626364 and I need to convert it back to 0abcd
> (from your example)

The correct fix is to get them to stop storing strings that way :-)
Strings can and should be stored as strings.

You would need to register a user defined function with SQLite that takes
the hex encoding and returns a string.  There is no other solution I can
think of as you ruled out a 'host' language.

It is possible to make dynamically loaded extensions that can provide the
function although it will also require SQLite to have been called to
enable them which is unlikely.

If your final output can be html then you could output the field as is,
and have some Javascript that finds them and converts to text.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+PNowACgkQmOOfHg372QSCwgCgp9jYnBJ09zhrNNg6DYy9pBbI
kuEAoKl0VFB5eghuYe6+14Xx6dO0cigE
=4WcZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help with inline conversion of hex data

[Jim Sanders]

I found that, but I need essentially the reverse of that.   The data stored
in SQL is 3061626364 and I need to convert it back to 0abcd (from your
example)

On Wed, Apr 18, 2012 at 5:35 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 18/04/12 14:30, Jim Sanders wrote:
> > But I can't figure out a way do this dynamically for all rows.
>
> There is a builtin hex() function.
>
>  sqlite> create table foo(bar); insert into foo values('0abcd');
>  sqlite> select hex(bar) from foo;
>  3061626364
>
> This applies when databases are in the most likely utf8 encoding.  In
> utf16 encoding each character is two bytes(*).  'pragma encoding' will
> tell you which is in use.
>
> (*) Unicode is more complicated than that.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk+PM6AACgkQmOOfHg372QRv0wCghYRb3wBoTwKyMj6NTfuzFNw6
> +RYAn3gj8vo0PEFJph1wnMH0bPZwkKDr
> =mtot
> -END PGP SIGNATURE-
> ___
> 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] query help with inline conversion of hex data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/04/12 14:30, Jim Sanders wrote:
> But I can't figure out a way do this dynamically for all rows.

There is a builtin hex() function.

  sqlite> create table foo(bar); insert into foo values('0abcd');
  sqlite> select hex(bar) from foo;
  3061626364

This applies when databases are in the most likely utf8 encoding.  In
utf16 encoding each character is two bytes(*).  'pragma encoding' will
tell you which is in use.

(*) Unicode is more complicated than that.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+PM6AACgkQmOOfHg372QRv0wCghYRb3wBoTwKyMj6NTfuzFNw6
+RYAn3gj8vo0PEFJph1wnMH0bPZwkKDr
=mtot
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query help with inline conversion of hex data

[Jim Sanders]

I am trying to write a query to a SQLite database (Spiceworks) in which I
need to transform some data as part of the SELECT statement.

The query I *want* to use looks like this (includes some pseudo code):

SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,
  d.service_pack_major_version,
  pd.model as HDModel,
  --- check for hex string with leading zero (30) characters
  CASE WHEN pd.serial like "303030%" THEN
  LTRIM(X'pd.serial', '0')
  -- check for hex string with leading space (20) characters
  WHEN pd.serial like "202020%" THEN
  LTRIM(X'pd.serial')
  -- check for any other serial with leading zero characters
  WHEN pd.serial like "00%" THEN
  LTRIM(pd.serial, '0')
  -- fall back to just presenting whatever is in the column
  ELSE
 pd.serial
  END  as HDSerial,
  cast(pd.firmware as TEXT) as 'HD Firmware',
  cast((pd.size / 10) as NUMERIC) as SizeGB,
  pd.failure_prediction as 'SMART Enabled',
  pd.status as 'SMART Status'

FROM devices d

  INNER JOIN physical_disks pd ON (d.id = pd.computer_id)

WHERE SizeGB > 20 AND d.name like "0039%"

ORDER BY d.model, d.name ASC;

Everything in that query works perfectly for me except the X'pd.serial'
lines.   I want to put the value of the pd.serial field into the X'...'
format and be returned the decoded hex data.   I can manually do it one by
one and get the data I want:

sqlite> select X'3030303030303030313131343330423137454342';
111430B17ECB


But I can't figure out a way do this dynamically for all rows.   I'm hoping
there is just a bit of syntax I am missing or another function or method I
should be using.   I see lots and lots of documentation about using that
X'...' format, but always in the examples they are static one-off values,
and never as part of a larger SELECT query.

For what it's worth, I did discover that I can concatenate a string to
create the X'...' format I want, but there doesn't seem to be anyway to
actually evaluate that into the converted data.  (instead I just get back
the whole X'3030303030303030313131343330423137454342'   as a text string.

The concatenation method I used is:

('X''' || pd.serial || )


Unfortunately, I don't have the ability to just grab the raw data out of
SQL and then post-process it with PHP or another language for this specific
task (which would be much easier for me).   I'm trying to keep this as a
built-in report for Spiceworks, and that limits me to only what a SQL query
can spit out.

Any guidance offered is appreciated!

Thanks,

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

Re: [sqlite] Query help

[Marco Bambini]

Thanks a lot Igor, it's perfect now.

--
Marco Bambini
http://www.sqlabs.com








On Jun 12, 2011, at 4:46 PM, Igor Tandetnik wrote:

> Marco Bambini  wrote:
>> I have a table Clients defined as (simplified version):
>> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity 
>> TEXT, ping_timeout INTEGR);
>> 
>> ping_timeout was a global property so in order to get a list of all clients 
>> timedout I used a query like (C code):
>> snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
>> datetime('now', '-%d.00 seconds', 'localtime');",
>> settings.ping_timeout); 
>> 
>> things are recently changed in my app and ping_timeout is now a client 
>> property set inside the Clients table (and no longer a
>> global property), so I would like to perform the query using just the 
>> ping_timeout column in the Clients table instead of the
>> settings.ping_timeout global property.
> 
> select id from Clients where last_activity < datetime('now', -ping_timeout || 
> ' seconds', 'localtime');
> -- or
> select id from Clients where
>(julianday('now', 'localtime') - julianday(last_activity)) * 86400 > 
> ping_timeout;
> 
> -- 
> Igor Tandetnik
> 
> ___
> 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] Query help

[Igor Tandetnik]

Marco Bambini  wrote:
> I have a table Clients defined as (simplified version):
> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity 
> TEXT, ping_timeout INTEGR);
> 
> ping_timeout was a global property so in order to get a list of all clients 
> timedout I used a query like (C code):
> snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
> datetime('now', '-%d.00 seconds', 'localtime');",
> settings.ping_timeout); 
> 
> things are recently changed in my app and ping_timeout is now a client 
> property set inside the Clients table (and no longer a
> global property), so I would like to perform the query using just the 
> ping_timeout column in the Clients table instead of the
> settings.ping_timeout global property.

select id from Clients where last_activity < datetime('now', -ping_timeout || ' 
seconds', 'localtime');
-- or
select id from Clients where
(julianday('now', 'localtime') - julianday(last_activity)) * 86400 > 
ping_timeout;

-- 
Igor Tandetnik

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

Re: [sqlite] Query help

[Marco Bambini]

No I am sorry but I need to query the ping_timeout column from inside the same 
query.

--
Marco Bambini
http://www.sqlabs.com








On Jun 12, 2011, at 2:02 PM, Roger Andersson wrote:

>  On 06/12/11 01:52 PM, Marco Bambini wrote:
>> things are recently changed in my app and ping_timeout is now a client 
>> property set inside the Clients table (and no longer a global property), so 
>> I would like to perform the query:
>> snprintf(sql, sizeof(sql), "select id from Clients where last_activity<  
>> datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
>> using just the ping_timeout column in the Clients table instead of the 
>> settings.ping_timeout global property.
>> 
>> Any idea?
> snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
> datetime('now', '-%d.00 seconds', 'localtime');", ping_timeout);
> 
> ___
> 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] Query help

[Roger Andersson]

  On 06/12/11 01:52 PM, Marco Bambini wrote:
> things are recently changed in my app and ping_timeout is now a client 
> property set inside the Clients table (and no longer a global property), so I 
> would like to perform the query:
> snprintf(sql, sizeof(sql), "select id from Clients where last_activity<  
> datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
> using just the ping_timeout column in the Clients table instead of the 
> settings.ping_timeout global property.
>
> Any idea?
snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
datetime('now', '-%d.00 seconds', 'localtime');", ping_timeout);

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

[sqlite] Query help

[Marco Bambini]

Hello guys,

I have a table Clients defined as (simplified version):
CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity TEXT, 
ping_timeout INTEGR);

each time a client performs an operation the last_activity column is updated 
with:
UPDATE Clients SET last_activity=datetime('now','localtime') WHERE id=N;

ping_timeout was a global property so in order to get a list of all clients 
timedout I used a query like (C code):
snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);

things are recently changed in my app and ping_timeout is now a client property 
set inside the Clients table (and no longer a global property), so I would like 
to perform the query:
snprintf(sql, sizeof(sql), "select id from Clients where last_activity < 
datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout);
using just the ping_timeout column in the Clients table instead of the 
settings.ping_timeout global property.

Any idea?
--
Marco Bambini
http://www.sqlabs.com








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

Re: [sqlite] Query help

[Philip Graham Willoughby]

On 1 Mar 2011, at 22:01, Jeff Archer wrote:

> Hi all, and thanks in advance for you help.
> 
> And this select which I would like to modify to only return lowest X,Y value
> for each ImageID.
> 
> select Defects.DefectID
> , Defects.ImageID
> , Defects.AnalysisID
> , Defects.X
> , Defects.Y
> , Defects.W
> , Defects.H
> , Defects.Area
> , Images.X + Defects.X as DefectX
> , Images.Y + Defects.Y as DefectY
> from Defects join Images on Defects.ImageID = Images.ImageID

…ORDER BY DefectX * DefectX + DefectY * DefectY ASC LIMIT 1;

More generally, the nearest to a target point (Tx,Ty) is:

…ORDER BY (DefectX-Tx)*(DefectX-Tx) + (DefectY-Ty)*(DefectY-Ty) ASC LIMIT 1;

Since more than one distinct point can be at the same distance from the target, 
these queries cannot guarantee the same result if executed twice on the same 
data.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] Query help

[Igor Tandetnik]

On 3/1/2011 6:47 PM, Jeff Archer wrote:
> I think it will just happen to work out if I could get the first row
> for each ImageID since the values should have been entered in
> ascending order.  I realize this will probably not be guaranteed to
> get lowest X,Y but for my purpose at the moment this is OK.

select * from Defects, Images
where Defects.DefectID = (
   select min(d2.DefectID) from Defects d2 where d2.ImageId = Images.ImageId
);

-- 
Igor Tandetnik

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

Re: [sqlite] Query help

[Jeff Archer]

>From: Igor Tandetnik [mailto:itandet...@mvps.org] 
>Sent: Tuesday, March 01, 2011 5:47 PM
>
>On 3/1/2011 5:01 PM, Jeff Archer wrote:
>> And this select which I would like to modify to only return lowest X,Y 
>> value for each ImageID.
>
>What does "lowest" mean? If you have two points (100, 200) and (200, 100), 
>which one is "lower"?
>--
>Igor Tandetnik
>
Sorry, I guess I wasn't very clear.  
For this purpose either would be fine.  The values should tend to be (1.5, 1.5).
I think it will just happen to work out if I could get the first row for each 
ImageID since the values should have been entered in ascending order.  I 
realize this will probably not be guaranteed to get lowest X,Y but for my 
purpose at the moment this is OK.

Thanks,
Jeff


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

Re: [sqlite] Query help

[Igor Tandetnik]

On 3/1/2011 5:01 PM, Jeff Archer wrote:
> And this select which I would like to modify to only return lowest X,Y value
> for each ImageID.

What does "lowest" mean? If you have two points (100, 200) and (200, 
100), which one is "lower"?
-- 
Igor Tandetnik

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

[sqlite] Query help

[Jeff Archer]

Hi all, and thanks in advance for you help.

 

I have the following schema

 

CREATE TABLE [Scans] 

(ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,Timestamp DATETIME NOT NULL UNIQUE

,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME

,ResultVARCHAR

);

 

CREATE TABLE [Images] 

(ImageID   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY
DEFERRED

,Filename  VARCHAR NOT NULL

,NoINTEGER NOT NULL

,X REAL NOT NULL DEFAULT 0.0

,Y REAL NOT NULL DEFAULT 0.0

,Z REAL NOT NULL DEFAULT 0.0

,R INTEGER NOT NULL DEFAULT 0

,C INTEGER NOT NULL DEFAULT 0

,wMicrons  REAL NOT NULL DEFAULT 0.0

,hMicrons  REAL NOT NULL DEFAULT 0.0

,wPixels   INTEGER NOT NULL DEFAULT 0

,hPixels   INTEGER NOT NULL DEFAULT 0

,UNIQUE (ScanID, Filename, No));

 

CREATE INDEX Images_ScanID_Index on Images(ScanID);

 

CREATE TABLE [Analyzers] 

(AnalyzerIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,Name  VARCHAR NOT NULL UNIQUE

);

 

CREATE TABLE [Analysis] 

(AnalysisIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,AnalyzerIDINTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY
DEFERRED

,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY
DEFERRED

,Timestamp DATETIME NOT NULL

,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME

,NumDefectsINTEGER DEFAULT 0

,ResultVARCHAR

);

 

CREATE INDEX Analysis_AnalyzerID_Index on Analysis(AnalyzerID);

 

CREATE INDEX Analysis_ScanID_Index on Analysis(ScanID);

 

CREATE TABLE [Defects] 

(DefectID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,ImageID   INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY
DEFERRED

,AnalysisIDINTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY
DEFERRED

,X REAL NOT NULL DEFAULT 0.0

,Y REAL NOT NULL DEFAULT 0.0

,W REAL NOT NULL DEFAULT 0.0

,H REAL NOT NULL DEFAULT 0.0

,Area  REAL NOT NULL DEFAULT 0.0

);

 

And this select which I would like to modify to only return lowest X,Y value
for each ImageID.

 

select Defects.DefectID

, Defects.ImageID

, Defects.AnalysisID

, Defects.X

, Defects.Y

, Defects.W

, Defects.H

, Defects.Area

, Images.X + Defects.X as DefectX

, Images.Y + Defects.Y as DefectY

from Defects join Images on Defects.ImageID = Images.ImageID

 

I believe that the data is all stored such that the first stored defect for
each ImageID will have the lowest X,Y values.  At least for now this
assumption is probably good enough.  

 

 

Jeff Archer

Nanotronics Imaging

jsarc...@nanotronicsimaging.com

<330>819-4615

 

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

Re: [sqlite] Query help

[Marco Bambini]

Thanks Igor and thanks Martin,
I need to add both the id and the other properties to an hash table (a Cocoa 
NSDictionary) so I needed a way to have a key, value representation that 
includes also the id.

I solved the problem with 2 queries and some Cocoa code.
I don't like complex queries and 2 simple queries is a better approach for the 
maintainability of the project.

Thanks a lot for your advices.
--
Marco Bambini
http://www.sqlabs.com






On Feb 2, 2011, at 8:11 PM, Igor Tandetnik wrote:

> On 2/2/2011 11:16 AM, Marco Bambini wrote:
>> your query returns 3 columns, but I need just two columns (key, value for 
>> example).
> 
> Why? You have all the information you need, just in a slightly different 
> (and, arguably, easier to use) form.
> 
>> The first row should be the label 'ID' and the id of the MKObjects followed 
>> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.
> 
> Why should it? Why exactly do you insist on this format?
> 
> What should happen, in your proposed representation, when there is more 
> than row in MKObjects, each with its own set of properties?
> -- 
> Igor Tandetnik
> 
> ___
> 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] Query help

[Igor Tandetnik]

On 2/2/2011 11:16 AM, Marco Bambini wrote:
> your query returns 3 columns, but I need just two columns (key, value for 
> example).

Why? You have all the information you need, just in a slightly different 
(and, arguably, easier to use) form.

> The first row should be the label 'ID' and the id of the MKObjects followed 
> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.

Why should it? Why exactly do you insist on this format?

What should happen, in your proposed representation, when there is more 
than row in MKObjects, each with its own set of properties?
-- 
Igor Tandetnik

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

Re: [sqlite] Query help

[Marco Bambini]

Hello Igor,
your query returns 3 columns, but I need just two columns (key, value for 
example).

The first row should be the label 'ID' and the id of the MKObjects followed by 
a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.

For example MKObjects contains (1,IPHONE,PANEL,0)
and MKProperties contains (1,1,NAME,About Box) and (2,1,WIDTH,200)

the result of the query should should be:
col1col2
'ID'1
'NAME'  'About Box'
'WIDTH', '200'
--
Marco Bambini
http://www.sqlabs.com






On Feb 2, 2011, at 1:43 PM, Igor Tandetnik wrote:

> Marco Bambini  wrote:
>> Hello, I have two tables defined as:
>> 
>> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
>> type TEXT, parent_id INTEGER DEFAULT 0);
>> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
>> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id,
>> prop_key)); 
>> 
>> I need to create a query that returns 2 columns key, value (column names are 
>> not important) where the first row is the label 'ID'
>> with value id from MKObjects and the other rows are the columns prop_key, 
>> prop_value from MKProperties where obj_id= MKObjects.id
>> satisfying a WHERE condition.  
> 
> You don't really need, or want, to create a query like that. It goes against 
> the grain of SQL. You want this:
> 
> select o.id,  prop_key, prop_value
> from MKObjects o join MKProperties p on (o.id = p.obj_id)
> where type='PANEL' AND platform='IPHONE'
> order by o.id;
> 
> When formatting your report, output a section heading whenever id column 
> changes from previous row.
> -- 
> Igor Tandetnik
> 
> ___
> 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] Query help

[Martin Engelschalk]

Hello Marco,

As far as i can see, the union is necessary. However, the second select 
in the union can be rewritten as a join:

SELECT 'ID', id
   FROM MKObjects
  WHERE type='PANEL' AND platform='IPHONE'
UNION
  SELECT prop_key, prop_value
FROM MKProperties
JOIN MKObjects on MKProperties.obj_id = MKObjects.id
   WHERE MKObjects.type='PANEL' AND MKObjects.platform='IPHONE'


I am not sure if i understood your question correctly - perhaps you want to add 
some examples.

Martin


Am 02.02.2011 10:04, schrieb Marco Bambini:
> Hello, I have two tables defined as:
>
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));
>
> I need to create a query that returns 2 columns key, value (column names are 
> not important) where the first row is the label 'ID' with value id from 
> MKObjects and the other rows are the columns prop_key, prop_value from 
> MKProperties where obj_id= MKObjects.id satisfying a WHERE condition.
>
> So far I am using a query like:
> SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION 
> SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM 
> MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id);
>
> but I am wondering if there is a better way (without using 3 select 
> statements).
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
> ___
> 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] Query help

[Igor Tandetnik]

Marco Bambini  wrote:
> Hello, I have two tables defined as:
> 
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id,
> prop_key)); 
> 
> I need to create a query that returns 2 columns key, value (column names are 
> not important) where the first row is the label 'ID'
> with value id from MKObjects and the other rows are the columns prop_key, 
> prop_value from MKProperties where obj_id= MKObjects.id
> satisfying a WHERE condition.  

You don't really need, or want, to create a query like that. It goes against 
the grain of SQL. You want this:

select o.id,  prop_key, prop_value
from MKObjects o join MKProperties p on (o.id = p.obj_id)
where type='PANEL' AND platform='IPHONE'
order by o.id;

When formatting your report, output a section heading whenever id column 
changes from previous row.
-- 
Igor Tandetnik

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

[sqlite] Query help

[Marco Bambini]

Hello, I have two tables defined as:

CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
type TEXT, parent_id INTEGER DEFAULT 0);
CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));

I need to create a query that returns 2 columns key, value (column names are 
not important) where the first row is the label 'ID' with value id from 
MKObjects and the other rows are the columns prop_key, prop_value from 
MKProperties where obj_id= MKObjects.id satisfying a WHERE condition.

So far I am using a query like:
SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION 
SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM 
MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id);

but I am wondering if there is a better way (without using 3 select statements).
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com






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

Re: [sqlite] Query help - two similar queries

[Black, Michael (IS)]

This also works...a little mod to Igor's...
 
You need to ensure that the players are always listed in the same 1,2 order 
though.  Otherwise the group by won't work.
 
 
.mode column
.width 8
 
create table Games(id,player1,player2,score);
insert into Games values (1,1,2,1);
insert into Games values (2,1,2,-1);
insert into Games values (3,1,3,1);
insert into Games values (4,1,3,1);
insert into Games values (5,2,3,-1);
insert into Games values (6,2,3,-1);
select 
player1,player2,count(*) TotalGames,
sum(score > 0) GamesWonByPlayer1,
sum(score < 0) GamesWonByPlayer2,
sum(score = 0) Draws
from Games group by player1,player2;
 
player1   player2 TotalGames  GamesWonByPlayer1  GamesWonByPlayer2  Draws
  --  --  -  -  
--
1 2   2   1  1  0
1 3   2   2  0  0
2 3   2   0  2  0
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Ian Hardingham
Sent: Tue 11/16/2010 7:31 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Query help - two similar queries



Many thanks again Igor.


On 16/11/2010 13:15, Igor Tandetnik wrote:
> Ian Hardingham<i...@omroth.com>  wrote:
>> Thanks Igor.  Can i get custom results like
>>
>> GamesWonByPlayer1
>>
>> By using getColumn in the normal way?
> I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a 
> column alias which a) is completely optional, you could safely drop it from 
> the query (I've just included it for clarity), and b) does not in any way 
> interfere with sqlite3_column_* API (on which, I guess, getColumn is based in 
> whatever language binding you are using).
>
>> That may be a stupid question - I guess what I mean is, are those custom
>> identifiers treated as column names when reading back from the select?
> Basically, yes. With SQLite, you could address a column by name or by 
> position (numbered left to right starting from 0). Column alias in the query 
> makes it more convenient to do the former.

___
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] Query help - two similar queries

[Ian Hardingham]

Many thanks again Igor.


On 16/11/2010 13:15, Igor Tandetnik wrote:
> Ian Hardingham  wrote:
>> Thanks Igor.  Can i get custom results like
>>
>> GamesWonByPlayer1
>>
>> By using getColumn in the normal way?
> I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a 
> column alias which a) is completely optional, you could safely drop it from 
> the query (I've just included it for clarity), and b) does not in any way 
> interfere with sqlite3_column_* API (on which, I guess, getColumn is based in 
> whatever language binding you are using).
>
>> That may be a stupid question - I guess what I mean is, are those custom
>> identifiers treated as column names when reading back from the select?
> Basically, yes. With SQLite, you could address a column by name or by 
> position (numbered left to right starting from 0). Column alias in the query 
> makes it more convenient to do the former.

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

Re: [sqlite] Query help - two similar queries

[Igor Tandetnik]

Ian Hardingham  wrote:
> Thanks Igor.  Can i get custom results like
> 
> GamesWonByPlayer1
> 
> By using getColumn in the normal way?

I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a 
column alias which a) is completely optional, you could safely drop it from the 
query (I've just included it for clarity), and b) does not in any way interfere 
with sqlite3_column_* API (on which, I guess, getColumn is based in whatever 
language binding you are using).

> That may be a stupid question - I guess what I mean is, are those custom
> identifiers treated as column names when reading back from the select?

Basically, yes. With SQLite, you could address a column by name or by position 
(numbered left to right starting from 0). Column alias in the query makes it 
more convenient to do the former.
-- 
Igor Tandetnik

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

Re: [sqlite] Query help - two similar queries

[Ian Hardingham]

Thanks Igor.  Can i get custom results like

GamesWonByPlayer1

By using getColumn in the normal way?

That may be a stupid question - I guess what I mean is, are those custom 
identifiers treated as column names when reading back from the select?

Thanks,
Ian

On 16/11/2010 13:04, Igor Tandetnik wrote:
> Ian Hardingham  wrote:
>> I have a badly designed structure for a table which records /games
>> played/ by people.  It looks like:
>>
>> id
>> player1
>> player2
>> score
>>
>> If score>  0, player 1 won the game.  If score<  0, player 2 won it.
>> (Score of 0 is a draw).
>>
>> I wish to find the total record in games between two specific players.
>> It seems to me that I would like to do the following:
>>
>> Select all games between the two players
>> take a count of this
>> Select from within the first select all games won by a certain player
> I'm not quite sure what you are trying to achieve, but see if this helps, at 
> least as a starting point:
>
> select
>  count(*) TotalGames,
>  sum(score>  0) GamesWonByPlayer1,
>  sum(score<  0) GamesWonByPlayer2,
>  sum(score = 0) Draws
> from Games where player1 = ? and player2 = ?;
>

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

Re: [sqlite] Query help - two similar queries

[Igor Tandetnik]

Ian Hardingham  wrote:
> I have a badly designed structure for a table which records /games
> played/ by people.  It looks like:
> 
> id
> player1
> player2
> score
> 
> If score > 0, player 1 won the game.  If score < 0, player 2 won it.
> (Score of 0 is a draw).
> 
> I wish to find the total record in games between two specific players.
> It seems to me that I would like to do the following:
> 
> Select all games between the two players
> take a count of this
> Select from within the first select all games won by a certain player

I'm not quite sure what you are trying to achieve, but see if this helps, at 
least as a starting point:

select
count(*) TotalGames,
sum(score > 0) GamesWonByPlayer1,
sum(score < 0) GamesWonByPlayer2,
sum(score = 0) Draws
from Games where player1 = ? and player2 = ?;

-- 
Igor Tandetnik

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

[sqlite] Query help - two similar queries

[Ian Hardingham]

Hey guys.

I have a badly designed structure for a table which records /games 
played/ by people.  It looks like:

id
player1
player2
score

If score > 0, player 1 won the game.  If score < 0, player 2 won it.  
(Score of 0 is a draw).

I wish to find the total record in games between two specific players.  
It seems to me that I would like to do the following:

Select all games between the two players
take a count of this
Select from within the first select all games won by a certain player

Is this kind of sub-selection possible?

Should I really consider making another table which just records player 
wins and losses?

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

Re: [sqlite] query help

[Paul Sanderson]

Thank You.

On 26 October 2010 13:14, Igor Tandetnik  wrote:
> Paul Sanderson  wrote:
>> I have two tables, table b is a subset of table a. both tables have
>> the same primary key
>>
>> I want to update the rows from table a with a single column from table
>> b, what sql command would be most efficient for this?
>
> update a set ColumnToUpdate = coalesce(
>    (select ColumnToUpdate from b where b.KeyColumn = a.KeyColumn),
>    ColumnToUpdate);
>
> -- or
>
> insert or replace into a(ColumnToUpdate, AllOtherColumns)
> select b1.ColumnToUpdate, a1.AllOtherColumns
> from b1 join a1 on b1.KeyColumn = a1.KeyColumn;
>
> The second statement could be faster if b is much smaller than a, but is more 
> verbose and has to be updated whenever schema changes. Time both on real 
> data, see which one works better for you.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Sanderson
Sanderson Forensics
+44 (0)1325 572786
www.sandersonforensics.com
http://www.twitter.com/sandersonforens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

[Igor Tandetnik]

Paul Sanderson  wrote:
> I have two tables, table b is a subset of table a. both tables have
> the same primary key
> 
> I want to update the rows from table a with a single column from table
> b, what sql command would be most efficient for this?

update a set ColumnToUpdate = coalesce(
(select ColumnToUpdate from b where b.KeyColumn = a.KeyColumn),
ColumnToUpdate);

-- or

insert or replace into a(ColumnToUpdate, AllOtherColumns)
select b1.ColumnToUpdate, a1.AllOtherColumns
from b1 join a1 on b1.KeyColumn = a1.KeyColumn;

The second statement could be faster if b is much smaller than a, but is more 
verbose and has to be updated whenever schema changes. Time both on real data, 
see which one works better for you.
-- 
Igor Tandetnik

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

[sqlite] query help

[Paul Sanderson]

I have two tables, table b is a subset of table a. both tables have
the same primary key

I want to update the rows from table a with a single column from table
b, what sql command would be most efficient for this?

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

Re: [sqlite] Query help

[Simon Davies]

On 19 October 2010 16:26, jeff archer  wrote:
> I have a table containing width and height of images with columns wPixels,
> hPixels.  I would like to select all rows that have either a unique wPixels 
> or a
> unique hPixels value.
>
> for this data:
> 10, 20
> 10, 20
> 10, 30
> 10, 3015, 10
> 15, 30
> 15, 30
> 15, 30
>
> I would like to select:
> 10, 20
> 10, 30
> 15, 10
> 15, 30

select distinct wPixels, hPixels from table;

>
>  Jeff Archer

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

[sqlite] Query help

[jeff archer]

I have a table containing width and height of images with columns wPixels, 
hPixels.  I would like to select all rows that have either a unique wPixels or 
a 
unique hPixels value.

for this data:
10, 20
10, 20
10, 30
10, 3015, 10
15, 30
15, 30
15, 30

I would like to select:
10, 20
10, 30
15, 10
15, 30

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Spread the word...that's what these lists are for...
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Tue 10/19/2010 8:03 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




Hi,

Well i have to say i am like a kid in a sweet shop right now, you all may
have just saved me 6 or so hours work. Thanks again for your input.

I was wondering if anyone had any issues with me posting this up on the
Boxee forum as there lots of people with this issue? I will of course tell
people that it came from here.

Thanks again,

Rich
--
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi,

Well i have to say i am like a kid in a sweet shop right now, you all may
have just saved me 6 or so hours work. Thanks again for your input.

I was wondering if anyone had any issues with me posting this up on the
Boxee forum as there lots of people with this issue? I will of course tell
people that it came from here.

Thanks again,

Rich
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Igor Tandetnik]

Dickie.wild  wrote:
> I thought that looked like it would get the same results, but i seem to be
> getting the following error, are you able to try it and let me know if your
> getting the same error?
> 
> SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
> (rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')

Make it

UPDATE video_files SET strCover = rtrim(strPath,replace(strPath,'\','')) || 
'folder.jpg';

-- 
Igor Tandetnik

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

Re: [sqlite] EXTERNAL:Re: EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Sorry...I didn't test before I submitted...
 
sqlite> create table video_files(strPath varchar,strCover varchar);
sqlite> insert into video_files values('c:\dir1\dir2\file.txt','');
sqlite> update video_files set strCover=(rtrim(strPath,replace(strPath,'\','')) 
|| 'folder.jpg');
sqlite> select * from video_files;
c:\dir1\dir2\file.txt|c:\dir1\dir2\folder.jpg
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Tue 10/19/2010 7:35 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




Hi,

I thought that looked like it would get the same results, but i seem to be
getting the following error, are you able to try it and let me know if your
getting the same error?

SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
(rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')  [ near "TO":
syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
--
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2755.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi,

I thought that looked like it would get the same results, but i seem to be
getting the following error, are you able to try it and let me know if your
getting the same error?

SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
(rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg')  [ near "TO":
syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2755.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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Actually marbex came up with the best solutionwould work with ANY allowable 
path characters.
 
UPDATE video_files SET strCover TO (rtrim(strPath,replace(strPath,'\','')) || 
'folder.jpg')

 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Tue 10/19/2010 7:17 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls




On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:

> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
>
> Update video_files
>
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'

We told you how to do it in SQLite.  The answer involved using rtrim().  Two of 
us even posted example code for you.

If you want an equivalent to that specific command then, copying from my post 
of a few days ago, it would be something like

UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._') 
|| 'folder.jpg')

(expanded to include alphabets and digits).  If you have tried that and it 
didn't work please tell us what it did instead of working.

Simon.
___
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] EXTERNAL: SQLite query help pls

[Simon Slavin]

On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:

> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also attached the DB just encase anyone can do it for me?
> 
> Update video_files
> 
> Set strCover =
> Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
> 'Folder.jpg'

We told you how to do it in SQLite.  The answer involved using rtrim().  Two of 
us even posted example code for you.

If you want an equivalent to that specific command then, copying from my post 
of a few days ago, it would be something like

UPDATE video_files SET strCover TO (rtrim(strPath, 'abcd... ABCD... 123... ._') 
|| 'folder.jpg')

(expanded to include alphabets and digits).  If you have tried that and it 
didn't work please tell us what it did instead of working.

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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Dickie.wild]

Hi, 

Thanks for the reply's i have tried the various ways described that they do
not seem to be working. I have a way in which it works but this is in SQL
could anyone convert it to SQLite for me? i am not to sure it is even
possible. I have also attached the DB just encase anyone can do it for me?

Update video_files

Set strCover =
Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
'Folder.jpg'

http://old.nabble.com/file/p2497/Boxee_catalog.db Boxee_catalog.db 
-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2497.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

Re: [sqlite] EXTERNAL: SQLite query help pls

[marbex]

Black, Michael (IS) wrote:
> 
> Since there aren't a whole lot of string manipulaion functions (like
> indexof or such) try this:
>  
> sqlite> create table t(s varchar);
> sqlite> create table t2(s varchar);
> sqlite> insert into t values('C:\richEminem\file.txt');
> sqlite> select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> C:\richEminem\folder.jpg
> sqlite> insert into t2(s)  select rtrim(s,'._
> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from
> t;
> sqlite> select * from t2;
> C:\richEminem\folder.jpg
>  
> You do, of course, need to put all allowable characters in the rtrim
> character set (except the backslash or forward slash).
>  
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>  
> 
I came up with this SQL some time ago when I needed to get the folder from a
full path, the beauty with it is that it always works regardless of which
characters you have in the filename. I though I'd share it.

select RTRIM(path,REPLACE(path,'\','')) from 
(select 'C:\richEminem\file.txt' path)

-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.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

Re: [sqlite] EXTERNAL: SQLite query help pls

[Black, Michael (IS)]

Since there aren't a whole lot of string manipulaion functions (like indexof or 
such) try this:
 
sqlite> create table t(s varchar);
sqlite> create table t2(s varchar);
sqlite> insert into t values('C:\richEminem\file.txt');
sqlite> select rtrim(s,'._ 
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
C:\richEminem\folder.jpg
sqlite> insert into t2(s)  select rtrim(s,'._ 
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')||'folder.jpg' from t;
sqlite> select * from t2;
C:\richEminem\folder.jpg
 
You do, of course, need to put all allowable characters in the rtrim character 
set (except the backslash or forward slash).
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild
Sent: Sun 10/17/2010 7:26 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite query help pls




Hi All,

I was hoping for a little help, well little would be an understatement, I
currently have a file location in a field and i would like to take all of it
up untill the last / (folder) and copy it in to another column and then
attach folder.jpg on the end. So the end result ends up being something like
c:\rich\Eminem\folder.jpg

I have never used SQLite before and help would be great

R

--
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite query help pls

[Simon Slavin]

On 17 Oct 2010, at 1:26pm, Dickie.wild wrote:

> I was hoping for a little help, well little would be an understatement, I
> currently have a file location in a field and i would like to take all of it
> up untill the last / (folder) and copy it in to another column and then
> attach folder.jpg on the end. So the end result ends up being something like
> c:\rich\Eminem\folder.jpg

SQLite has a whacky 'rtrim()' function which can trim things other than 
whitespace. So work out which characters you want to strip from after the last 
'/', presumably something like

'abcd... ABCD... 123... ._'

and do something like

UPDATE locations SET jpegPath TO (rtrim(filePath, 'abcd... ABCD... 123... ._') 
|| 'folder.jpg')

I haven't tried it but it might work.

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

Re: [sqlite] SQLite query help pls

[Germán Herrera]

You want to strip a complete path + name and save them as separate
fields, or you already have it splitted and want to join them together ?

On 10/17/2010 09:26 AM, Dickie.wild wrote:
> 
> Hi All,
> 
> I was hoping for a little help, well little would be an understatement, I
> currently have a file location in a field and i would like to take all of it
> up untill the last / (folder) and copy it in to another column and then
> attach folder.jpg on the end. So the end result ends up being something like
> c:\rich\Eminem\folder.jpg
> 
> I have never used SQLite before and help would be great
> 
> R
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite query help pls

[Dickie.wild]

Hi All,

I was hoping for a little help, well little would be an understatement, I
currently have a file location in a field and i would like to take all of it
up untill the last / (folder) and copy it in to another column and then
attach folder.jpg on the end. So the end result ends up being something like
c:\rich\Eminem\folder.jpg

I have never used SQLite before and help would be great

R

-- 
View this message in context: 
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.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

Re: [sqlite] Query help

[Igor Tandetnik]

"Marco Bambini"  wrote
in message news:3265458b-af7b-434f-83e8-f9448bab0...@sqlabs.net
> Hello all,
>
> I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
> and I have some data into foo:
> id id2 optype
> -
> 1 2 10
> 2 2 10
> 3 2 10
> 4 2 10
> 5 2 10
> 6 2 20
> 7 2 10
> 8 2 20
> 9 2 20
> 10 2 10
>
> I need a query that returns results like:
> 1,2,3,4,5
> 6
> 7
> 8,9
> 10
>
> (divided by optype and sorted by id)

Try something like this:

SELECT group_concat(id) FROM rsql_mvcc t1 WHERE transactionID=2
GROUP BY (
select min(id) from rsql_mvcc t2
where t2.transactionID=2 and t2.id <= t1.id and t2.optype=t1.optype 
and
not exists (
select 1 from rsql_mvcc t3 where t3.transactionID=2 and
t3.id > t2.id and t3.id < t1.id and t3.optype != t1.optype
)
);

However, this is likely to be excrutiatingly slow ( O(N^3) ) for 
anything but small number of records. The problem doesn't lend itself 
easily to SQL. I submit it would likely be easier, and much faster, to 
run a query like this:

select id, optype from rsql_mvcc WHERE transactionID=2
order by id;

and assemble groups in your application code as you walk the resultset.

Igor Tandetnik 



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

[sqlite] Query help

[Marco Bambini]

Hello all,

I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
and I have some data into foo:
id  id2 optype
-
1   2   10
2   2   10
3   2   10
4   2   10
5   2   10
6   2   20
7   2   10
8   2   20
9   2   20
10  2   10

I need a query that returns results like:
1,2,3,4,5
6
7
8,9
10

(divided by optype and sorted by id)

If I use a simple:
SELECT group_concat(id) FROM rsql_mvcc WHERE transactionID=2 GROUP BY  
OPTYPE;

I obtain:
1,2,3,4,5,7,10
6,8,9

I would really appreciate any help.
Thanks a lot.
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





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

Re: [sqlite] Query help?

[Stephen Oberholtzer]

What if you took a slightly different tack?

CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate
null=unknown
);


And then periodically run this:

update FinishedWork
set isDuplicate = case when exists(select 1 from FinishedWork fw2 where
fw2.ProcessId=FinishedWork.ProcessId and fw2.FileName=FinishedWork.Filename
and fw2.rowid < FinsishedWork.rowid) then 1 else 0 end where isDuplicate is
null;

Then your report would be this:

SELECT ProcessID, sum(BytesProcessed)

FROM FinishedWork

WHERE EventTime > {20 minutes ago}
AND isDuplicate=0;



By the way, what's magic about 20 minutes ago?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query help?

[Igor Tandetnik]

"Doug" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Thank you Igor.  The GROUP BY was the secret (I was trying to do a
> GROUP BY on the outer select, but it wasn't quite cutting it).
>
> GROUP BY is very powerful, but I notice it has a performance cost.
> Is there a way to use an index with it?  I have EventTime indexed and
> that index is being used.  I suppose creating a combined index of
> EventTime, ProcessID and FileName might help because the underlying
> record wouldn't need to be looked up.  Any thoughts on that idea?

No, I don't see how such an index would help. Since EventTime is the 
first component, this index can't be used to enumerate rows in the order 
defined by (ProcessID, FileName) pair. Imagine two records with the same 
ProcessID and FileName but with EventTimes that are far apart. Such 
records won't be adjacent in your index.

You could create an index on (ProcessID, FileName). But SQLite can only 
use one index in a single query. You should test and measure which of 
the two indexes results in better performance.

Igor Tandetnik 



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

Re: [sqlite] Query help?

[Doug]

Thank you Igor.  The GROUP BY was the secret (I was trying to do a GROUP BY
on the outer select, but it wasn't quite cutting it).

GROUP BY is very powerful, but I notice it has a performance cost.  Is there
a way to use an index with it?  I have EventTime indexed and that index is
being used.  I suppose creating a combined index of EventTime, ProcessID and
FileName might help because the underlying record wouldn't need to be looked
up.  Any thoughts on that idea?

Thanks
Doug


> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
> Behalf Of Igor Tandetnik
> Sent: Tuesday, May 27, 2008 12:24 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query help?
> 
> Doug <[EMAIL PROTECTED]> wrote:
> > SELECT ProcessID, count(1), sum(BytesProcessed)
> > FROM FinishedWork
> > WHERE EventTime > {20 minutes ago}
> > GROUP BY ProcessID, FileName
> >
> > Unfortunately when a file is processed twice, it's counted twice (ie
> > added into the sum twice) and I need to show only unique work, so I
> > need to count each processID-FileName pair only once for the given
> > timeframe.
> 
> Try this:
> 
> SELECT ProcessID, 1, BytesProcessed
> FROM FinishedWork
> WHERE RowId IN (
> select RowId from FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
> );
> 
> Igor Tandetnik
> 
> 
> 
> ___
> 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] Query help?

[Igor Tandetnik]

Doug <[EMAIL PROTECTED]> wrote:
> SELECT ProcessID, count(1), sum(BytesProcessed)
> FROM FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
>
> Unfortunately when a file is processed twice, it's counted twice (ie
> added into the sum twice) and I need to show only unique work, so I
> need to count each processID-FileName pair only once for the given
> timeframe.

Try this:

SELECT ProcessID, 1, BytesProcessed
FROM FinishedWork
WHERE RowId IN (
select RowId from FinishedWork
WHERE EventTime > {20 minutes ago}
GROUP BY ProcessID, FileName
);

Igor Tandetnik 



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

[sqlite] Query Help

[Aviad Harell]

Hi,

I Have a problem with the following query executed on sqlite

SELECT   CUSTOMER,
 PRODUCT,
 [RANK]
FROM (SELECT CUSTOMER,
 PRODUCT,
 [SUM_SALES],
 (SELECT COUNT(T2.SUM_SALES)
  FROM   (SELECT   CUSTOMER,
   PRODUCT,
   (SUM(SALES)) AS [SUM_SALES]
  FROM Test1MX1000Multi AS T1
  GROUP BY CUSTOMER,
   PRODUCT) AS [T2]
  WHERE  T1.CUSTOMER = T2.CUSTOMER
 AND T2.SUM_SALES > T1.SUM_SALES) AS [RANK]
  FROM   (SELECT   CUSTOMER,
   PRODUCT,
   (SUM(SALES)) AS [SUM_SALES]
  FROM Test1MX1000Multi AS T1
  GROUP BY CUSTOMER,
   PRODUCT) AS [T1]) AS [TEMP]
WHERERANK <= 4
ORDER BY CUSTOMER,
 RANK

 i get:

misuse of aggregate: (SUM(SALES)). this query is cool under sql server

any ideas?



tnx