search

andreasgerstmayr / fava-dashboards

Custom Dashboards for Beancount in Fava
MIT License
176 stars 21 forks source link

Query issue: no function matches "ymonth(date)" name and argument types #72

Closed pallavg closed 4 months ago

pallavg commented 4 months ago

I upgraded to latest fava-dashboards version. I'm seeing this error on my dashboard now:

error processing panel "Essentials MoM 🍔": failed to execute query SELECT YMONTH(date) AS date, CONVERT(SUM(position), 'USD') AS value WHERE account ~ '^Expenses:Food:Groceries': no function matches "ymonth(date)" name and argument types

I'm using beancount 2.3.6. If I run the exact same query on the query editor in fava I get a result. Any idea how to resolve this?

Not sure which package is the issue coming from.

andreasgerstmayr commented 4 months ago

I recently switched from beancount.query to beanquery, which is actively maintained and the successor of the BQL engine in beancount. See https://groups.google.com/g/beancount/c/iTdRuvZnE4E/m/o9V91WfgFQAJ for the deprecation notice.

beanquery is almost a drop-in replacement, but the function ymonth() you're using in your query got renamed to yearmonth(). Can you confirm that with this change the query works again?

pallavg commented 4 months ago

  1. That works OK now.

  2. But another query is failing but works OK on editor:

SELECT year, CONVERT(SUM(position), 'USD', LAST(date)) AS value WHERE account ~ "^Assets:US:Fidelity:401K:Cash" and 'XXX Payroll' in narration GROUP BY year: operator "in(str, str)" not supported

andreasgerstmayr commented 4 months ago

I've seen the IN operator only used to check if value X is in a set Y.

afaics the correct query in this case is

SELECT year, CONVERT(SUM(position), 'USD', LAST(date)) AS value WHERE account ~ "^Assets:US:Fidelity:401K:Cash" and narration ~ 'XXX Payroll' GROUP BY year
pallavg commented 4 months ago

  1. Thanks. I have fixed the query on your suggestion.

  2. Now everything is working normally. I will close this ticket.