Greg Kelley wrote:
>
> I have created a SQL script file that creates a table then populates it with
> INSERT INTO table VALUES ('blah','blah','etc')
>
> MySQL is running under Redhat Linux v5.2
>
> It appears that values of NULL cause subsequent field values to shift up and
> fill in what should have been the NULL value. The script seems OK - is
> there a problem with MySQL and scripts?
>
> The 5th field (expdt) instead of a getting NULL value is getting the 6th
> value (28540) that belongs to field 6 (anlrt). I have 11 tables and over
> 150,000 records to move from M$-Acce$$ to MySQL.
>
> Sorry for the large mail message, but here is part of the script: (thanks
> for any insights...)
>
> CREATE TABLE HR1
> (
> usid CHAR(9) NOT NULL,
> psnum CHAR(6),
> pstyp CHAR(1),
> birdt DATETIME,
> expdt DATETIME,
> anlrt REAL,
> hrlrt REAL,
> dayyr INT,
> hrsyr INT,
> ftebs CHAR(1),
> fte INT,
> vpr CHAR(2),
> band CHAR(1),
> amsrg CHAR(2),
> exe INT,
> clscd CHAR(5),
> bench CHAR(1),
> hegis CHAR(6),
> agg CHAR(6),
> shtxt CHAR(15),
> octyp CHAR(1),
> cltit CHAR(50),
> entry CHAR(1),
> arfrdt DATETIME,
> clsyrs REAL,
> aclsyrs REAL,
> hire DATETIME,
> tendt DATETIME,
> fname CHAR(15),
> mname CHAR(15),
> lname CHAR(20),
> rarea CHAR(3),
> rorg CHAR(4),
> objno CHAR(4),
> arave REAL,
> hrave REAL,
> ams_band_arave REAL,
> bench_arave REAL,
> armpt INT,
> hrmpt REAL,
> ardef INT,
> hrdef REAL,
> sal_def INT,
> bench_sal_def INT,
> cls_sal_def INT,
> hmin REAL,
> hmean REAL,
> hmax REAL,
> hquart REAL,
> amin INT,
> amean INT,
> amax INT,
> aquart INT,
> band_amean INT,
> adj_amin INT,
> adj_amean INT,
> adj_amax INT,
> adj_aquart INT,
> tot_in_class INT,
> min_hrlrt REAL,
> avg_hrlrt REAL,
> max_hrlrt REAL,
> min_anlrt INT,
> avg_anlrt INT,
> max_anlrt INT,
> bench_avg_anlrt INT,
> fte_anlrt REAL,
> aye_anlrt INT,
> mark_pct INT,
> emp_mark_pct INT,
> bench_mark_pct INT,
> over_min INT,
> emp_sur_adj_anl INT,
> sur_adj_anl INT,
> emp_sur_adj_hrl REAL,
> sur_adj_hrl REAL,
> rpene INT,
> pscd INT,
> h3quart REAL,
> a3quart INT,
> adj_a3quart INT,
> KEY (usid)
> )\g
>
> INSERT INTO HR1
> VALUES (
> '012345678',
> 'KAE067',
> 'B',
> '1988-07-01 00:00:00',
> NULL,
> 28540,
> 0,
> 196,
> 0,
> '6',
> 75,
> '15',
> NULL,
> NULL,
> 1,
> '00265',
> '0',
> NULL,
> NULL,
> NULL,
> 'P',
> 'HEALTH EDUCATOR/COUNSELOR',
> 'N',
> '1988-10-12 00:00:00',
> 8.7,
> 5.7,
> '1988-10-12 00:00:00',
> NULL,
> 'JOHN',
> NULL,
> 'SMITH',
> 'KSH',
> 'KC24',
> '0650',
> 38797,
> 0,
> 0,
> 0,
> 37268,
> 0,
> -19,
> 0,
> -19,
> 0,
> 70,
> 0,
> 0,
> 0,
> 0,
> 28920,
> 36570,
> 44210,
> 32740,
> 0,
> 21690,
> 27430,
> 33160,
> 24560,
> 7,
> 0,
> 0,
> 0,
> 30290,
> 36070,
> 44200,
> 0,
> 38050,
> 0,
> 76,
> 20,
> 0,
> 316,
> 560,
> 19474,
> 0,
> 0,
> 597,
> 2,
> NULL,
> 40390,
> 30290 )\g
>
> ___________________________
> Greg Kelley
Hi Greg
Couldn't reproduce any errors with mysql 3.22.20a
I get this:
mysql> select * from HR1;
+-----------+--------+-------+---------------------+-------+
| usid | psnum | pstyp | birdt | expdt |
+-----------+--------+-------+---------------------+-------+
| 012345678 | KAE067 | B | 1988-07-01 00:00:00 | NULL |
+-----------+--------+-------+---------------------+-------+
------------+--------+-------+-------+-------+------+------+
anlrt | hrlrt | dayyr | hrsyr | ftebs | fte | vpr |
------------+--------+-------+-------+-------+------+------+
28540.0000 | 0.0000 | 196 | 0 | 6 | 75 | 15 |
------------+--------+-------+-------+-------+------+------+
------+-------+------+-------+-------+-------+------+-------+
band | amsrg | exe | clscd | bench | hegis | agg | shtxt |
------+-------+------+-------+-------+-------+------+-------+
NULL | NULL | 1 | 00265 | 0 | NULL | NULL | NULL |
------+-------+------+-------+-------+-------+------+-------+
-------+---------------------------+-------+---------------------+
octyp | cltit | entry | arfrdt |
-------+---------------------------+-------+---------------------+
P | HEALTH EDUCATOR/COUNSELOR | N | 1988-10-12 00:00:00 |
-------+---------------------------+-------+---------------------+
--------+---------+---------------------+-------+-------+-------+
clsyrs | aclsyrs | hire | tendt | fname | mname |
--------+---------+---------------------+-------+-------+-------+
8.7000 | 5.7000 | 1988-10-12 00:00:00 | NULL | JOHN | NULL |
--------+---------+---------------------+-------+-------+-------+
-------+-------+------+-------+------------+--------+----------------+
lname | rarea | rorg | objno | arave | hrave | ams_band_arave |
-------+-------+------+-------+------------+--------+----------------+
SMITH | KSH | KC24 | 0650 | 38797.0000 | 0.0000 | 0.0000 |
-------+-------+------+-------+------------+--------+----------------+
-------------+-------+--------+-------+--------+---------+---------------+
bench_arave | armpt | hrmpt | ardef | hrdef | sal_def | bench_sal_def |
-------------+-------+--------+-------+--------+---------+---------------+
0.0000 | 37268 | 0.0000 | -19 | 0.0000 | -19 | 0 |
-------------+-------+--------+-------+--------+---------+---------------+
-------------+--------+--------+--------+--------+-------+-------+-------+
cls_sal_def | hmin | hmean | hmax | hquart | amin | amean | amax |
-------------+--------+--------+--------+--------+-------+-------+-------+
70 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 28920 | 36570 | 44210 |
-------------+--------+--------+--------+--------+-------+-------+-------+
--------+------------+----------+-----------+----------+------------+
aquart | band_amean | adj_amin | adj_amean | adj_amax | adj_aquart |
--------+------------+----------+-----------+----------+------------+
32740 | 0 | 21690 | 27430 | 33160 | 24560 |
--------+------------+----------+-----------+----------+------------+
--------------+-----------+-----------+-----------+-----------+-----------+
tot_in_class | min_hrlrt | avg_hrlrt | max_hrlrt | min_anlrt | avg_anlrt |
--------------+-----------+-----------+-----------+-----------+-----------+
7 | 0.0000 | 0.0000 | 0.0000 | 30290 | 36070 |
--------------+-----------+-----------+-----------+-----------+-----------+
-----------+-----------------+------------+-----------+----------+
max_anlrt | bench_avg_anlrt | fte_anlrt | aye_anlrt | mark_pct |
-----------+-----------------+------------+-----------+----------+
44200 | 0 | 38050.0000 | 0 | 76 |
-----------+-----------------+------------+-----------+----------+
--------------+----------------+----------+-----------------+-------------+
emp_mark_pct | bench_mark_pct | over_min | emp_sur_adj_anl | sur_adj_anl |
--------------+----------------+----------+-----------------+-------------+
20 | 0 | 316 | 560 | 19474 |
--------------+----------------+----------+-----------------+-------------+
-----------------+-------------+-------+------+---------+---------+-------------+
emp_sur_adj_hrl | sur_adj_hrl | rpene | pscd | h3quart | a3quart | adj_a3quart |
-----------------+-------------+-------+------+---------+---------+-------------+
0.0000 | 0.0000 | 597 | 2 | NULL | 40390 | 30290 |
-----------------+-------------+-------+------+---------+---------+-------------+
1 row in set (0.01 sec)
Tschau
Christian
