How do I transfer below stores procedure into MySQL stored procedure

Question

1.00/5 (2 votes)

See more:

CREATE PROCEDURE [dbo].[TrialBalance] AS SELECT COA_T .AccCode AS [Account Code], COA_T.AccName AS [Account Name],
CASE WHEN SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
THEN DebitAmt-CreditAmt ELSE 0 END) > 0 THEN (SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses') and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
THEN DebitAmt-CreditAmt end )) ELSE 0 END AS [Debit],

CASE WHEN SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
THEN DebitAmt-CreditAmt ELSE 0 END) < 0 THEN (SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses') and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
THEN DebitAmt-CreditAmt end )) ELSE 0 END AS [Credit]

FROM COA_T JOIN Trans_T ON COA_T.AccName = Trans_T.AccName
WHERE COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
GROUP BY COA_T.AccCode,COA_T.AccName ORDER BY COA_T.AccCode

Posted 6-Jan-16 2:49am

Mathiudi

Add a Solution

Comments

ZurdoDev 6-Jan-16 8:53am

Is this MySql code or Microsoft SQL server code?

Mathiudi 6-Jan-16 8:57am

It is Microsoft SQL server code

ZurdoDev 6-Jan-16 8:59am

Then you'll need to re-write it into MySql, or there might be online translators. I've never needed to look before.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-01-06T03:25:00

Having a quick look the procedure doesn't seem to use anything very special to SQL Server so I suggest just modifying the create command and see if it compiles.

In other words something like:

SQL

CREATE PROCEDURE TrialBalance()
BEGIN
SELECT COA_T .AccCode AS [Account Code], COA_T.AccName AS [Account Name], 
CASE WHEN SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP') 
THEN DebitAmt-CreditAmt ELSE 0 END) > 0 THEN (SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses') and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP') 
THEN DebitAmt-CreditAmt end )) ELSE 0 END AS [Debit],
CASE WHEN SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP') 
THEN DebitAmt-CreditAmt ELSE 0 END) < 0 THEN (SUM (CASE WHEN COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses') and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP') 
THEN DebitAmt-CreditAmt end )) ELSE 0 END AS [Credit]
FROM COA_T JOIN Trans_T ON COA_T.AccName = Trans_T.AccName
WHERE COA_T.AccType in ('Cash & Cash Equivalents ','Marketable Securities','Accounts Receivable','Inventories','Prepaid Expenses','Fixed Asset','Intangible Asset','Investments','Other Non-current Assets','Accounts Payable','Accrued Expenses','Short-term Loans','Current Portion of Long-term Debt','Income Taxes Payable','Line of Credit','Term Loan','Capital','Current Year Earnings','Retained Earnings','Dividends','Share Capital','Paid in Capital','Open Balance','Sales Revenue','Operating Expenses','Administrative expenses')and COA_T.AccTransCate in ('BS','PL') and COA_T.AccTransSubCate in ('ASS','LBT', 'EQU', 'REV', 'EXP')
GROUP BY COA_T.AccCode,COA_T.AccName ORDER BY COA_T.AccCode
END