Incorrect syntax

Question

Incorrect syntax

nazirulfitrifauzi

SSC Veteran

Points: 277
More actions
October 4, 2016 at 2:02 am

#313607

SELECT 'KESELURUHAN' AS STATE,
'KESELURUHAN' AS BRANCH,
dbo.uf_get_koddana(h.funding_scheme) AS Dana,
Count(*) AS cnt_all,
Sum(h.approved_limit) AS sum_all,
Sum(isnull(w.wo_amt,0)) AS sum_wo,
Sum(isnull(RECOVERED_AMOUNT,0)) AS sum_wb,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then 1 else 0 END) AS cnt_npl,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(instal_arrears,0) - isnull(income_arrears,0)
Else 0 END) AS npl_costarr,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(income_arrears,0) else 0 END) AS npl_prftarr,
Sum(case when account_status = 9 then 1 else 0 END )as bil_wo
FROM dbo.account_history h,
dbo.Write_off_report w
WHERE ssma_oracle.trunc(h.report_date) = convert(DATETIME, '2016-06-30')
AND h.account_no = w.new_account (+)
AND h.account_status not in (2,16)
GROUP by dbo.uf_get_koddana(h.funding_scheme)
what im missing here? im getting error : Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The (+) you had in your code seems to suggest thta you're attempting to outer join using the old style join syntax that has been deprecated since forever. Change that to a proper outer join.

Things will be much clearer if you format your code. You can use an online formatter such as the one at poorsql.com

SELECT 'KESELURUHAN' AS STATE,

'KESELURUHAN' AS BRANCH,

dbo.uf_get_koddana(h.funding_scheme) AS Dana,

Count(*) AS cnt_all,

Sum(h.approved_limit) AS sum_all,

Sum(isnull(w.wo_amt,0)) AS sum_wo,

Sum(isnull(RECOVERED_AMOUNT,0)) AS sum_wb,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then 1 else 0 END) AS cnt_npl,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(instal_arrears,0) - isnull(income_arrears,0)

Else 0 END) AS npl_costarr,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(income_arrears,0) else 0 END) AS npl_prftarr,

Sum(case when account_status = 9 then 1 else 0 END )as bil_wo

FROM dbo.account_history h

LEFT JOIN dbo.Write_off_report w

ON h.account_no = w.new_account --(+) <-- was this meant to be an outer join?

WHERE ssma_oracle.trunc(h.report_date) = convert(DATETIME, '2016-06-30')

AND h.account_status not in (2,16)

GROUP by dbo.uf_get_koddana(h.funding_scheme)

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

nazirulfitrifauzi (10/4/2016)
SELECT 'KESELURUHAN' AS STATE,
'KESELURUHAN' AS BRANCH,
dbo.uf_get_koddana(h.funding_scheme) AS Dana,
Count(*) AS cnt_all,
Sum(h.approved_limit) AS sum_all,
Sum(isnull(w.wo_amt,0)) AS sum_wo,
Sum(isnull(RECOVERED_AMOUNT,0)) AS sum_wb,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then 1 else 0 END) AS cnt_npl,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(instal_arrears,0) - isnull(income_arrears,0)
Else 0 END) AS npl_costarr,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(income_arrears,0) else 0 END) AS npl_prftarr,
Sum(case when account_status = 9 then 1 else 0 END )as bil_wo
FROM dbo.account_history h,
dbo.Write_off_report w
WHERE ssma_oracle.trunc(h.report_date) = convert(DATETIME, '2016-06-30')
AND h.account_no = w.new_account (+)
AND h.account_status not in (2,16)
GROUP by dbo.uf_get_koddana(h.funding_scheme)
what im missing here? im getting error : Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

Convert your Oracle query to TSQL dialect. In addition to w.new_account (+) pointed out by SpaghettiDBA you have ssma_oracle.trunc(h.report_date).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazirulfitrifauzi SSC Veteran Points: 277 More actions · Answer 3

