> The query does not seem to do anything, the table is empty after I run the > query.
This can only mean that there was some error executing insert. It's written in a way that it just cannot leave empty table for any reason except error. You have all columns NOT NULL, so maybe dblookup doesn't have value for some column and thus the whole insert fails. > 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? You don't have to. Your types declared in such way that SQLite will automatically try to convert all varchar values to integers and doubles where necessary. If SQLite won't be able to convert it will insert text value provided. But if you put explicit casting then SQLite will insert 0 if cast won't be successful. Pavel On Fri, Jul 6, 2012 at 2:45 PM, Sam Carleton <scarle...@miltonstreet.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
