SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    insert of aggregated value becomes null?

    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?


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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how many columns in the target table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you please do a SHOW CREATE TABLE for both tables, as well as the entire query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    insert to the table clist2 of col tID, NormalTime, CrashTime all enter without a problem is just the last 3.

    Code MySQL:
    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;
    Code MySQL:
    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;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that all looks fine

    i am stumped
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by raz0r View Post
    It seems that the "*cost" is somehow causing the trouble
    that was my suspicion too, but it's defined as NOT NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this iis done in a stored procdure could it be the problem?
    Code MySQL:
     
    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;

  11. #11
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK... i discover the problem while i try to find a different way to insert.
    it is "Cost" that was causing the problem.

    Code:
    ... 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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •