Data Manipulation ?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Data Manipulation ?

Richard-118
Does SQLite support any direct built-in commands, that can
manipulate data?

ie.
suppose the boss wanted a report of cars honda in CA,
but he wants to see the California instead of CA,
so you need to convert all the state fields into full names,
how would this be done...

Fields are as follows:

recid
qty
make
model
year
engine
color
state
price


Thanks -
Richard

--

I will not be pushed, filed, stamped, indexed, briefed, debriefed, or
numbered!
My life is my own - No. 6
Reply | Threaded
Open this post in threaded view
|

Re: Data Manipulation ?

Doug Hanks
Richard,

If you need any help I'm available under contract.  I'll be in L.A.
next week on business with a client, but otherwise I will be free.

I've been programming for 10+ years and what you need done is very
simple and I could charge for a minimum number of hours and you will
have a full functional solution.

Doug

On 9/25/05, Richard Nagle <[hidden email]> wrote:

> Does SQLite support any direct built-in commands, that can
> manipulate data?
>
> ie.
> suppose the boss wanted a report of cars honda in CA,
> but he wants to see the California instead of CA,
> so you need to convert all the state fields into full names,
> how would this be done...
>
> Fields are as follows:
>
> recid
> qty
> make
> model
> year
> engine
> color
> state
> price
>
>
> Thanks -
> Richard
>
> --
>
> I will not be pushed, filed, stamped, indexed, briefed, debriefed, or
> numbered!
> My life is my own - No. 6
>


--
- Doug Hanks = dhanks(at)gmail(dot)com
Reply | Threaded
Open this post in threaded view
|

Re: Data Manipulation ?

Firman Wandayandi
In reply to this post by Richard-118
On 9/26/05, Richard Nagle <[hidden email]> wrote:
> Does SQLite support any direct built-in commands, that can
> manipulate data?
>
> ie.
> suppose the boss wanted a report of cars honda in CA,
> but he wants to see the California instead of CA,
> so you need to convert all the state fields into full names,
> how would this be done...
>

There's no built-in function for this cases, you need something like
replace(), see http://sqlite.org/lang_expr.html for the built-in
functions. You need the aggregate functions.

I use SQLite with PHP, so it's easy to create the aggregate functions.

> Fields are as follows:
>
> recid
> qty
> make
> model
> year
> engine
> color
> state
> price
>
>
> Thanks -
> Richard
>


