I have problem on script to get minimum/maximum and to copy data into another table 1

[keisha1]

Im using visual foxpro9 and I have problem on my script to get minimum & maximum and to copy the data into another table using query command INTO TABLE. can help me to have complete commands for this.

your reply is most highly appreciate. Thanks


See my script and error displayed.

SELECT MIN(Journaldb.seq_num), MAX(Journaldb.seq_num),;
Journaldb.terminal, Journaldb.termi_name, Journaldb.type_,;
Journaldb.resp_code;
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY Journaldb.terminal;
ORDER BY Journaldb.terminal;
INTO TABLE jominmax.dbf


ERROR DISPLAYED in visual foxpro9 upon execute.

SQL: GROUP BY close is missing on invalid

 

[mjcmkrsr]

hi,

SELECT MIN(Journaldb.seq_num), MAX(Journaldb.seq_num),;
Journaldb.terminal, Journaldb.termi_name, Journaldb.type_,;
Journaldb.resp_code;
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY Journaldb.terminal;
ORDER BY Journaldb.terminal;
INTO TABLE jominmax.dbf

You have to GROUP BY all non-aggregated fields/expressions (see code below)

From the Help File:

The GROUP BY clause specifies one or more columns used to group rows returned by the query. Columns referenced in the SQL SELECT statement list, except for aggregate expressions, must be included in the GROUP BY clause. You cannot group by Memo, General, or Blob fields.

SELECT MIN(Journaldb.seq_num), MAX(Journaldb.seq_num),;
Journaldb.terminal, Journaldb.termi_name, Journaldb.type_,;
Journaldb.resp_code;
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY 3, 4, 5, 6 ;
ORDER BY 3 ;
INTO TABLE jominmax.dbf

hth
MarK

 

[Mike Lewis]

The reason for the error is that, when you are using grouping, all the fields in the SELECT list must be either aggregate functions - such as your MIN() and MAX() - or they must be fields that are listed in the GROUP BY clause. You are trying to select three fields - termi_name, type_ and resp_code - that do not meet this requirement.

To solve the problem, either leave those fields out of the SELECT list, or add them to the GROUP BY.

Another way of solving the problem is to execute SET ENGINEBEHAVIOR 70. That will cause the above rule to be relaxed. But it is generally considered undesriable as it could lead to misleading results.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes. Here's the first sentence from the help topic about the GROUP BY clause:

The GROUP BY clause specifies one or more columns used to group rows returned by the query. Columns referenced in the SQL SELECT statement list, except for aggregate expressions, must be included in the GROUP BY clause.

Your GROUP BY clause only specifies grouping by Journaldb.terminal, a single field. That's the only field you may specify in your SELECT field list as an unaggregated value.

Keep in mind, or finally learn, what GROUP BY means: The goal is to have one and exactly one record per group. The groups are defined by the value or tuple of values the columns have, that you specify in the GROUP BY clause. So, those columns can be interpreted as the head data of a group, data that by definition is the same in all the records of the group before the grouping aggregation, the group by happens.

But that can't be said for any other columns, so depending on your point of view, you have to add them to the GROUP BY clause to make that valid, or you have to remove them from the SELECT field list to make the GROUP BY clause valid.

Nobody can tell you what you want. If you group by all these fields, unless they all are the same value for the same Journaldb.terminal, you get more groups, more specialized groups. In the extreme case of specifying all columns of a record, each record becomes its own group and grouping becomes pointless.

In cases, none of the two alternatives of putting fewer fields in the SELECT field list nor adding more fields to the GROUP BY field list reaches your goal, that type of query is not solving your problem, not in one step.

I see you want summary information of all the journals coming from the same terminal. Well, if there are several types in Journaldb.type_ that's beyond what you can get in one single record per terminal. Also in a SQL query result the type_ column can only store one value. GROUP BY won't list all Journaldb.type_ values for the same Journaldb.terminal.

You need a separate query for that aspect. If you are still interested in the MIN(Journaldb.seq_num), MAX(Journaldb.seq_num), that is the range of sequence numbers of journal rows from the whole terminal and not just from the journal rows of a certain type, this can't be answered in a single query.

Here are the two queries for just these aspects as a starter:

SELECT Journaldb.terminal, Journaldb.termi_name, MIN(Journaldb.seq_num), MAX(Journaldb.seq_num);
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY Journaldb.terminal,  Journaldb.termi_name;
ORDER BY Journaldb.terminal,  Journaldb.termi_name;
INTO CURSOR journalterminalsequencenumberranges