I got an error : Msg 313, Level 16, State 2, Line 1

An insufficient number of arguments were supplied for the procedure or function ssma_oracle.trunc.

nazirulfitrifauzi SSC Veteran Points: 277 More actions · Answer 4

ChrisM@Work (10/4/2016)
nazirulfitrifauzi (10/4/2016)
SELECT 'KESELURUHAN' AS STATE,
'KESELURUHAN' AS BRANCH,
dbo.uf_get_koddana(h.funding_scheme) AS Dana,
Count(*) AS cnt_all,
Sum(h.approved_limit) AS sum_all,
Sum(isnull(w.wo_amt,0)) AS sum_wo,
Sum(isnull(RECOVERED_AMOUNT,0)) AS sum_wb,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then 1 else 0 END) AS cnt_npl,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(instal_arrears,0) - isnull(income_arrears,0)
Else 0 END) AS npl_costarr,
Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(income_arrears,0) else 0 END) AS npl_prftarr,
Sum(case when account_status = 9 then 1 else 0 END )as bil_wo
FROM dbo.account_history h,
dbo.Write_off_report w
WHERE ssma_oracle.trunc(h.report_date) = convert(DATETIME, '2016-06-30')
AND h.account_no = w.new_account (+)
AND h.account_status not in (2,16)
GROUP by dbo.uf_get_koddana(h.funding_scheme)
what im missing here? im getting error : Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.
Convert your Oracle query to TSQL dialect. In addition to w.new_account (+) pointed out by SpaghettiDBA you have ssma_oracle.trunc(h.report_date).

How can i convert to TSQL? any tools or online converter.. I use yhis one before : http://www.sqlines.com/home

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

You SQL has already been converted to SQL Server: the ssma_oracle.trunc function seems to indicate that you used the SQL Server Migration Assistant (SSMA) for Oracle.

The function is erroring out because you didn't provide all needed parameters. Look it up and see which parameters it accepts

-- Gianluca Sartori

nazirulfitrifauzi SSC Veteran Points: 277 More actions · Answer 6

