JOINs and aggregate functions in Protheus
SELECT ... LEFT JOIN ... ON ... GROUP BY ...Joins tables and aggregates results while preserving branch context.
Overview
JOIN defines key matching; GROUP BY groups rows and COUNT/SUM calculate per-group values. A 1:N relationship can multiply rows and distort header totals. For LEFT JOIN, evaluate optional-table filters in ON when unmatched records must remain. This example builds a SQL string without executing it. Branch and logical-deletion filters apply independently to each table. The community reference illustrates JOIN and aggregation, but its general NOLOCK advice and direct writes are not adopted. (Sources: refs. 1–2.)
Syntax
SELECT ... LEFT JOIN ... ON ... GROUP BY ...Commented example
#Include "Protheus.ch"
User Function Ed500()
Local cSql := "SELECT A.A1_COD,A.A1_LOJA,COUNT(P.C5_NUM) AS QTD "
// Filter each table according to its sharing settings.
cSql += "FROM "+RetSqlName("SA1")+" A "
cSql += "LEFT JOIN "+RetSqlName("SC5")+" P ON "
cSql += "P.C5_CLIENTE=A.A1_COD AND P.C5_LOJACLI=A.A1_LOJA "
cSql += "AND P.C5_FILIAL='"+xFilial("SC5")+"' "
cSql += "AND P.D_E_L_E_T_=' ' "
cSql += "WHERE A.A1_FILIAL='"+xFilial("SA1")+"' "
cSql += "AND A.D_E_L_E_T_=' ' "
cSql += "GROUP BY A.A1_COD,A.A1_LOJA"
ConOut(cSql)
Return NilDisplays SQL counting orders by customer/store, including groups without orders; opens no cursor. Under review: editorial example neither compiled nor run in Protheus. Check includes, release, RDD and context before testing.
- Before execution, validate dictionary, permissions, cardinality, ChangeQuery, types and plan. For external values, use framework parameters rather than concatenation.
- Under review: editorial example neither compiled nor run in Protheus. Check includes, release, RDD and context before testing.
Comments
There are no approved comments yet.
Sign in with Google or Microsoft to comment.