SELECT Journaldb.terminal, Journaldb.termi_name, Journaldb.type_;
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY Journaldb.terminal,  Journaldb.termi_name, Journaldb.type_;
ORDER BY Journaldb.terminal,  Journaldb.termi_name, Journaldb.type_;
INTO CURSOR journalterminaltypes

The ranges you get from the first query üer terminal will cover all journal types. You can add MIN(Journaldb.seq_num), MAX(Journaldb.seq_num) to the field list of the second query, but when there are multiple types, only the minimum of all minimum values and the maximum of all maximum values will be the values you get from the first query. The rows for a specific type are obviously a subgroup. And that's the core reason you can't get that, a GROUP BY query is not able to give you nested hierarchical data. It is determined to give you one record per group. Other ways of grouping are partitioning queries and VFP SQL does not cover that.

If the overall journal is feed by all terminals chronologically, these ranges will not really give you the portion of records only about one specific terminal, the ranges can nest and overlap within each other unless each terminal has its own sequence number counter.

Last, not least: In earlier VFP versions you could group that way and the SQL engine would then give you the values of the first row of a group, but that means if you did that in the past you dropped a lot of detail information. Eg you got Journaldb.type_ even when there are multiple ones in all records of the same Journaldb.terminal. VFP9 made this strict and thereby actually fixed a bug of the VFP SQL language. If that code comes from an old project done in previous VFP versions, you've now come to a point to fix something, which likely will bring out really correct data summaries for the first time. So there you have a third vote against solving that by going back to legacy behavior. If it's new code, then anyway, learn how to really use that tool of GROUP BY.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Mike,

Very sorry but your "Another way of solving the problem is to execute SET ENGINEBEHAVIOR 70" is a show-stopper.
This means literally: Select anything, I dont care what the result will be.
Your advise to "GROUP BY all non-aggregated fields/expressions" is the one and only correct advise.

Regards,
Koen

(Hope you don't feel disappointed) )

 

[Mike Lewis]

(Hope you don't feel disappointed)

Of course not. You are absolutely correct, Koen. It's why I said "it is generally considered undesriable". "Show-stopper" would have been a stronger way of putting it. The point was that I was trying to discourage the OP from doing that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[keisha1]

HI Sir Mike/ Koen,

Thanks to your important reply. appreciated.

But I have a little favor from you for what I need is data records should be 1 record per terminal only.
the output encountered upon executed my script with group by fields were incorrect output, because minimum/maximum should be one record per row/terminal and also terminal must be 1 column only. see output generated. Please give me suggestion. Thanks

Script:

SELECT MIN(Journaldb.seq_num),MAX(Journaldb.seq_num),Journaldb.terminal,Journaldb.terminal,Journaldb.termi_name,Journaldb.type_,Journaldb.resp_code;
FROM journaldb;
WHERE Journaldb.type_ = "Cash ";
GROUP BY Journaldb.terminal,Journaldb.termi_name,Journaldb.type_,Journaldb.resp_code;
ORDER BY Journaldb.terminal;
INTO TABLE jominmax.dbf


Incorrect output displayed
727727 728124 00000000 00000000 PLAZA B Cash 000
727949 728047 00000000 00000000 PLAZA B Cash 005
727989 727989 00000000 00000000 PLAZA B Cash 012
727935 727935 00000000 00000000 PLAZA B Cash 024
727806 728109 00000000 00000000 PLAZA B Cash 051
835844 836132 00000001 00000001 MALL 1 Cash 000
835925 836110 00000001 00000001 MALL 1 Cash 005
835839 835853 00000001 00000001 MALL 1 Cash 012
835895 836102 00000001 00000001 MALL 1 Cash 024
836124 836124 00000001 00000001 MALL 1 Cash 046
835863 836038 00000001 00000001 MALL 1 Cash 051
835838 836101 00000001 00000001 MALL 1 Cash 055
569572 569626 00000002 00000002 MOB Cash 000
569597 569597 00000002 00000002 MOB Cash 025

 

[keisha1]

Already okay Sirs,
Please disregard my 2nd messages. I tried omitted the two field in the group by Journaldb.type_,Journaldb.resp_code; the output successfully correct. Thanks guys your advice & sample are most highly appreciated. Thanks thanks...