0

I am using PHPMyAdmin and MariaDB and get this weird error as mentioned in the title.

CALL log_total_outstanding(@message);
MySQL said: Documentation

#1054 - Unknown column 'time' in 'field list'

I have read online this issue can be due to using "" instead of '' or due to whitespace but i have neither of these issues. I also don't have a column called 'time' anywhere.

Code:

USE bank;

DROP TABLE IF EXISTS total_outstanding_log;
CREATE TABLE total_outstanding_log (
    total_outstanding DECIMAL(13,2) NOT NULL,
    log_time DATETIME NOT NULL
);

DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS insert_total_outstanding_log(
         IN total DECIMAL(13,2),
         OUT reply VARCHAR(50)) 
BEGIN
    
    INSERT INTO total_outstanding_log (total_outstanding,log_time)
           VALUES(total, CURRENT_DATE); -- Insert into log table
    SET REPLY = 'Total outstanding log inserted successfully '
            || CHAR(CURRENT_DATE); -- Set the out param message

END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS log_total_outstanding(OUT message VARCHAR(50)) 
BEGIN
    
    -- DECLARE @total_outstanding DECIMAL(13,2);
                -- Used to store total outstanding amount
    
    SELECT
        SUM(a.opening_balance)
        +
        (SELECT SUM(amount)
        FROM transaction WHERE isIncoming)
        -
        (SELECT SUM(Amount)
        FROM transaction WHERE isOutgoing) AS total_outstanding
        INTO @total_outstanding -- Assign the value to declared variable
    FROM account a;
    
    -- Call inserting log procedure (nested)
    CALL insert_total_outstanding_log(@total_outstanding, message);

END $$
DELIMITER ;

CALL log_total_outstanding(@message);
SELECT @message; -- Display total oustanding log message

The

SELECT
        SUM(a.opening_balance)
        +
        (SELECT SUM(amount)
        FROM transaction WHERE isIncoming)
        -
        (SELECT SUM(Amount)
        FROM transaction WHERE isOutgoing) AS total_outstanding
        INTO @total_outstanding -- Assign the value to declared variable
    FROM account a;

Does work alone I have tested.

4
  • "create if not exists". Are you sure the procedures in the database correspond to what you show? Eg. select into @ total_outstanding while the declare @ total_outstanding is commented out. Jan 10 at 14:32
  • Do you have any triggers? Jan 10 at 15:38
  • @BillKarwin nope
    – Mj _
    Jan 10 at 17:42
  • 1
    I don't see any reference to an identifier time in the code you posted. I guess the error is not coming from this code. Jan 10 at 21:51
1

|| is not for string concatenation in MySQL/MariaDB. Use the CONCAT(..., ...) function. Here is what MySQL says:

mysql> select 'Total outstanding log inserted successfully '             || CHAR(CURRENT_DATE);
+----------------------------------------------------------------------------------+
| 'Total outstanding log inserted successfully '             || CHAR(CURRENT_DATE) |
+----------------------------------------------------------------------------------+
|                                                                                0 |
+----------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings
    -> ;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Total outstanding log inserted successfully '                      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '\x014\x88\xCE'                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------+
1
  • Thanks this fixed it also dropping my procedure
    – Mj _
    Jan 11 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.