0

I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads:

Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must be GROUPed or used in an aggregate function.

Here is the code:

SELECT substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) p2
    ,substr(work_item.wi_name, 1, 4) wi_name
    ,substr(pr_line_item.prac_no, 7, 16) prac_no
    ,moa_code
    ,resource_code
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0) COMMITTED
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0) obligated
    ,unoblig_us_bal_amt unoblig
    ,max(receive_org_code) receive
    ,substr(org_name, 1, 13) org_name
    ,fund_acct_reg_view.fund_acct_no fund_acct
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN') type
FROM pr_line_item
    ,work_item
    ,pr_amend
    ,wm_organization
    ,fund_acct_reg_view
WHERE ((substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) = '%153719%'))
    AND work_item.wi_code = pr_line_item.wi_code
    AND pr_line_item.prac_no = pr_amend.prac_no
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'OA%'
    AND (
        ABS(nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0)) > 0
        OR nvl(obli_excess_commit_amt, 0) > 0
        )
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'J169479'
    AND receive_org_code = org_code
    AND pr_line_item.fund_acct_no = fund_acct_reg_view.fund_acct_no
GROUP BY substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6)
    ,substr(pr_line_item.prac_no, 7, 16)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0)
    ,unoblig_us_bal_amt
    ,resource_code
    ,pr_line_item.fund_acct_no
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN')
    ,substr(org_name, 1, 13)
    ,substr(work_item.wi_name, 1, 4)
    ,moa_code
ORDER BY type
    ,fund_acct
    ,moa_code
    ,resource_code
    ,receive

After testing the code multiple times, I keep having the issued with either this field or another field. I am hoping that the issue it at the tail end of my code and someone may know a simple quick fix. I have spent almost all day trying to read up on this error, but I can't see how other solution apply to my situation.

1

You use fund_acct_reg_view.fund_acct_no fund_acct, in SELECT but use pr_line_item.fund_acct_no in GROUP BY. Use fund_acct_no from the same table in both cases.

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.