Regards,
--
Firman Wandayandi
Never Dreamt Before (http://php.hm/~firman/)
Reply | Threaded
Open this post in threaded view
|

Re: Data Manipulation ?

Igor Tandetnik
In reply to this post by Richard-118
Richard Nagle <cms01-odAbcf0Mc9JJm/[hidden email]> wrote:
> Does SQLite support any direct built-in commands, that can
> manipulate data?
>
> ie.
> suppose the boss wanted a report of cars honda in CA,
> but he wants to see the California instead of CA,
> so you need to convert all the state fields into full names,
> how would this be done...

Add a table with two columns to the database, one with the state
abbreviation and the other with full state name. Then you can do
something like this:

select make, model, fullstate
from cars join states on (cars.state = states.state)
where make='honda'

Alternatively, register a user-defined function using
sqlite3_create_function[16] that would perform the translation, and use
the function in the query.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

RE: Data Manipulation ?

Steve O'Hara
In reply to this post by Richard-118
Here's a  way to go with pure SQL;

create table cars (recid,qty,make,model,year,engine,color,state,price);
create table states (abbr,name);

insert into states values("ca","California");
insert into cars values(1,5,"Ford","Bandit",2005,2000,"Red","ca",5000);

select make,(select name from states where abbr=state) as statename from
cars;

Steve




-----Original Message-----
From: sqlite-users-return-7941-sohara=[hidden email]
[mailto:sqlite-users-return-7941-sohara=[hidden email]
rg] On Behalf Of Richard Nagle
Sent: 26 September 2005 04:14
To: [hidden email]
Subject: [sqlite] Data Manipulation ?

Does SQLite support any direct built-in commands, that can
manipulate data?

ie.
suppose the boss wanted a report of cars honda in CA,
but he wants to see the California instead of CA,
so you need to convert all the state fields into full names,
how would this be done...

Fields are as follows:

recid
qty
make
model
year
engine
color
state
price


Thanks -
Richard

--

I will not be pushed, filed, stamped, indexed, briefed, debriefed, or
numbered!
My life is my own - No. 6


Reply | Threaded
Open this post in threaded view
|

SQL Queries

Chris Gurtler
Hi,

I'm pretty new to SQLite, and am just looking for a few pointers on SQL
queries, this is an example of a query from a crapy MS Access database that
I want to convert to SQLite, but it fails. it says c.group_id does not
exist.

I'm wondering if anyone has got some tips on multiple joins, I suspect
SQLite doesn't like these joins very much and I will need to rewrite some of
the queries.

Regards,

Chris.

Query  is :-

Select *
  from
   (
     select b.group_id from users a INNER JOIN
        (
          select group_id from group_users
            Where user_id = 'pass'
        ) as b
     on (a.user_id = b.user_id)
    Where a.password = 'pass'
  ) c
  inner join groups as d on
  (c.group_id = d.group_id)




Reply | Threaded
Open this post in threaded view
|

RE: Data Manipulation ?

nedbatchelder
In reply to this post by Firman Wandayandi
There is a built-in functionality you could use: case.  For example, here's
a query against the state column that shows the results with nice names.
You'd have to add the other state names to the query, but you get the idea.

select recid, qty, make, model,
        case state
        when 'ca' then 'California'
        when 'ny' then 'New York'
        when 'ma' then 'Massachusetts'
        else state end as fullstate
from mytableofcars;


--Ned.
http://nedbatchelder.com
 

-----Original Message-----
From: Firman Wandayandi [mailto:[hidden email]]
Sent: Sunday, 25 September, 2005 11:43 PM
To: [hidden email]; [hidden email]
Subject: Re: [sqlite] Data Manipulation ?

On 9/26/05, Richard Nagle <[hidden email]> wrote:
> Does SQLite support any direct built-in commands, that can
> manipulate data?
>
> ie.
> suppose the boss wanted a report of cars honda in CA,
> but he wants to see the California instead of CA,
> so you need to convert all the state fields into full names,
> how would this be done...
>

There's no built-in function for this cases, you need something like
replace(), see http://sqlite.org/lang_expr.html for the built-in
functions. You need the aggregate functions.

I use SQLite with PHP, so it's easy to create the aggregate functions.

> Fields are as follows:
>
> recid
> qty
> make
> model
> year
> engine
> color
> state
> price
>
>
> Thanks -
> Richard
>


Regards,
--
Firman Wandayandi
Never Dreamt Before (http://php.hm/~firman/)

Reply | Threaded
Open this post in threaded view
|

Re: SQL Queries

Eric Bohlman
In reply to this post by Chris Gurtler
Chris Gurtler wrote:
> I'm pretty new to SQLite, and am just looking for a few pointers on SQL
> queries, this is an example of a query from a crapy MS Access database
> that I want to convert to SQLite, but it fails. it says c.group_id does
> not exist.
>
> I'm wondering if anyone has got some tips on multiple joins, I suspect
> SQLite doesn't like these joins very much and I will need to rewrite
> some of the queries.

It's not a problem with joins _per se_, it involves the propagation of
column names from subqueries.  It looks like this is the same problem
reported in ticket 1111
(http://www.sqlite.org/cvstrac/tktview?tn=1111,33) and the workaround
suggested there might help.
Reply | Threaded
Open this post in threaded view
|

Re: Data Manipulation ?

John Stanton-3
In reply to this post by nedbatchelder
A neat way to do what you want is to set up a table cross-referencing
State mnemonics like CA with California, and then use a lookup as
suggested by an earlier correspondent.

Finally raise a VIEW containing the complex SELECT statement so that you
can do your query with the simplest SQL.  If you are constantly
referring to California, make a VIEW just for that query.

JS

> -----Original Message-----
> From: Firman Wandayandi [mailto:[hidden email]]
> Sent: Sunday, 25 September, 2005 11:43 PM
> To: [hidden email]; [hidden email]
> Subject: Re: [sqlite] Data Manipulation ?
>
> On 9/26/05, Richard Nagle <[hidden email]> wrote:
>
>>Does SQLite support any direct built-in commands, that can
>>manipulate data?
>>
>>ie.
>>suppose the boss wanted a report of cars honda in CA,
>>but he wants to see the California instead of CA,
>>so you need to convert all the state fields into full names,
>>how would this be done...
>>
>
>
> There's no built-in function for this cases, you need something like
> replace(), see http://sqlite.org/lang_expr.html for the built-in
> functions. You need the aggregate functions.
>
> I use SQLite with PHP, so it's easy to create the aggregate functions.
>
>
>>Fields are as follows:
>>
>>recid
>>qty
>>make
>>model
>>year
>>engine
>>color
>>state
>>price
>>
>>
>>Thanks -
>>Richard
>>
>
>
>
> Regards,
> --
> Firman Wandayandi
> Never Dreamt Before (http://php.hm/~firman/)
>

Reply | Threaded
Open this post in threaded view
|

Re: SQL Queries

Chris Gurtler
In reply to this post by Eric Bohlman
Thanks Eric,

That's exactly what the issue was, and it all works like a treat now.

After about 4 hours I have managed to remove my Access Database and now have
a far better solution, I'm impressed!

Regards,

Chris





----- Original Message -----
From: "Eric Bohlman" <[hidden email]>
To: <[hidden email]>
Sent: Tuesday, September 27, 2005 4:37 AM
Subject: Re: [sqlite] SQL Queries


> Chris Gurtler wrote:
>> I'm pretty new to SQLite, and am just looking for a few pointers on SQL
>> queries, this is an example of a query from a crapy MS Access database
>> that I want to convert to SQLite, but it fails. it says c.group_id does
>> not exist.
>>
>> I'm wondering if anyone has got some tips on multiple joins, I suspect
>> SQLite doesn't like these joins very much and I will need to rewrite some
>> of the queries.
>
> It's not a problem with joins _per se_, it involves the propagation of
> column names from subqueries.  It looks like this is the same problem
> reported in ticket 1111 (http://www.sqlite.org/cvstrac/tktview?tn=1111,33)
> and the workaround suggested there might help.
>
>