October 5, 2016 at 9:04 pm
SELECT DANA,
SUM(DISBAMT) AS DISBAMT,
SUM(CAJ) AS CAJ
FROM (
SELECTH.funding_scheme AS DANA,
SUM(AMT_DISBURSE) AS DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) AS CAJ
FROMdbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERED.ACCOUNT_NO= H.ACCOUNT_NO
ANDH.report_date = '30 Jun 2016'
ANDdbo.uf_monthdiff(cheque_date, H.REPORT_DATE) = 0 AND disb_status not in ('X','R')
ANDdisburse_mode = 'CH'
ANDH.account_status not in (2,6,16)
GROUP BY H.funding_scheme
UNION
SELECTH.funding_scheme as DANA,
SUM(AMT_DISBURSE) as DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) as CAJ
FROM dbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERE D.ACCOUNT_NO= H.ACCOUNT_NO
AND H.report_date = '30 Jun 2016'
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
I dont know whats wrong with my code.. i check using online checker and nothing come out.. but when i run the query in mssql, i got error : Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'GROUP'.
when i run in oracle, it works fine..
October 6, 2016 at 4:42 am
You are getting the error because you have not given the Alias to the Query Table. I gave it Alias and it got successfully compiled.
Please find the modified query for your reference.
Have a good day!
SELECT DANA,
SUM(DISBAMT) AS DISBAMT,
SUM(CAJ) AS CAJ
FROM
(
SELECTH.funding_scheme AS DANA,
SUM(AMT_DISBURSE) AS DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) AS CAJ
FROMdbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERED.ACCOUNT_NO= H.ACCOUNT_NO
ANDH.report_date = '30 Jun 2016'
ANDdbo.uf_monthdiff(cheque_date, H.REPORT_DATE) = 0 AND disb_status not in ('X','R')
ANDdisburse_mode = 'CH'
ANDH.account_status not in (2,6,16)
GROUP BY H.funding_scheme
UNION
SELECTH.funding_scheme as DANA,
SUM(AMT_DISBURSE) as DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) as CAJ
FROMdbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERED.ACCOUNT_NO= H.ACCOUNT_NO
ANDH.report_date = '30 Jun 2016'
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
) A
GROUP BY DANA
October 6, 2016 at 6:35 am
Be careful, you might want to use UNION ALL to prevent that duplicate rows are eliminated (in case that the aggregations return the same value for the funding scheme).
You also want to remove that user defined function from your where clauses, it will only slow you down. At least, change it to a system function.
October 6, 2016 at 8:59 pm
Brahmanand Shukla (10/6/2016)
You are getting the error because you have not given the Alias to the Query Table. I gave it Alias and it got successfully compiled.Please find the modified query for your reference.
Have a good day!
SELECT DANA,
SUM(DISBAMT) AS DISBAMT,
SUM(CAJ) AS CAJ
FROM
(
SELECTH.funding_scheme AS DANA,
SUM(AMT_DISBURSE) AS DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) AS CAJ
FROMdbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERED.ACCOUNT_NO= H.ACCOUNT_NO
ANDH.report_date = '30 Jun 2016'
ANDdbo.uf_monthdiff(cheque_date, H.REPORT_DATE) = 0 AND disb_status not in ('X','R')
ANDdisburse_mode = 'CH'
ANDH.account_status not in (2,6,16)
GROUP BY H.funding_scheme
UNION
SELECTH.funding_scheme as DANA,
SUM(AMT_DISBURSE) as DISBAMT,
SUM(H.TOT_PROFIT_UNEARNED) as CAJ
FROMdbo.disbursement_request D, dbo.ACCOUNT_HISTORY H
WHERED.ACCOUNT_NO= H.ACCOUNT_NO
ANDH.report_date = '30 Jun 2016'
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
) A
GROUP BY DANA
Thanks mate... you just save my day!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply