Today I came at work with cleared mind (after a long time I would say!) and within minutes have found an easy and rather silly solution to my initial request.
Here's the code: CREATE TABLE tmp_name( id integer primary key, name text); CREATE TABLE [temp_01] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [name_id] INTEGER, [val] REAL(0, 2)); CREATE TABLE [temp_02] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [name_id] INTEGER, [val] REAL(0, 2)); CREATE VIEW [MergeTwoTableValuesAsOne] AS select tmp_name.[name], temp_01.val as [First Value], temp_02.val as [Second Value], (temp_01.val + temp_02.val) as [Addition], (temp_01.val - temp_02.val) as [Subtraction], (temp_01.val * temp_02.val) as [Multiplication], (temp_01.val / temp_02.val) as [Division], (temp_01.val % temp_02.val) as [Remainder] from temp_01, temp_02, tmp_name where temp_01.id = temp_02.id and temp_01.[name_id] = temp_02.[name_id] and temp_01.name_id = tmp_name.id and temp_02.name_id = tmp_name.id; This is way much better than passing my data to a table. Like this, I generate my information on the fly and if I want them in a table I can create a new table and insert them in: create table <table-name> as select * from MergeTwoTableValuesAsOne; Simple as that :D Just wanted to share it with you guys :) Waiting for your valuable replies. >________________________________ >From: "Black, Michael (IS)" <michael.bla...@ngc.com> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Tuesday, August 30, 2011 6:48 PM >Subject: Re: [sqlite] Clarification about Triggers > >I found that if you used the default rowid it always gave -1 for the value. >That's why I put in it's own key. > >I don't understand why this doesn't work...perhaps somebody can point out the >error here...new.rowid contains -1. I would think that rowid ought to be >available after the insert. >This is using 3.7.4 > >create table temp_01(val float); >create table temp_02(val float); >create table total(val float); >create table row(row2 integer); >create trigger after insert on temp_01 >begin >insert into total values((select new.val+temp_02.val from temp_02 where >temp_02.rowid=new.rowid)); >insert into row values(new.rowid); >end; >insert into temp_02 values(2.0); >insert into temp_01 values(1.0); >select * from total; > >select * from row; >-1 > > > >Michael D. Black >Senior Scientist >NG Information Systems >Advanced Analytics Directorate > > > > >From: stefanos sofroniou [stefanossofroniou...@yahoo.com] >Sent: Tuesday, August 30, 2011 9:42 AM >To: Black, Michael (IS) >Subject: EXT :Re: [sqlite] Clarification about Triggers > > >I did exactly what you have suggested but it does not work properly with my >software. > > > >It increments my id (i replaced rownum with id, the function still is the >same), but the value is null for some reason. > > > > >From: "Black, Michael (IS)" <michael.bla...@ngc.com> >To: stefanos sofroniou <stefanossofroniou...@yahoo.com>; General Discussion of >SQLite Database <sqlite-users@sqlite.org> >Sent: Tuesday, August 30, 2011 3:19 PM >Subject: RE:[sqlite] Clarification about Triggers > > >There may be a more elegant way but this works: > >create table temp_01(rownum integer primary key,val float); >create table temp_02(rownum integer primary key,val float); >create table total(rownum integer primary key,val float); >create trigger after insert on temp_01 >begin >insert into total values(new.rownum,(select new.val+temp_02.val from temp_02 >where temp_02.rownum=new.rownum)); >end; >insert into temp_02 values(1,2.0); >insert into temp_01 values(1,1.0); >select * from total; >1|3.0 >insert into temp_02 values(2,20.0); >insert into temp_01 values(2,30.0); >select * from total; >1|3.0 >2|50.0 > > > >Michael D. Black >Senior Scientist >NG Information Systems >Advanced Analytics Directorate > > > > >From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on >behalf of stefanos sofroniou [stefanossofroniou...@yahoo.com] >Sent: Tuesday, August 30, 2011 2:32 AM >To: sqlite-users@sqlite.org >Subject: EXT :[sqlite] Clarification about Triggers > > >Hello everyone. > >I have successfully created a trigger for experimentation and I would like >your help for implementing the concept behind of it. > >My table pollaplasiasmos (which stands for multiplication in Greek, even >though I used English characters to write it [Gree-glish]), takes 3 values: > * Quantity [int] > * amount [real(0,2)] > > * VAT [real(0,2)]I have created the triggers to make the necessary >calculations for vat_value (per item), vat_included (item price), and >total_price (which includes vat). > >Now what I want to do is to take two tables, (let's call them temp_01 and >temp_02) that would both have columns id and val, and I want with a trigger to >add temp_01.val with temp_02.val and put their result in a new table named >total. > >I have tried something like this: > >create trigger [after_insert_temp_01] >after insert on temp_01 >begin > update total > set val = temp_01.val + temp_02.val > where rowid = old.rowid; >end; > >I know that this is wrong of what I am doing, because there is no way to call >the temp_02 table using this method; this is not an inner join where I can >compare IDs and make the necessary adjustments on my code. > >I am really out of ideas and if there's a link with more information that I >should know already about it, by all means let me know because I need to learn >these things. > >Regards, > >Stefanos >_______________________________________________ >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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
