Sales Invoice child table error. This error is the same as in Sales Order, Purchase Invoice, and Purchase Order.

[shyamrathod12]

Information about bug

HINT: Use separate LIMIT and OFFSET clauses. Possible source of error: erpnext (app)

Module

accounts

Version

ERPNext: v15.34.0 (version-15)

Frappe Framework: v15.40.0 (version-15)

Installation method

manual install

Relevant log output / Stack trace / Full Error Message.

### App Versions

{
    "erpnext": "15.34.0",
    "frappe": "15.40.0"
}

Route

Form/Sales Invoice/new-sales-invoice-wlcuhqfffi

Traceback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 114, in application
    response = frappe.api.handle(request)
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1775, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
  File "apps/frappe/frappe/utils/goal.py", line 97, in get_monthly_goal_graph_data
    month_to_value_dict = get_monthly_results(
  File "apps/frappe/frappe/utils/goal.py", line 37, in get_monthly_results
    .run()
  File "apps/frappe/frappe/query_builder/utils.py", line 87, in execute_query
    result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
  File "apps/frappe/frappe/database/postgres/database.py", line 218, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 227, in sql
    self._cursor.execute(query, values)
psycopg2.errors.AmbiguousFunction: function sum(unknown) is not unique
LINE 1: ...CT TO_CHAR("posting_date",'MM-YYYY') "month_year",sum('base_...
                                                             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Request Data

{
    "type": "GET",
    "args": {
        "doctype": "Company",
        "docname": "Satya Tech",
        "title": "Sales",
        "goal_value_field": "monthly_sales_target",
        "goal_total_field": "total_monthly_sales",
        "goal_history_field": "sales_monthly_history",
        "goal_doctype": "Sales Invoice",
        "goal_doctype_link": "company",
        "goal_field": "base_grand_total",
        "date_field": "posting_date",
        "filters": "{\"docstatus\":1,\"is_opening\":[\"!=\",\"Yes\"]}",
        "aggregation": "sum"
    },
    "headers": {},
    "error_handlers": {},
    "url": "/api/method/frappe.utils.goal.get_monthly_goal_graph_data",
    "request_id": null
}

Response Data

{
    "exception": "",
    "exc_type": "AmbiguousFunction",
    "_debug_messages": "[\"Error in query:\\nfunction sum(unknown) is not unique\\nLINE 1: ...CT TO_CHAR(\\\"posting_date\\\",'MM-YYYY') \\\"month_year\\\",sum('base_...\\n                                                             ^\\nHINT:  Could not choose a best candidate function. You might need to add explicit type casts.\\n\"]"
}

[VishvParikh]

issue is resolved

[VishvParikh]

issue is resolved code is pushed in the limit_and_offset_isuue and pr number link: https://github.com/8848digital/erpnext/pull/12/files

[aasif-patel]

@shyamrathod12 the Traceback and Requested Data is not matching with the Error in SS, can you please provide the brief about the issue, so that it can be tested.

[aasif-patel]

Tested and Resolved hence closing..