ORA-00936: missing expression | ASP .NET CORE 3.1 MVC | WITH FILES AS | NPoco

Question

I have a problem with my function throwing an exception "ORA-00936: missing expression" yet the query works on Oracle SQL Developer...

Does anyone know where this could come from?

public List<(string, string, string, string)> GetSomething(string idWorkflow)
{
    var nb = new List<(string, string, string, string)>();

    if (!string.IsNullOrEmpty(idWorkflow))
    {
        try
        {
            List<(string, string, string, string)> result = _core.Query<dynamic>(
                $"WITH FILES AS " +
                $"(SELECT m.col1, m.col2 FROM TABLE1 m WHERE m.col5=@0 AND EXISTS(SELECT 1 FROM TABLE3 s WHERE s.col5 = m.col5 AND m.col6 = s.col8) AND m.col7 IS NULL) " +
                $"SELECT " +
                $"f.col1, " +
                $"f.col2, " +
                $"(SELECT col9 FROM TABLE2 h, TABLE4 d WHERE h.col1 = d.col10 AND d.col11 = f.col1 AND col12 = 3 AND d.col13 = 'INV' AND ROWNUM = 1) col3, " +
                $"(SELECT col9 FROM TABLE2 h, TABLE4 d WHERE h.col1 = d.col10 AND d.col11 = f.col1 AND col12 in (5, 6) AND d.col13 = 'INV' AND ROWNUM = 1) col4 " +
                $"FROM FILES f"
                ,
                idWorkflow
                ).Select(x => ((string)x.col1, (string)x.col2, (string)x.col3, (string)x.col4)).ToList();
            return result;
        }
        catch (Exception e)
        {
            var test = e.Message;
            return nb;
        }
    }

    return nb;
}

Answer 1

Here is how I solved my problem :

public List<(string, string, string, string)> GetSomething(string idWorkflow)
{
    var nb = new List<(string, string, string, string)>();
    var param = new { workflow = idWorkflow };
    var query = @";WITH FILES AS (
              SELECT m.col1, m.col2 FROM TABLE1 m WHERE m.col5=@workflow AND EXISTS(SELECT 1 FROM TABLE3 s WHERE s.col5 = m.col5 AND m.col6 = s.col8) AND m.col7 IS NULL)
                SELECT
                f.col1,
                f.col2,
                (SELECT col9 FROM TABLE2 h, TABLE4 d WHERE h.col1 = d.col10 AND d.col11 = f.col1 AND col12 = 3 AND d.col13 = 'INV' AND ROWNUM = 1) col3,
                (SELECT col9 FROM TABLE2 h, TABLE4 d WHERE h.col1 = d.col10 AND d.col11 = f.col1 AND col12 in (5, 6) AND d.col13 = 'INV' AND ROWNUM = 1) col4
                FROM FILES f";
    if (!string.IsNullOrEmpty(idWorkflow))
    {
        try
        {
            List<(string, string, string, string)> result = _core.Query<dynamic>(query, param).Select(x => ((string)x.col1, (string)x.col2, (string)x.col3, (string)x.col4)).ToList();
            return result;
        }
        catch (Exception e)
        {
            var test = e.Message;
            return nb;
        }
    }
    return nb;
}