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 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 = (SELECT TERM_TEXT
FROM
> GROUP_12 T WHERE PATIENT_ID = T.PID), START_DATE_E12 = (SELECT START_DATE
> FROM GROUP_12 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E12 = (SELECT
> ADDED_DATE FROM GROUP_12 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E12 =
> (SELECT NUMERIC_VALUE FROM GROUP_12 T WHERE PATIENT_ID = T.PID),
> ENTRY_ID_E13 = (SELECT ENTRY_ID FROM GROUP_13 T WHERE PATIENT_ID = T.PID),
> READ_CODE_E13 = (SELECT READ_CODE FROM GROUP_13 T WHERE PATIENT_ID =
T.PID),
> TERM_TEXT_E13 = (SELECT TERM_TEXT FROM GROUP_13 T WHERE PATIENT_ID =
T.PID),
> START_DATE_E13 = (SELECT START_DATE FROM GROUP_13 T WHERE PATIENT_ID =
> T.PID), ADDED_DATE_E13 = (SELECT ADDED_DATE FROM GROUP_13 T WHERE
PATIENT_ID
> = T.PID), NUMERIC_VALUE_E13 = (SELECT NUMERIC_VALUE FROM GROUP_13 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E14 = (SELECT ENTRY_ID FROM GROUP_14 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E14 = (SELECT READ_CODE FROM GROUP_14 T
WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E14 = (SELECT TERM_TEXT FROM GROUP_14 T
WHERE
> PATIENT_ID = T.PID), START_DATE_E14 = (SELECT START_DATE FROM GROUP_14 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E14 = (SELECT ADDED_DATE FROM
GROUP_14
> T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E14 = (SELECT NUMERIC_VALUE
FROM
> GROUP_14 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E15 = (SELECT ENTRY_ID FROM
> GROUP_15 T WHERE PATIENT_ID = T.PID), READ_CODE_E15 = (SELECT READ_CODE
FROM
> GROUP_15 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E15 = (SELECT TERM_TEXT
FROM
> GROUP_15 T WHERE PATIENT_ID = T.PID), START_DATE_E15 = (SELECT START_DATE
> FROM GROUP_15 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E15 = (SELECT
> ADDED_DATE FROM GROUP_15 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E15 =
> (SELECT NUMERIC_VALUE FROM GROUP_15 T WHERE PATIENT_ID = T.PID)
>
>
> RBS
>
>
>
>
----------------------------------------------------------------------------
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
----------------------------------------------------------------------------
-
>
>
> .
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
