Pavel, The goal is to get them all into one row, correct.
The query does not seem to do anything, the table is empty after I run the query. One question... In the PP_VIEWER_SETTINGS, I have columns of different types, but in DBLookup all the values are varchar's. Since sqlite doesn't actually deal with column types, I figured this would not be an issue. Might it be? Should I be casting them to the correct type first? Sam On Thu, Jul 5, 2012 at 11:24 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > The insert statement below should insert one row into table > PP_VIEWER_SETTINGS. Does it do that? Is it what you called "does not > work"? To insert several rows you need to write a huge join of > dblookup to itself, so your insert statement should look like this: > > insert into PP_VIEWER_SETTINGS (...) > select a.ItemValue, b.ItemValue, c.ItemValue, ... > from dblookup a, dblookup b, dblookup c, ... > where a.Category = "KvsSettings" > and a.ItemName = "Company" > and b.Category = "KvsSettings" > and b.ItemName = "DspNextPrevious" > and c.Category = "KvsSettings" > and c.ItemName = "EnableCarts" > ... > and a.? = b.? > and a.? = c.? > ... > ; > > Question marks here is the field which value should identify what row > particular ItemName should go to. > > > Pavel > > > On Thu, Jul 5, 2012 at 11:03 PM, Sam Carleton > <scarle...@miltonstreet.com> wrote: > > I am working on converting my system table from one form to another. The > > old form was one row per value with a category/key/value (DBLookup) , the > > new form is a separate column for each value (PP_VIEWER_SETTINGS). I am > > trying to create an insert statement to run when the new table is > created, > > but when I run it, it does not work nor do I get any errors in SQLite > > manager: > > > > /* The OLD one */ > > CREATE TABLE DBLookup ( > > Category VARCHAR(32) NOT NULL, > > ItemName VARCHAR(128) NOT NULL, > > ItemValue VARCHAR(3000) NOT NULL, > > PRIMARY KEY(Category, ItemName)) > > > > /* The NEW one */ > > CREATE TABLE PP_VIEWER_SETTINGS > > ( > > VIEWER_SETTINGS_ID INTEGER PRIMARY KEY > > AUTOINCREMENT, > > COMPANY_NAME VARCHAR( 260) NOT NULL, > > DSPNEXTPREVIOUS SMALLINT NOT NULL, > > ENABLE_CARTS SMALLINT NOT NULL, > > ENABLE_DEBUGINFO SMALLINT NOT NULL, > > ENABLE_FAVORITES SMALLINT NOT NULL, > > ENABLE_RIGHTCLICK SMALLINT NOT NULL, > > ENABLE_SLIDESHOW SMALLINT NOT NULL, > > ENABLE_TIMEOUT SMALLINT NOT NULL, > > EXIT_KVS SMALLINT NOT NULL, > > EXIT_PASSWORD VARCHAR( 20) NOT NULL, > > IS_CART_FAVORITES SMALLINT NOT NULL, > > IS_LOGIN_REQUIRED SMALLINT NOT NULL, > > IMAGE_SIZE INTEGER NOT NULL, > > PHONE_NUM_FORMAT VARCHAR( 20) NOT NULL, > > THEME_ID INTEGER NOT NULL, > > THUMBNAIL_SIZE SMALLINT NOT NULL, > > TICKER_MSG VARCHAR( 260) NOT NULL, > > TO_AFTER SMALLINT NOT NULL, > > TO_STARTS SMALLINT NOT NULL, > > TO_TRANSITION_SECS SMALLINT NOT NULL, > > SS_COUNT SMALLINT NOT NULL, > > SS_DEFAULT_IS_IN_SLIDESHOW SMALLINT NOT NULL, > > SS_DISPLAY_SECONDS DOUBLE PRECISION NOT NULL, > > SS_ZOOM_FACTOR DOUBLE PRECISION NOT NULL, > > USERLAN VARCHAR( 260) NOT NULL > > ); > > > > /* The insert script */ > > > > insert into PP_VIEWER_SETTINGS > > ( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO, > > ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW, > > ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES, > > IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID, > > THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, > TO_TRANSITION_SECS, > > SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW, > > SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN) > > values ( > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "Company"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "DspNextPrevious"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "EnableCarts"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "EnableDebugInfo"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "EnableFavorites"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "EnableRightClick"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "EnableSlideShow"), > > 1, > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "ExitKvs"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "ExitPassword"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "IsCartFavorites"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "IsLoginRequired"), > > 900, > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "PhoneNumberFormat"), > > 0, > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "ThumbnailSize"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "TickerMsg"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "TimeoutAfter"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "TimeoutStarts"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "TransitionSeconds"), > > 0, > > (select ItemValue from dblookup where Category = > > "SlideShowSettings" and ItemName = "DefaultIsInSlideShow"), > > (select ItemValue from dblookup where Category = > > "SlideShowSettings" and ItemName = "DisplaySeconds"), > > (select ItemValue from dblookup where Category = > > "SlideShowSettings" and ItemName = "ZoomFactor"), > > (select ItemValue from dblookup where Category = "KvsSettings" > and > > ItemName = "USERLAN")); > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
