insert of aggregated value becomes null?

[raz0r]

When i do call this in console prompt I get the correct value, but when it comes to inserting it, it inserts a null value? What do you guys think of that?

INSERT INTO clist2 SELECT CEIL((o + m + p)/6)* Cost) as FinalCost ...

[r937]

how many columns in the target table?

[raz0r]

6, all but two has the "ceil" function

It seems that the "*cost" is somehow causing the trouble since another column has "CEIL((o + m + p)/6)" and it insert into the column just fine..

[longneck]

is cost NULL for any of the rows? because any operation that includes a NULL generally results in a NULL. in other words, NULL is not the same as 0.

[raz0r]

is cost NULL for any of the rows? because any operation that includes a NULL generally results in a NULL. in other words, NULL is not the same as 0.

nope, they all contain an INT value. if i get an output value when i call it through console. shouldn't they be the same when it comes to inserted value?

[r937]

could you please do a SHOW CREATE TABLE for both tables, as well as the entire query

[raz0r]

insert to the table clist2 of col tID, NormalTime, CrashTime all enter without a problem is just the last 3.

INSERT INTO clist2 SELECT tID, CEIL((Tp + (4*Tm) + Top)/6) AS NormalTime, Ceil(Top) as CrashTime, (CEIL((Tp + (4*Tm) + Top)/6)* Cost) as NormalCost,
          (CEIL((Tp + (4*Tm) + Top)/6)* Cost) + ((CEIL((Tp + (4*Tm) + Top)/6)-Ceil(Top))*Cost) AS CrashCost, Cost FROM tlist WHERE pID = IN_pID AND tID = d;

CREATE TABLE clist2(
  tID INT (11) DEFAULT NULL,
  NormalTime INT (11) DEFAULT NULL,
  CrashTime INT (11) DEFAULT NULL,
  NormalCost INT (11) DEFAULT NULL,
  CrashCost INT (11) DEFAULT NULL,
  AdditionalCost INT (11) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET latin1
COLLATE latin1_swedish_ci;
 
--
-- Definition for table tlist
--
CREATE TABLE tlist(
  ID INT (8) NOT NULL AUTO_INCREMENT,
  pID INT (11) NOT NULL,
  tID INT (11) NOT NULL,
  tName CHAR (128) NOT NULL,
  Tm FLOAT NOT NULL,
  Tp FLOAT NOT NULL,
  Top FLOAT NOT NULL,
  Cost INT (11) NOT NULL,
  activityTypeID INT (1) NOT NULL,
  PRIMARY KEY (ID),
  INDEX pID USING BTREE (pID),
  INDEX tID USING BTREE (tID),
  INDEX activityTypeID USING BTREE (activityTypeID),
  CONSTRAINT tlist_ibfk_1 FOREIGN KEY (pID)
  REFERENCES plist (pID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT tlist_ibfk_2 FOREIGN KEY (activityTypeID)
  REFERENCES atype (aID) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
AUTO_INCREMENT = 10
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

[r937]

that all looks fine

i am stumped

[r937]

It seems that the "*cost" is somehow causing the trouble

that was my suspicion too, but it's defined as NOT NULL

[raz0r]

this iis done in a stored procdure could it be the problem?

 
REPEAT
  SET d = i;
 
    INSERT INTO clist2 SELECT tID, CEIL((Tp + (4*Tm) + Top)/6) AS NormalTime, Ceil(Top) as CrashTime, (CEIL((Tp + (4*Tm) + Top)/6)* Cost) as NormalCost,
          (CEIL((Tp + (4*Tm) + Top)/6)* Cost) + ((CEIL((Tp + (4*Tm) + Top)/6)-Ceil(Top))*Cost) AS CrashCost, Cost FROM tlist WHERE pID = IN_pID AND tID = d; 
 
    SET i = i + 1;
  UNTIL i > c
  END REPEAT;

[raz0r]

OK... i discover the problem while i try to find a different way to insert.
it is "Cost" that was causing the problem.

... CEIL((Tp + (4 * Tm) + Top) / 6) * Cost) + ((CEIL((Tp + (4 * Tm) + Top) / 6) - CEIL(Top)) * Cost) AS CrashCost, Cost ....

Cost of the table was used in teh aggregated calculation, and i also select the "Cost", but i set it "INTO Cost" variable declare the same name as the selected col instead of somethingelse like "INTO v_Cost" which somehow solved the problem.