CODEWITHSUNDEEP
mysql
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

mysql stored procedure can't retrieve temporary table

I am creating a stored procedure which I will retrieve if there is a data retrieved. I am storing the 1st query result in a temporary table but when I retrieve the data in temporary table it returns an erro #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM tempt END' at line 40. But when I try the query it works fine. Here is the command written

CREATE PROCEDURE test()
BEGIN
CREATE TEMPORARY TABLE tempt
SELECT userInput.ap_id,
       b.accountName,
       b.accountNumber,
       userInput.date,
       p.payeeName,
       t.taxType,
       t.value businessTax,
       userInput.grossAmount,
       userInput.taxableBase,
       CAST(userInput.taxableBase * t.value as decimal(11,2)) as taxAmount,
       userInput.grossAmount - (CAST(userInput.taxableBase * t.value as decimal(11,2))) as netOfVAT,
       particulars,
       accounts,
       NOW(),
       1,
       e.type,
       e.value ewtTax,
       ewtBase
FROM
(
    SELECT CONCAT('AP-',YEAR(NOW()),MONTH(NOW()),DAY(NOW()),'-',(COUNT(*) + 1)) as ap_id,
           paccountNumber as accountNumber,
           pDate as date,
           ppayee as payee,
           pgross as grossAmount,
           ptaxBase as taxableBase,
           pparticulars as particulars,
           paccounts as accounts,
           pewtBase as ewtBase
    FROM ap_entry
) userInput
INNER JOIN bnks b ON userInput.accountNumber = b.accountNumber
INNER JOIN pyee p ON userInput.payee = p.payeeName
INNER JOIN txtype t ON p.taxType = t.taxType
INNER JOIN ewt e ON p.ewt = e.id

SELECT COUNT(*) FROM tempt
END

Upvotes: 1

Views: 42

Answers (1)

James
James

Reputation: 1829

In your query semi colon is missing.

DELIMITER $$

CREATE PROCEDURE test()
BEGIN
CREATE TEMPORARY TABLE tempt
SELECT userInput.ap_id,
       b.accountName,
       b.accountNumber,
       userInput.date,
       p.payeeName,
       t.taxType,
       t.value businessTax,
       userInput.grossAmount,
       userInput.taxableBase,
       CAST(userInput.taxableBase * t.value as decimal(11,2)) as taxAmount,
       userInput.grossAmount - (CAST(userInput.taxableBase * t.value as decimal(11,2))) as netOfVAT,
       particulars,
       accounts,
       NOW(),
       1,
       e.type,
       e.value ewtTax,
       ewtBase
FROM
(
    SELECT CONCAT('AP-',YEAR(NOW()),MONTH(NOW()),DAY(NOW()),'-',(COUNT(*) + 1)) as ap_id,
           paccountNumber as accountNumber,
           pDate as date,
           ppayee as payee,
           pgross as grossAmount,
           ptaxBase as taxableBase,
           pparticulars as particulars,
           paccounts as accounts,
           pewtBase as ewtBase
    FROM ap_entry
) userInput
INNER JOIN bnks b ON userInput.accountNumber = b.accountNumber
INNER JOIN pyee p ON userInput.payee = p.payeeName
INNER JOIN txtype t ON p.taxType = t.taxType
INNER JOIN ewt e ON p.ewt = e.id; -- semi colon missing

SELECT COUNT(*) FROM tempt; -- semi colon missing
END
$$
DELIMITER ; -- changed back to default

Upvotes: 1

Related Questions