Re: Converting data to Informix from PICK!

In article <4qp79i$ftn@newsbf02.news.aol.com>, pfsiegel@aol.com (P F SIEGEL) wrote: >My company is moving from a Pick database to Informix. I need to transfer >/ convert only our data >Some of my concerns are dealing with Pick's variable field lengths and >multi-values. 1. I haven't done this. 2. The variable field lengths shouldn't be a problem (you'll have to make-up some maximum field size, or use Online's VARCHAR data type). 3. Multi-value fields *will* be a problem. I'd do it like this: 3.1 Load the multi-value fields all into one fat database column (VARCHAR or CHAR -- shouldn't matter). 3.2 Build a new table for each multi-valued column, with an appropriate unique key to reference the original table. 3.3 To populate the new table, run a 4GL program that looks something like this: DECLARE conv_curs CURSOR FOR SELECT header_key, mv_column FROM header_table FOREACH conv_curs INTO l_key, l_mv LET l_mv = l_mv CLIPPED, CHAR(254) FOR i = 1 TO LENGTH(l_mv) IF l_mv[i]=CHAR(254) THEN INSERT INTO mv_table (header_key, value) VALUES (l_key, l_value) ELSE LET l_value = l_value CLIPPED, l_mv[i] END IF END FOR END FOREACH PS : I *think* a multi-value mark is ASCII 254. PPS: If you've used sub-value marks (ASCII 253?) you're a) Going to have to create another sub-table b) Going to have to add another level of parsing c) Going to have to be patient! 3.4 Drop the "fat database column" you created in 3.1. Good luck! ------------------------------------,------------------------------------------ Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ v2.5 June 96 Quanta Systems, Auckland | http://www.iiug.org/techinfo/faq/ New Zealand. Work: +64 9 377-4473 | ftp://ftp.iiug.org/pub/informix/faq Home: +64 9 279-3571 | ftp://kcbbs.gen.nz:/pub/informix/