Paul,
I'd suggest that if you know that the value is going to be null that
you not include that column in your column or value list. The insert
will create a null for the column regardless.
Ben Petersen
On 15 May 2001, at 19:03, [EMAIL PROTECTED] wrote:
>
> Greetings, all. Since Razzak has already made my day with today's
> announcement, if I get an answer to this, I'll be in heaven
>
> Latest RBWIN65 version.
>
> I'm having problems understanding why the insert below fails in the way it
> does. The problem part in in larger type.
>
> INSERT INTO scbcrse (scbcrse_subj_code,scbcrse_crse_numb,scbcrse_csta_code,
> +
> scbcrse_repeat_lim, scbcrse_pwav_code, scbcrse_tuiw_ind,+
> scbcrse_add_fees_i, scbcrse_actv_date,
> scbcrse_reps_code,scbcrse_max_rpt_u,+
> scbcrse_capp_ind ) +
> VALUES (.vnewprefix,.vcourse,.vactive, +
> .breplimit,.vprereqs,, .vsfeecode,+
> .#DATE,.brepcode,.brepunits,.vbareaflag)
>
>
> "breplimit" is a integer field - corresponds to "scbcrse_repeat_lim" and is
> always 1 to 10.
> "vprereqs" is a character field - corresponds to "scbcrse_pwav_code", and
> can be null.
> note the extra comma between vprereqs and vsfeecode. This simply skips
> loading anything into "scbcrse_tuiw_i" (a text field)
> as far as I can tell from the insert documentation.
> "vsfeecode" is a text field - corresponds to "scbcrse_add_fees_i", and can
> be null.
>
> When vprereqs is null, the insert fails, and I get an "incorrect number of
> values for this table"
> If vprereqs is not null, the insert performs correctly.
>
> Here's the kicker. If I change the location of the commas so that
> vprereqs now loads into "scbcrse_tuiw_i", the insert never fails, even with
> null values.
> Null in my case is nothing, not -0-.
>
> In that case the segment looks like this: .breplimit,,.vprereqs,
> .vsfeecode,+
>
> So, there's something about loading a null text value after an interger
> field, or something, that makes the insert get lost, I suppose.
>
> The complete code is below. It's actually a compact version of a larger
> program, but the problem performs the same. In the larger version, I have
> a lot of ',,,,' to skip loading columns. As long as the problem above is
> avoided, it all works fine.
>
> Anybody experience this?
>
>
> Paul Patrick [EMAIL PROTECTED]
> University of Central Oklahoma
> Edmond, OK 73034
> (405) 974-2336 fax (405) 341-4964
>
> SET WHILEOPT OFF
> SET VAR ldq TEXT = '"', rdq TEXT = '"', mq = '","'
> SET VAR blabhrs REAL = NULL, blechrs REAL = NULL, bcontacthrs REAL = NULL
> SET VAR bcredithrs REAL = NULL, bbillhrs REAL = NULL, breplimit INTEGER = 0
> SET VAR brepcode TEXT = NULL, brepunits REAL = NULL
> SET VAR scbcrs_line TEXT = NULL, bpwavcod TEXT = NULL
> DELETE ROWS FROM scbcrse
> DECLARE c1 CURSOR +
> FOR SELECT active, newschool, newdept, newprefix, course, hours, prereqs,+
> sfeecode,bareaflag, descript, common FROM courses WHERE newprefix = 'BIO' +
> ORDER BY cprenum
> OPEN c1
> FETCH c1 +
> INTO vactive IND v1, vnewschool IND v1, vnewdept IND v1, vnewprefix IND v1,
> +
> vcourse IND v1, vhours IND v1, vprereqs IND v1, vsfeecode IND v1,+
> vbareaflag IND v1, vdescript IND v1,vcommon IND v1
>
> WHILE SQLCODE = 0 THEN
> IF vcommon = 'Y' THEN
> SET VAR breplimit = 10,brepunits = 40,brepcode = 'RP'
> ELSE
> SET VAR breplimit = 1, brepunits = .vhours, brepcode = 'RP'
> ENDIF
> IF vsfeecode IS NOT NULL THEN
> SET VAR vsfeecode = 'Y'
> ENDIF
> IF vprereqs IS NOT NULL THEN
> SET VAR vprereqs = 'C'
> ENDIF
> SET VAR vbareaflag = 'N'
> INSERT INTO scbcrse
> (scbcrse_subj_code,scbcrse_crse_numb,scbcrse_csta_code,+
> scbcrse_repeat_lim, scbcrse_pwav_code, scbcrse_tuiw_ind,+
> scbcrse_add_fees_i, scbcrse_actv_date,
> scbcrse_reps_code,scbcrse_max_rpt_u,+
> scbcrse_capp_ind ) +
> VALUES (.vnewprefix,.vcourse,.vactive,.breplimit,,.vprereqs,+
> .vsfeecode,.#DATE,.brepcode,.brepunits,.vbareaflag)
> FETCH c1 +
> INTO vactive IND v1, vnewschool IND v1, vnewdept IND v1, vnewprefix IND
> v1,+
> vcourse IND v1, vhours IND v1, vprereqs IND v1, vsfeecode IND v1,+
> vbareaflag IND v1, vdescript IND v1,vcommon IND v1
> ENDWHILE
>
>
>
