No function clause matching in Ecto.Query.join/5

def dashboard_params() do

"vw_dashboard_params"
|> join(
:right,
[c],

day in fragment("""
SELECT CAST(DATEADD(DAY, nbr - 1, DATEADD(month, DATEDIFF(month, 0, CAST(CURRENT_TIMESTAMP AS DATETIME)), 0)) AS DATE) d
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY c.object_id) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, DATEADD(month, DATEDIFF(month, 0, CAST(CURRENT_TIMESTAMP AS DATETIME)), 0), EOMONTH(CAST(CURRENT_TIMESTAMP AS DATETIME)))
"""),
day.d == fragment("CAST(? AS DATE)", c.inserted_at)
)

|> group_by([c, day], [day.d,  c.status])
|> order_by([_c, day], day.d)
|> select([c, day], %{
day: fragment("convert(varchar, ?, 107)", day.d),
count: count(c.id),
status:  c.status
})
|> Repo.all()

end

The final argument of join/5 needs to be a keyword list, not an expression:

|> join(:right, [c], day in ..., on: day.d == fragment(...))
1 Like

after some research i found in ecto 3 that’s not how to run that query there some changes.
thanks for the help