Foxpro VS SQL Logic

Question

I couldn't think of a better title. If you have any suggestions, feel free to edit the title of my post.

I have a foxpro query that looks like this:

Sele
 a.plan_enddt as enddt, a.plrevno, a.keyfld, "PLAN    " as source ;
    from a_plan a inner join c_temp1a_pre b ;
    on a.keyfld=b.keyfld and a.plrevno=b.plrevno ;
    into cursor c_temp1a ;
    group by a.keyfld ;
    where a.plan_enddt>=date1 ;

What I am trying to do is translate from foxpro into T-SQL.

Wouldn't this foxpro statement fail because only 1 column is listed in the group by?

Edit:

I am not looking for a solution to fixing my foxpro statement

In TSQL, you can't just group by 1 column if you have multiple in the select statement, even if you aren't using an aggregate function.

Is this also true in Foxpro?

VFP allowed what you're showing through version 7, and added the restriction in version 8. — Tamar E. Granor, Jan 03 '19 at 21:23

Lukasz Szozda · Answer 1 · 2019-01-02T18:06:57.020

0

You could use:

Select MIN(a.plan_enddt) as enddt, MIN(a.plrevno), a.keyfld, 'PLAN    ' as [source]
from a_plan a 
inner join c_temp1a_pre b
  on a.keyfld=b.keyfld 
 and a.plrevno=b.plrevno 
where a.plan_enddt>=date1
group by a.keyfld ;

"I am not looking for a solution to fixing my foxpro statement"

This is the same case as between MySQL and PostgreSQL: Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns

T-SQL 2.1.2.221 T301, Functional dependencies

edited Jan 02 '19 at 18:06

answered Jan 02 '19 at 17:57

Lukasz Szozda

162,964
23
234
275

That is very similar to what I posted already. Wouldn't more columns be required in the group by? – Chicken Sandwich No Pickles Jan 02 '19 at 17:58
1

@LunchBox Yes, you have 2 options use AGG func on columns that are not in group by or add them to group by clause – Lukasz Szozda Jan 02 '19 at 17:59
You edited your post after I posted. Let me edit my original post – Chicken Sandwich No Pickles Jan 02 '19 at 17:59
Edited my original post. I'm not really looking for a way to fix my statement. I just wanted to know if it is possible because it's not possible in TSQL. – Chicken Sandwich No Pickles Jan 02 '19 at 18:00
1

That's what I was looking for. Bingo – Chicken Sandwich No Pickles Jan 02 '19 at 18:06
1

@LunchBox Not every RDBMS implements T301 feature – Lukasz Szozda Jan 02 '19 at 18:07

score 0 · Answer 2 · answered Jan 21 '19 at 06:53

It looks as bug in Foxpro (Visual Foxpro 8 and earlier) to accept such invalid group by clause. All fields listed in select that are not aggregate values should be also part of group by fields.

Like this:

select min(a.plan_enddt) as enddt, min(a.plrevno) as plrevno, a.keyfld, "PLAN " as source from a_plan a inner join c_temp1a_pre b on a.keyfld=b.keyfld and a.plrevno=b.plrevno into cursor c_temp1a group by a.keyfld where a.plan_enddt>=date1 ;

or this:

select a.plan_enddt as enddt, a.plrevno, a.keyfld, "PLAN " as source from a_plan a inner join c_temp1a_pre b on a.keyfld=b.keyfld and a.plrevno=b.plrevno into cursor c_temp1a group by a.keyfld, a.plan_enddt, a.plrevno where a.plan_enddt>=date1 ;

But in this case you can get more than one record, there is group by few fields.

It's not a bug, it just didn't follow the standard closely enough. — Alan B, Aug 26 '19 at 10:16

Foxpro VS SQL Logic

2 Answers2

Linked