Missing clause group by DBF FoxPro

Question

I have this query in C# and I have tried to add group by for all non-aggregate values but it does not function.

I have 3 tables:

First table I have assemblyno, part_no, rout_no (unique), wo_no, etc...
On the second I have wo_no (same from table 1) and job_no, this is the "link" table between 1 and 3
And third table the job_no (same as second table) with order_val

Have to extract for a given rout_no in 1st table the max(order_val) on the third table using the second table that will link wo_no and job_no...
I hope is more clear.

Error:

Additional information: SQL: GROUP BY clause is missing or invalid.

        sql = string.Format(@" SELECT t1.assemblyno, t1.level, t1.wo_no, t1.rout_no, t1.due_date, t1.printed, t1.rev_no, t2.lot, t2.po, t2.qty_due, t3.comment, t3.jobno, MAX(t3.order_val), t3.part_no,t3.po,t3.price, t3.qty_order, t3.quote_no, t3.rev_no 
    FROM ('{0}') t1 
    LEFT JOIN ('{1}') t2 
    on t1.wo_no = 2.wo_no 
    LEFT JOIN ('{2}') t3 
    on t2.jobno = t3.jobno 
    GROUP BY  t1.assemblyno, t1.level, t1.wo_no, t1.rout_no, t1.due_date, t1.printed, t1.rev_no, t2.lot, t2.po, t2.qty_due, t3.comment, t3.jobno,t3.part_no,t3.po,t3.price, t3.qty_order, t3.quote_no, t3.rev_no 
WHERE t1.rout_no=" + "\'" + rNum + "\'" + ";", databaseTable_WOROUTH, databaseTable_WOJOBS, databaseTable_SOMAST);

Your query does not contain a group by clause. Please show your attempt including the group by clause. And may I ask why you are using a FoxPro DBF? FoxPro is essentially dead technology, and if you can, you should avoid it. — Mark Rotteveel
Indeed is a dead technologies but where I work is a must. I have posted with group statement — busymind
It is not a good way of writing queries in C# (be it for Foxpro or any other backend - makes the query unreadable if nothing else like SQL injection). The problem has nothing to do with the backend being Foxpro, it would be the case with any backend. If you are using aggregation you need "group by". In your case however just listing all non-aggregate would likely simply return excess information that you don't want. Please share the structures, plus what you really need. For example jobno field, is that unique in T3? If not, given a job how do determine the unique id for given order_val? — Cetin Basoz
I now see your edited query and it likely simply would return the information that you don't want (you are asking for the max(order_val) where all other columns are distinct). — Cetin Basoz
I learn new things but I have to fix old things - that are new for moment :) — busymind

Cetin Basoz Cetin Basoz · Accepted Answer · 2018-05-09T14:17:45

Looks like you are not going to share your structures and more info. Based on only what we have, probably this is what you are looking for:

    sql = string.Format(@" SELECT 
        t1.assemblyno, t1.level, t1.wo_no, t1.rout_no, 
        t1.due_date, t1.printed, t1.rev_no, t2.lot, t2.po, t2.qty_due, 
        t3.comment, t3.jobno, MAX(t3.order_val), t3.part_no,t3.po,t3.price, t3.qty_order,
        t3.quote_no, t3.rev_no 
        FROM ('{0}') t1 
        LEFT JOIN ('{1}') t2  on t1.wo_no = 2.wo_no 
        LEFT JOIN (
    select * from ('{2}') tmp1
    inner join  
    (select jobNo, max(order_val) as order_val from ('{2}') group by jobNo) tmp2
    on tmp1.JobNo = tmp2.JobNo and tmp1.Order_Val=tmp2.Order_val
        ) t3 
        on t2.jobno = t3.jobno 
    WHERE t1.rout_no=?", databaseTable_WOROUTH, databaseTable_WOJOBS, databaseTable_SOMAST);

//cmd.Parameters.Add("@rnum", OleDbType.Char).Value = rnum;
//...

// Thanks for BASOZ idea and help

var sql = string.Format(@"
select top 1 
    t1.assemblyno, t1.level, t1.wo_no, t1.rout_no, t1.due_date, t1.printed, t1.rev_no, t2.lot, t2.po, t2.qty_due, 
    t3.comment, t3.jobno, t3.order_val, t3.part_no,t3.po,t3.price, t3.qty_order,t3.quote_no, t3.rev_no 
from {2} t3
    inner join {1} t2 on t3.jobno = t2.jobno
    inner join {0} t1 on t1.wo_no = t2.wo_no 
    where t1.rout_no=?
        and t3.order_val in (
        SELECT max(tt3.order_val) 
                            FROM {0} tt1 
                            left join {1} tt2 
                                on tt1.wo_no = tt2.wo_no
                            left join {2} tt3 
                                on tt2.jobno = tt3.jobno
                            where tt1.rout_no = ?
                            group by tt1.rout_no
        )
        {3} {4}
        order by t3.jobno desc ", "t1", "t2", "t3", startCheck, endCheck);

Missing clause group by DBF FoxPro

2 Answers