SELECT 2016 AS Tahun
,'Jun' AS bulan
,dbo.uf_get_koddana(BYFUND.DANA) AS DANA
,SUM(ISNULL(DISB.DISBAMT, 0)) AS JUMLAHPINJAMAN
,SUM(ISNULL(DISB.CAJ, 0)) AS CAJ
,sum(BYFUND.cost_amt) AS ByrPokok
,sum(BYFUND.profit_amt) AS ByrCaj
,sum(BYFUND.savings_amt) AS ByrSimpanan
,sum(BYFUND.resit_amount) AS JumKutip
,sum(isnull(ADJ.cost_amt, 0)) AS laraspokok
,sum(isnull(ADJ.profit_amt, 0)) AS larascaj
,sum(isnull(ADJ.txn_amt, 0)) AS laras
,sum(BYFUND.cost_amt) + sum(isnull(ADJ.cost_amt, 0)) AS jumPokok
,sum(BYFUND.profit_amt) + sum(isnull(ADJ.profit_amt, 0)) AS jumCaj
,sum(CASE
WHEN BYFUND.savings_amt - isnull(ADJ.txn_amt, 0) > 0
THEN BYFUND.savings_amt - isnull(ADJ.txn_amt, 0)
ELSE 0
END) AS jumSimpanan
,sum(isnull(RSAVG.APPRV_AMT, 0)) AS RedeemSavings
,sum(BYFUND.WriteBack) AS WriteBack
,sum(BYFUND.BIL) AS Bil
FROM (
SELECT H.funding_scheme AS dana
,sum(COLL_COST_MTH) AS cost_amt
,sum(COLL_PRFT_MTH) AS profit_amt
,sum(COLL_THIS_MTH - (COLL_COST_MTH + COLL_PRFT_MTH)) AS savings_amt
,sum(COLL_THIS_MTH) AS resit_amount
,SUM(CASE H.account_status
WHEN 9
THEN ISNULL(COLL_THIS_MTH, 0)
ELSE 0
END) AS WriteBack
,SUM(CASE
WHEN COLL_THIS_MTH > 0
THEN 1
ELSE 0
END) AS BIL
FROM ACCOUNT_HISTORY H
WHERE H.report_date = '2016-06-30'
AND H.account_status NOT IN (
2
,6
,16
)
GROUP BY H.funding_scheme
) BYFUND
,(
SELECT DANA
,SUM(DISBAMT) AS DISBAMT
,SUM(CAJ) AS CAJ
FROM (
SELECT H.funding_scheme AS DANA
,SUM(AMT_DISBURSE) AS DISBAMT
,SUM(H.TOT_PROFIT_UNEARNED) AS CAJ
FROM disbursement_request D
,ACCOUNT_HISTORY H
WHERE D.ACCOUNT_NO = H.ACCOUNT_NO
AND H.report_date = '2016-06-30'
AND dbo.uf_monthdiff(cheque_date, H.REPORT_DATE) = 0
AND disb_status NOT IN (
'X'
,'R'
)
AND disburse_mode = 'CH'
AND H.account_status NOT IN (
2
,6
,16
)
GROUP BY H.funding_scheme
UNION
SELECT H.funding_scheme
,SUM(AMT_DISBURSE)
,SUM(H.TOT_PROFIT_UNEARNED)
FROM disbursement_request D
,ACCOUNT_HISTORY H
WHERE D.ACCOUNT_NO = H.ACCOUNT_NO
AND H.report_date = '2016-06-30'
AND dbo.uf_monthdiff(DISBURSE_DATE, H.REPORT_DATE) = 0
AND disb_status NOT IN (
'X'
,'R'
)
AND disburse_mode = 'AD'
AND H.account_status NOT IN (
2
,6
,16
)
GROUP BY H.funding_scheme
)
GROUP BY DANA
) DISB
,(
SELECT H.funding_scheme AS DANA
,SUM(txn_amt) AS TXN_AMT
,SUM(cost_amt) AS COST_AMT
,SUM(profit_amt) AS PROFIT_AMT
FROM loan_journal L
,ACCOUNT_HISTORY H
WHERE L.ACCOUNT_NO = H.ACCOUNT_NO
AND trunc(H.report_date) = convert(DATETIME, '30 Jun 2016')
AND H.account_status NOT IN (
2
,6
,16
)
AND uf_monthdiff(txn_date, REPORT_DATE) = 0
AND txn_code = '3830'
AND upper(doc_no) = 'SISTEM'
AND reversal_flag <> 'Y'
GROUP BY H.funding_scheme
) ADJ
,(
SELECT H.funding_scheme AS DANA
,SUM(approved_amt) AS APPRV_AMT
FROM savings_withdrawal_app S
,ACCOUNT_HISTORY H
WHERE S.ACCOUNT_NO = H.ACCOUNT_NO
AND trunc(H.report_date) = convert(DATETIME, '30 Jun 2016')
AND H.account_status NOT IN (
2
,6
,16
)
AND uf_monthdiff(cheque_date, REPORT_DATE) = 0
AND STATUS = 'A'
GROUP BY H.funding_scheme
) RSAVG
WHERE BYFUND.DANA = DISB.DANA(+)
AND BYFUND.DANA = ADJ.DANA(+)
AND BYFUND.DANA = RSAVG.DANA(+)
AND substring(b.BRANCH_CODE, 1, 2) = '01'
AND b.BRANCH_CODE = '0101'
GROUP BY uf_get_koddana(BYFUND.DANA)

i got another error : Msg 156, Level 15, State 1, Line 95

Incorrect syntax near the keyword 'GROUP'.

sorry im a newbie :crying: