RE: [sqlite] Limit statement size?
> I wonder what the reason was to limit the number of table joins to 32. http://www.sqlite.org/cvstrac/chngview?cn=3622 Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
I code in VB and I think I stay out of altering the C source code.
> just do a single REPLACE command with a SELECT on 2 or more subqueries on
> sub-sets of the tables (more efficient).
Will try that one.
> This stands to reason since you're only doing a single lookup per
> sub-table instead of the 6 lookups per sub-table you did with the
> UPDATE command.
Maybe, but the speed is actually less as my figure of 5 to 6 times faster
was faulty due to me not noticing the error caused by the > 32 table joins.
I would say it is about 2 to 3 times faster. Still worth it, plus a nicer
looking SQL.
I wonder what the reason was to limit the number of table joins to 32.
RBS
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: 01 February 2007 00:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.
Let's do some grepping...
#define BMS (sizeof(Bitmask)*8)
...
/* The number of tables in the FROM clause is limited by the number of
** bits in a Bitmask
*/
if( pTabList->nSrc>BMS ){
sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
return 0;
}
...
You could try changing src/sqliteInt.h:
-typedef unsigned int Bitmask;
+typedef u64 Bitmask;
and then recompiling sqlite. If all goes well, you should be able to
join up to 64 tables. Never tried it. It might work, or might not.
Alternatively, you can either perform 2 consecutive REPLACE commands
with half the tables in each update (less efficient), or just do a single
REPLACE command with a SELECT on 2 or more subqueries on sub-sets of
the tables (more efficient).
> Actually make that about 5 to 6 times as fast.
This stands to reason since you're only doing a single lookup per
sub-table instead of the 6 lookups per sub-table you did with the
UPDATE command.
Never Miss an Email
Stay connected with Yahoo! Mail on your mobile. Get started!
http://mobile.yahoo.com/services?promote=mail
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> There is one important problem though that I just discovered.
> Just found out that the maximum number of tables in a join is 32!
> So, with my base table that is only 31 to add.
Let's do some grepping...
#define BMS (sizeof(Bitmask)*8)
...
/* The number of tables in the FROM clause is limited by the number of
** bits in a Bitmask
*/
if( pTabList->nSrc>BMS ){
sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
return 0;
}
...
You could try changing src/sqliteInt.h:
-typedef unsigned int Bitmask;
+typedef u64 Bitmask;
and then recompiling sqlite. If all goes well, you should be able to
join up to 64 tables. Never tried it. It might work, or might not.
Alternatively, you can either perform 2 consecutive REPLACE commands
with half the tables in each update (less efficient), or just do a single
REPLACE command with a SELECT on 2 or more subqueries on sub-sets of
the tables (more efficient).
> Actually make that about 5 to 6 times as fast.
This stands to reason since you're only doing a single lookup per
sub-table instead of the 6 lookups per sub-table you did with the
UPDATE command.
Never Miss an Email
Stay connected with Yahoo! Mail on your mobile. Get started!
http://mobile.yahoo.com/services?promote=mail
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Limit statement size?
There is one important problem though that I just discovered. Just found out that the maximum number of tables in a join is 32! So, with my base table that is only 31 to add. This trouble doesn't of course apply to the old UPDATE method. So, I think after all I need the old way of doing it or what I could do is see how many tables are to be added and pick the method accordingly. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:54 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400
RE: [sqlite] Limit statement size?
Actually make that about 5 to 6 times as fast. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 2
RE: [sqlite] Limit statement size?
Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_ae2_be3_ae3_b > 3 300 310 23.1230.1 > 4 400 410 24.1240.1 > 5
RE: [sqlite] Limit statement size?
> Your INSERT OR REPLACE statement is in error. Yes, you are right. In the end it all came down to a simple mistake on my side. Sorry if I have wasted anybody's time. Got this all working now and will now see if it is faster than my old method. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 03:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Your INSERT OR REPLACE statement is in error. You have fewer columns in your SELECT clause than are specified in your INSERT column name list. You should have seen an error like this in SQLite version 3.3.12: SQL error: X values for Y columns Assuming PATIENT_ID is the sole unique key for A3TestB67_J and your SQL column counts match, the REPLACE should work. --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
Your INSERT OR REPLACE statement is in error. You have fewer columns in your SELECT clause than are specified in your INSERT column name list. You should have seen an error like this in SQLite version 3.3.12: SQL error: X values for Y columns Assuming PATIENT_ID is the sole unique key for A3TestB67_J and your SQL column counts match, the REPLACE should work. --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_ae2_be3_ae3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_ae2_be3_ae3_b > 3 300 310 23.1230.1 > 4 400 410 24.1240.1 > 5 500 510 25.1250.1 > > > > > > RBS > > > > -Original Message- > > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > > Sent: 29 January 2007 23:52 > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Limit statement size? > > > > RB Smissaert wrote: > > > Had a go at this, but sofar I haven't been able yet to get it to work. > > > I get no error, but A3Test115_J remains just at it is. > > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > > documentation. What exactly should it do? Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
I can see now what the trouble is if I do the SELECT without the INSERT OR REPLACE (shortened): select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1, GROUP_2 g2, GROUP_3 g3, GROUP_4 g4 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID I only get the rows that have entries in all groups. So, this is like an inner join and I need a left join. Have tried this, but it didn't alter the table, although there was no error: INSERT OR REPLACE INTO A3TestB67_J(PATIENT_ID, ENTRY_ID_E1, READ_CODE_E1, TERM_TEXT_E1, START_DATE_E1, ADDED_DATE_E1, NUMERIC_VALUE_E1, ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, START_DATE_E2, ADDED_DATE_E2, NUMERIC_VALUE_E2, ENTRY_ID_E3, READ_CODE_E3, TERM_TEXT_E3, START_DATE_E3, ADDED_DATE_E3, NUMERIC_VALUE_E3, ENTRY_ID_E4, READ_CODE_E4, TERM_TEXT_E4, START_DATE_E4, ADDED_DATE_E4, NUMERIC_VALUE_E4) select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1 left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) My old method is actually quite fast and not sure if I can improve on it. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 30 January 2007 05:53 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, that is how I understood it to be. > I must be overlooking something simple here. Check your SELECT sub-statement within the REPLACE statement to see what rows it returns. .header on .mode tabs create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); insert into t1 values(3, 30,31, 23,230); insert into t1 values(4, 40,41, 24,240); insert into t1 values(5, 50,51, 25,250); create table e2(id primary key, a, b); insert into e2 values(3, 300, 310); insert into e2 values(4, 400, 410); insert into e2 values(5, 500, 510); create table e3(id primary key, a, b); insert into e3 values(3, 23.1, 230.1); insert into e3 values(4, 24.1, 240.1); insert into e3 values(5, 25.1, 250.1); select * from t1 order by id; replace into t1(id, e2_a, e2_b, e3_a, e3_b) select t1.id, e2.a, e2.b, e3.a, e3.b from t1, e2, e3 where t1.id = e2.id and t1.id = e3.id; select * from t1 order by id; id e2_ae2_be3_ae3_b 3 30 31 23 230 4 40 41 24 240 5 50 51 25 250 id e2_ae2_be3_ae3_b 3 300 310 23.1230.1 4 400 410 24.1240.1 5 500 510 25.1250.1 > > RBS > > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 29 January 2007 23:52 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Limit statement size? > > RB Smissaert wrote: > > Had a go at this, but sofar I haven't been able yet to get it to work. > > I get no error, but A3Test115_J remains just at it is. > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > documentation. What exactly should it do? > > It will try to do an INSERT. If the PATIENT_ID field is already in the > file, it will delete the old conflicting entry before inserting. > > The best write-up is at: > > http://sqlite.org/lang_conflict.html > > > HTH, > > Gerry No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
--- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, that is how I understood it to be. > I must be overlooking something simple here. Check your SELECT sub-statement within the REPLACE statement to see what rows it returns. .header on .mode tabs create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); insert into t1 values(3, 30,31, 23,230); insert into t1 values(4, 40,41, 24,240); insert into t1 values(5, 50,51, 25,250); create table e2(id primary key, a, b); insert into e2 values(3, 300, 310); insert into e2 values(4, 400, 410); insert into e2 values(5, 500, 510); create table e3(id primary key, a, b); insert into e3 values(3, 23.1, 230.1); insert into e3 values(4, 24.1, 240.1); insert into e3 values(5, 25.1, 250.1); select * from t1 order by id; replace into t1(id, e2_a, e2_b, e3_a, e3_b) select t1.id, e2.a, e2.b, e3.a, e3.b from t1, e2, e3 where t1.id = e2.id and t1.id = e3.id; select * from t1 order by id; id e2_ae2_be3_ae3_b 3 30 31 23 230 4 40 41 24 240 5 50 51 25 250 id e2_ae2_be3_ae3_b 3 300 310 23.1230.1 4 400 410 24.1240.1 5 500 510 25.1250.1 > > RBS > > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 29 January 2007 23:52 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Limit statement size? > > RB Smissaert wrote: > > Had a go at this, but sofar I haven't been able yet to get it to work. > > I get no error, but A3Test115_J remains just at it is. > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > documentation. What exactly should it do? > > It will try to do an INSERT. If the PATIENT_ID field is already in the > file, it will delete the old conflicting entry before inserting. > > The best write-up is at: > > http://sqlite.org/lang_conflict.html > > > HTH, > > Gerry No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
Thanks, that is how I understood it to be. I must be overlooking something simple here. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 29 January 2007 23:52 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Limit statement size? RB Smissaert wrote: > Had a go at this, but sofar I haven't been able yet to get it to work. > I get no error, but A3Test115_J remains just at it is. > I couldn't find much information about INSERT OR REPLACE in the SQLite > documentation. What exactly should it do? It will try to do an INSERT. If the PATIENT_ID field is already in the file, it will delete the old conflicting entry before inserting. The best write-up is at: http://sqlite.org/lang_conflict.html HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Limit statement size?
RB Smissaert wrote: Had a go at this, but sofar I haven't been able yet to get it to work. I get no error, but A3Test115_J remains just at it is. I couldn't find much information about INSERT OR REPLACE in the SQLite documentation. What exactly should it do? It will try to do an INSERT. If the PATIENT_ID field is already in the file, it will delete the old conflicting entry before inserting. The best write-up is at: http://sqlite.org/lang_conflict.html HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
Had a go at this, but sofar I haven't been able yet to get it to work. I get no error, but A3Test115_J remains just at it is. I couldn't find much information about INSERT OR REPLACE in the SQLite documentation. What exactly should it do? RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 20:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? I forgot to specify the unique key for the table to be updated in the first attempt at INSERT OR REPLACE. Please try this... insert or replace into A3Test115_J( PATIENT_ID, ---<--- ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, ... NUMERIC_VALUE_E15 ) select t1.PATIENT_ID, ---<--- g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, ... g15.NUMERIC_VALUE from A3Test115_J t1, GROUP_2 g2, GROUP_3 g3, ... GROUP_15 g15 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID and t1.PATIENT_ID = g5.PID ... and t1.PATIENT_ID = g15.PID --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, will have a look at that. > It definitely looks better. > > RBS Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Limit statement size?
I forgot to specify the unique key for the table to be updated in the first attempt at INSERT OR REPLACE. Please try this... insert or replace into A3Test115_J( PATIENT_ID, ---<--- ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, ... NUMERIC_VALUE_E15 ) select t1.PATIENT_ID, ---<--- g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, ... g15.NUMERIC_VALUE from A3Test115_J t1, GROUP_2 g2, GROUP_3 g3, ... GROUP_15 g15 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID and t1.PATIENT_ID = g5.PID ... and t1.PATIENT_ID = g15.PID --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, will have a look at that. > It definitely looks better. > > RBS Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Limit statement size?
--- Joe Wilson <[EMAIL PROTECTED]> wrote: > I think this technique might be more compact and efficient than the update: I think my suggestion may result in duplicate rows. If the INSERT or REPLACE doesn't work, you might try: 1. BEGIN 2. dump the joined select into a temp table 3. delete the affected rows from A3Test115_J 4. insert the rows from the temp table into A3Test115_J 5. drop the temp table. 6. COMMIT But it might not be worth the trouble. > insert or replace into A3Test115_J( > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > ... > NUMERIC_VALUE_E15 > ) > select > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > ... > g15.NUMERIC_VALUE > from > A3Test115_J t1, > GROUP_2 g2, > GROUP_3 g3, > ... > GROUP_15 g15 > where > t1.PATIENT_ID = g2.PID > and t1.PATIENT_ID = g3.PID > and t1.PATIENT_ID = g4.PID > and t1.PATIENT_ID = g5.PID > ... > and t1.PATIENT_ID = g15.PID > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T WHERE > > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE > > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE > > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE > > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE > > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T > > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM > > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 = > > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 = > > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 = > > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 = > > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID = > > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID = > > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID = > > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID = > > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID = > > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID = > > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE > > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE > > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE > > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE > > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE > > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T > > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T > > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T > > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T > > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T > > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T > > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM > > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM > > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM > > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM > > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM > > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM > > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = > > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 = > > (S
RE: [sqlite] Limit statement size?
Thanks, will have a look at that. It definitely looks better. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 20:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Limit statement size? I think this technique might be more compact and efficient than the update: insert or replace into A3Test115_J( ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, ... NUMERIC_VALUE_E15 ) select g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, ... g15.NUMERIC_VALUE from A3Test115_J t1, GROUP_2 g2, GROUP_3 g3, ... GROUP_15 g15 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID and t1.PATIENT_ID = g5.PID ... and t1.PATIENT_ID = g15.PID --- RB Smissaert <[EMAIL PROTECTED]> wrote: > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 = > (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 = > (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PI
Re: [sqlite] Limit statement size?
I think this technique might be more compact and efficient than the update: insert or replace into A3Test115_J( ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, ... NUMERIC_VALUE_E15 ) select g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, ... g15.NUMERIC_VALUE from A3Test115_J t1, GROUP_2 g2, GROUP_3 g3, ... GROUP_15 g15 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID and t1.PATIENT_ID = g5.PID ... and t1.PATIENT_ID = g15.PID --- RB Smissaert <[EMAIL PROTECTED]> wrote: > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 = > (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 = > (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10 > = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E11 = (SELECT ENTRY_ID FROM GROUP_11 T WHERE > PATIENT_ID = T.PID), READ_CODE_E11 = (SELECT READ_CODE FROM GROUP_11 T WHERE > PATIENT_ID = T.PID), TER
RE: [sqlite] Limit statement size?
> The UPDATE statement in the original example is a way to > compensate for SQLite's inability to perform a join on > another table before doing an UPDATE. Yes, I remember trying to do this at first via joins, but I couldn't get that to work and ended up with the current solution. Still, it wouldn't surprise me if there was a better way to do this. Simplified, this is what I am trying to do: Given a table like this: PID DATECODE -- 1 20030101aa 1 20051112dge 2 19980505dd 3 20010808ee 3 20031212mm I need to transpose this to get only unique PID's in column 1 and the other data grouped in the same row, like this: PID DATE_1 CODE_1 DATE_2 CODE_2 1 20030101aa 20051112dge 2 19980505dd 3 20010808ee 20031212mm Etc. The number of rows per PID is variable and that can be up to maybe 100. Also the number of different items per row as in the first table is variable and that could be up to maybe 10. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 19:14 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- Fred Williams <[EMAIL PROTECTED]> wrote: > Wow! Talk about obfuscated code! I didn't even try to dig deeper than > a quick scan, but could this abomination be broken into multiple update > queries? On the surface it looks like each "group" is unique. If so, > wouldn't a transaction with multiple update statements be much more > efficient and a much lighter load on resources on a step by step basis? > I damn well know it would be much more pleasing to the eye! :-) It's not complicated at all. The UPDATE statement in the original example is a way to compensate for SQLite's inability to perform a join on another table before doing an UPDATE. That's why the author needed all those inner selects each returning a single value. http://www.sqlite.org/lang_update.html Had the MySQL UPDATE syntax been available, the same UPDATE statement could be expressed in less than 10% of the SQL in the original SQL statement, with far fewer database accesses due to the reduced number of SQL subqueries. http://dev.mysql.com/doc/refman/5.0/en/update.html Another alternative is for the SQLite queryer to first query all those other tables first and then programmatically (via C or whatever) construct and execute the UPDATE statement. But I can understand why anyone would want to avoid this extra work just to perform a straight-forward UPDATE. > > Fred > > > -Original Message- > > From: RB Smissaert [mailto:[EMAIL PROTECTED] > > Sent: Sunday, January 28, 2007 12:07 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Limit statement size? > > > > > > Is there any limit on the size of the SQL statements in SQLite? > > Didn't think this would come into play, but have now come across this > > query and wonder if this needs considering: > > > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE > > FROM GROUP_2 T > > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT > > NUMERIC_VALUE FROM > > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT > > ENTRY_ID FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT > > READ_CODE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT > > TERM_TEXT FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT > > START_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT > > ADDED_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > &g
RE: [sqlite] Limit statement size?
--- Fred Williams <[EMAIL PROTECTED]> wrote: > Wow! Talk about obfuscated code! I didn't even try to dig deeper than > a quick scan, but could this abomination be broken into multiple update > queries? On the surface it looks like each "group" is unique. If so, > wouldn't a transaction with multiple update statements be much more > efficient and a much lighter load on resources on a step by step basis? > I damn well know it would be much more pleasing to the eye! :-) It's not complicated at all. The UPDATE statement in the original example is a way to compensate for SQLite's inability to perform a join on another table before doing an UPDATE. That's why the author needed all those inner selects each returning a single value. http://www.sqlite.org/lang_update.html Had the MySQL UPDATE syntax been available, the same UPDATE statement could be expressed in less than 10% of the SQL in the original SQL statement, with far fewer database accesses due to the reduced number of SQL subqueries. http://dev.mysql.com/doc/refman/5.0/en/update.html Another alternative is for the SQLite queryer to first query all those other tables first and then programmatically (via C or whatever) construct and execute the UPDATE statement. But I can understand why anyone would want to avoid this extra work just to perform a straight-forward UPDATE. > > Fred > > > -Original Message- > > From: RB Smissaert [mailto:[EMAIL PROTECTED] > > Sent: Sunday, January 28, 2007 12:07 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Limit statement size? > > > > > > Is there any limit on the size of the SQL statements in SQLite? > > Didn't think this would come into play, but have now come across this > > query and wonder if this needs considering: > > > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM > > GROUP_2 T WHERE > > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE > > FROM GROUP_2 T > > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE > > FROM GROUP_3 T > > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT > > NUMERIC_VALUE FROM > > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT > > ENTRY_ID FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT > > READ_CODE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT > > TERM_TEXT FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT > > START_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT > > ADDED_DATE FROM > > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > READ_CODE_E5 = > > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > TERM_TEXT_E5 = > > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > START_DATE_E5 = > > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > ADDED_DATE_E5 = > > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE > > PATIENT_ID = > > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE > > PATIENT_ID = > > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE > > PATIENT_ID = > > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE > > PATIENT_ID = > > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T > > WHERE PATIENT_ID = > > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T > > WHERE PATIENT_ID = > > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM > > GROUP_7 T WHERE > > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM > > GROUP_7 T WHERE > > P
RE: [sqlite] Limit statement size?
I don't think normalization comes into play here. These are a whole of manipulations to transpose a table. My normal base tables are all normalized fine. RBS -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 19:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Limit statement size? You have any way to normalize that original source table? It's full of extents, which is the first sign of badness in a table design and assured of complicating your life. Clay RB Smissaert wrote: > Yes, I agree it looks messy, but I used to do this in steps and after advice > I think from Igor Tandenik I lumped it all together and run it in one go, > which is a lot faster. > > RBS > > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: 28 January 2007 18:49 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > Wow! Talk about obfuscated code! I didn't even try to dig deeper than > a quick scan, but could this abomination be broken into multiple update > queries? On the surface it looks like each "group" is unique. If so, > wouldn't a transaction with multiple update statements be much more > efficient and a much lighter load on resources on a step by step basis? > I damn well know it would be much more pleasing to the eye! :-) > > Fred > >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Sunday, January 28, 2007 12:07 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Limit statement size? >> >> >> Is there any limit on the size of the SQL statements in SQLite? >> Didn't think this would come into play, but have now come across this >> query and wonder if this needs considering: >> >> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE >> FROM GROUP_2 T >> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT >> NUMERIC_VALUE FROM >> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT >> ENTRY_ID FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT >> READ_CODE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT >> TERM_TEXT FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT >> START_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT >> ADDED_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT >> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = >> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> READ_CODE_E5 = >> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> TERM_TEXT_E5 = >> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> START_DATE_E5 = >> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> ADDED_DATE_E5 = >> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE >> PATIENT_ID = >> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE >> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT
Re: [sqlite] Limit statement size?
You have any way to normalize that original source table? It's full of extents, which is the first sign of badness in a table design and assured of complicating your life. Clay RB Smissaert wrote: > Yes, I agree it looks messy, but I used to do this in steps and after advice > I think from Igor Tandenik I lumped it all together and run it in one go, > which is a lot faster. > > RBS > > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: 28 January 2007 18:49 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > Wow! Talk about obfuscated code! I didn't even try to dig deeper than > a quick scan, but could this abomination be broken into multiple update > queries? On the surface it looks like each "group" is unique. If so, > wouldn't a transaction with multiple update statements be much more > efficient and a much lighter load on resources on a step by step basis? > I damn well know it would be much more pleasing to the eye! :-) > > Fred > >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Sunday, January 28, 2007 12:07 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Limit statement size? >> >> >> Is there any limit on the size of the SQL statements in SQLite? >> Didn't think this would come into play, but have now come across this >> query and wonder if this needs considering: >> >> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM >> GROUP_2 T WHERE >> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE >> FROM GROUP_2 T >> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE >> FROM GROUP_3 T >> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT >> NUMERIC_VALUE FROM >> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT >> ENTRY_ID FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT >> READ_CODE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT >> TERM_TEXT FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT >> START_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT >> ADDED_DATE FROM >> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT >> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = >> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> READ_CODE_E5 = >> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> TERM_TEXT_E5 = >> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> START_DATE_E5 = >> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> ADDED_DATE_E5 = >> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), >> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE >> PATIENT_ID = >> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE >> PATIENT_ID = >> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T >> WHERE PATIENT_ID = >> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE >> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM >> GROUP_7 T WHERE >> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE >> FROM GROUP_7 T >> WHERE PATIENT_ID = T.PID), EN
RE: [sqlite] Limit statement size?
Yes, I agree it looks messy, but I used to do this in steps and after advice I think from Igor Tandenik I lumped it all together and run it in one go, which is a lot faster. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 18:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Wow! Talk about obfuscated code! I didn't even try to dig deeper than a quick scan, but could this abomination be broken into multiple update queries? On the surface it looks like each "group" is unique. If so, wouldn't a transaction with multiple update statements be much more efficient and a much lighter load on resources on a step by step basis? I damn well know it would be much more pleasing to the eye! :-) Fred > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 28, 2007 12:07 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Limit statement size? > > > Is there any limit on the size of the SQL statements in SQLite? > Didn't think this would come into play, but have now come across this > query and wonder if this needs considering: > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE > FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT > NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT > ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT > READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT > TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT > START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT > ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE > PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE > FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT > NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT > ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT > READ_
RE: [sqlite] Limit statement size?
OK, thanks, size of the query shouldn't be a problem then.
Performance is fine as well. This particular query ran in 1.4 secs, which
in my app is fine as it is reporting software and results are expected to
take a bit of time.
This query is the final query of a number of queries that transpose a table
from a vertical layout to a horizontal layout and this is the best I can
come up with sofar.
Maintenance is not a problem either as this runs all automatic without any
user input.
This is what is going on to do the transpose:
CREATE TABLE 'MAX_ENTRY_COUNT' ([MEC_NODE_KEY] TEXT UNIQUE,
[MEC_MAX_ENTRY_COUNT] INTEGER)
CREATE TABLE 'A3Test115_F_COUNT' ([PATIENT_IDC] INTEGER PRIMARY KEY,
[PATIENT_COUNT] INTEGER)
INSERT INTO A3Test115_F_COUNT(PATIENT_IDC, PATIENT_COUNT) SELECT PATIENT_ID,
COUNT(PATIENT_ID) FROM A3Test115_F GROUP BY PATIENT_ID
analyze A3Test115_F_COUNT
SELECT MAX(PATIENT_COUNT) FROM A3Test115_F_COUNT
INSERT INTO MAX_ENTRY_COUNT(MEC_NODE_KEY, MEC_MAX_ENTRY_COUNT)
VALUES('3Test-1157F99B-DD0D-E64D-A154-0569C636C494', 15)
BEGIN TRANSACTION
DROP TABLE IF EXISTS GROUP_1
CREATE TABLE 'GROUP_1' ([PID] INTEGER PRIMARY KEY, [ENTRY_ID] INTEGER,
[READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [ADDED_DATE]
INTEGER, [NUMERIC_VALUE] REAL)
INSERT INTO GROUP_1 (PID, ENTRY_ID, READ_CODE, TERM_TEXT, START_DATE,
ADDED_DATE, NUMERIC_VALUE) SELECT T.PATIENT_ID, T.ENTRY_ID, T.READ_CODE,
T.TERM_TEXT, T.START_DATE, T.ADDED_DATE, T.NUMERIC_VALUE FROM A3Test115_F T
GROUP BY T.PATIENT_ID
CREATE INDEX IDX1_GROUP_1_ENTRY_ID ON GROUP_1(ENTRY_ID)
analyze GROUP_1
DROP TABLE IF EXISTS GROUP_2
CREATE TABLE 'GROUP_2' ([PID] INTEGER PRIMARY KEY, [ENTRY_ID] INTEGER,
[READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [ADDED_DATE]
INTEGER, [NUMERIC_VALUE] REAL)
INSERT INTO GROUP_2 (PID, ENTRY_ID, READ_CODE, TERM_TEXT, START_DATE,
ADDED_DATE, NUMERIC_VALUE) SELECT T.PATIENT_ID, T.ENTRY_ID, T.READ_CODE,
T.TERM_TEXT, T.START_DATE, T.ADDED_DATE, T.NUMERIC_VALUE FROM A3Test115_F T
INNER JOIN GROUP_1 G ON (T.PATIENT_ID = G.PID) WHERE T.ENTRY_ID < G.ENTRY_ID
GROUP BY T.PATIENT_ID
CREATE INDEX IDX1_GROUP_2_ENTRY_ID ON GROUP_2(ENTRY_ID)
analyze GROUP_2
SELECT COUNT(ROWID) FROM GROUP_2
Etc, etc.
Not sure if it makes it clear what is going on, but if anybody has a better
idea how to transpose a table I would be interested.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 January 2007 18:20
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Limit statement size?
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there any limit on the size of the SQL statements in SQLite?
32-bit integers are used to count things in various places. I
don't really consider that a limit, but some people do.
See http://www.sqlite.org/cvstrac/tktview?tn=2125
There may be other limits that I do not yet know about because
nobody has yet reached them.
> Didn't think this would come into play, but have now come across this
> query and wonder if this needs considering:
That query will likely work, though efficiency might become an issue.
More importantly, it seems like it might be hard to maintain.
I would refer the author to
http://www.en.wikipedia.org/wiki/Database_normalization as a
starting point.
>
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2
T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WH
RE: [sqlite] Limit statement size?
Wow! Talk about obfuscated code! I didn't even try to dig deeper than a quick scan, but could this abomination be broken into multiple update queries? On the surface it looks like each "group" is unique. If so, wouldn't a transaction with multiple update statements be much more efficient and a much lighter load on resources on a step by step basis? I damn well know it would be much more pleasing to the eye! :-) Fred > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 28, 2007 12:07 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Limit statement size? > > > Is there any limit on the size of the SQL statements in SQLite? > Didn't think this would come into play, but have now come across this > query and wonder if this needs considering: > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE > FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT > NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT > ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT > READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT > TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT > START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT > ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE > PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE > FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT > NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT > ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT > READ_CODE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT > TERM_TEXT FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT > START_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT > ADDED_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > NUMERIC_VALUE FR
Re: [sqlite] Limit statement size?
"RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is there any limit on the size of the SQL statements in SQLite? 32-bit integers are used to count things in various places. I don't really consider that a limit, but some people do. See http://www.sqlite.org/cvstrac/tktview?tn=2125 There may be other limits that I do not yet know about because nobody has yet reached them. > Didn't think this would come into play, but have now come across this > query and wonder if this needs considering: That query will likely work, though efficiency might become an issue. More importantly, it seems like it might be hard to maintain. I would refer the author to http://www.en.wikipedia.org/wiki/Database_normalization as a starting point. > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 = > (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 = > (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10 > = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE
[sqlite] Limit statement size?
Is there any limit on the size of the SQL statements in SQLite? Didn't think this would come into play, but have now come across this query and wonder if this needs considering: UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM GROUP_2 T WHERE PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 = (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 = (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 = (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 = (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID = T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID = T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 = (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 = (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10 = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E11 = (SELECT ENTRY_ID FROM GROUP_11 T WHERE PATIENT_ID = T.PID), READ_CODE_E11 = (SELECT READ_CODE FROM GROUP_11 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E11 = (SELECT TERM_TEXT FROM GROUP_11 T WHERE PATIENT_ID = T.PID), START_DATE_E11 = (SELECT START_DATE FROM GROUP_11 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E11 = (SELECT ADDED_DATE FROM GROUP_11 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E11 = (SELECT NUMERIC_VALUE FROM GROUP_11 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E12 = (SELECT ENTRY_ID FROM GROUP_12 T WHERE PATIENT_ID = T.PID), READ_CODE_E12 = (SELECT READ_CODE FROM GROUP_12 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E12 = (SEL
