Recordset Jeráquicos

ADO-Recordset Jeráquicos

Introducción

Se pueden utilizar conjuntos de registros jerárquicos como alternativa a la sintaxis de JOIN y GROUP BY cuando necesite tener acceso a información de resumen y de elementos primarios y secundarios.

Los conjuntos de registros jerárquicos se utilizan en muchos productos: los productos XBASE usan el comando SET RELATION, Access usa "tablas virtuales segmentadas" internamente en los informes con niveles de agrupación, y así sucesivamente. Las jerarquías permiten crear uno o varios conjuntos de registros, definir agrupamientos y especificar cálculos totales sobre conjuntos de registros secundarios. Aunque puede implementar una funcionalidad similar mediante código, de esta manera desplaza la mayor parte del trabajo rutinario del desarrollador al sistema.

Los conjuntos de registros jerárquicos están disponibles a través del proveedor de MSDataShape, que implementa el motor de cursor del cliente.

Los conjuntos de registros jerárquicos se diferencian de las instrucciones SQL JOIN y GROUP BY en que con un JOIN, tanto los campos de tabla primarios como los secundarios se representan en el mismo conjunto de registros. Mediante una jerarquía, el conjunto de registros contiene sólo campos de la tabla primaria. Además, contiene un campo extra que representa los datos secundarios relacionados, que puede asignar a un segundo conjunto de registros variable y que se puede recorrer.

Cuando realiza funciones agregadas mediante GROUP BY y los operadores agregados, sólo aparecen los valores agregados en el conjunto de registros.

En los conjuntos de registros jerárquicos, los valores agregados se representan en el conjunto de registros primario y los registros detallados aparecen en el conjunto de registros secundario.

Puede crear tres tipos de instrucciones SHAPE, cada uno con sus ventajas e inconvenientes. Elija el que mejor se ajuste a las necesidades de la aplicación y el entorno en el que ésta se ejecuta. Son los siguientes:

Los dos primeros son similares en que producen una jerarquía que, de otra manera, se representaría mediante una instrucción SQL JOIN. Se diferencian en que todos los registros primarios y secundarios se leen en una caché local antes de que continúe cualquier proceso en la jerarquía basada en relaciones. Este tipo de jerarquía tiene una sobrecarga inicial alta al recuperar los registros, pero que disminuye tras la recuperación inicial.

Inicialmente, las jerarquías basadas en parámetros sólo leen los registros primarios y buscan los registros secundarios cuando se requiere. Aunque se reduce la sobrecarga inicial, debe realizar una nueva consulta secundaria para cada registro primario al que se tiene acceso y debe mantener la conexión al origen de datos durante el tiempo que esté abierto el conjunto de registros.

La jerarquía basada en grupos equivale a producir una instrucción SQL agregada junto a una instrucción SQL detallada o realizar funciones agregadas en datos sin normalizar. No puede actualizar las columnas resumidas ni las calculadas porque pueden provenir de más de un registro. Como en las jerarquías basadas en relaciones, todos los registros se deben leer en primer lugar.

La disponibilidad de los conjuntos de registros jerárquicos se consigue mediante la cláusula SHAPE. En primer lugar se proporciona la sintaxis simplificada y a continuación los ejemplos con diagramas. Como la sintaxis de SHAPE puede ser muy compleja, al final del artículo se incluye la parte más formal de la cláusula para que pueda extender los ejemplos. También puede utilizar el programa que se incluye al final del artículo para probar sus propias instrucciones SHAPE.

Comandos SHAPE en general

Un comando SHAPE define la estructura de un RECORDSET jerárquico y los comandos necesarios para poblarlo de datos.

Una parte del comando SHAPE es una consulta emitida al proveedor de datos subyacente que devuelve un objeto RECORDSET. La sintaxis de la consulta depende de los requisitos del proveedor de datos principal. Normalmente éste será SQL, aunque ADO no necesita el uso de ningún lenguaje de consulta en particular. Podría utilizar una cláusula JOIN de SQL para relacionar dos tablas, sin embargo, un Recordset jerárquico representará la información de forma más eficaz. Cada fila de un Recordset creado mediante un JOIN repite la información de forma redundante de una de las tablas. Un Recordset jerárquico sólo tiene un Recordset primario para cada uno de los objetos Recordset secundarios.

Proveedores necesarios

Un proveedor de origen de datos OLE debe proporcionar datos que se pasarán a otro proveedor, que llevará a cabo la formación de los datos. El proveedor de origen de datos se especifica en la cadena de conexión del objeto CONNECTION como "Shape Provider = OrigenDeDatos". El proveedor que suministra el soporte de formación de datos se especifica en la propiedad PROVIDER del objeto CONNECTION como "MSDataShape".

Resumen de cursores jerárquicos

ADO 2.0 presenta la función cursor jerárquico, que permite definir un objeto RECORDSET secundario como el valor de un campo en un RECORDSET principal.

Esta es una forma sencilla de ver los cursores jerárquicos. Imagine un control visual, como el cuadro de dialogo "Abrir Archivo", que muestra los archivos y subdirectorios de una forma jerárquica. Piense en cada directorio como un objeto RECORDSET, en cada archivo dentro de un directorio como un objeto FIELD y en cada subdirectorio dentro de un directorio como un objeto FIELD cuyo valor es otro RECORDSET.

ADO 2.0 presenta además una nueva sintaxis de lenguaje de manipulación de datos de tipo SHAPE, que permite realizar consultas que den como resultado un RECORDSET jerárquico. Un comando de lenguaje SHQPE se emite como se haría con cualquier cadena de comando ADO.

El lenguaje SHAPE está incorporado en ADO Client Cursor Engine (Motor de cursores clientes de ADO). El proceso de crearlo se denomina Formación de datos.

El lenguaje SHAPE permite crear objetos jerárquicos de dos formas. La primera agrega un RECORDSET de nivel inferior al RECORDSET de nivel superior y la segunda calcula una operación agregada a un RECORDSET secundario y genera un RECORDSET principal.

Se pueden anidar los objetos RECORDSET jerárquicos a cualquier profundidad que se requiera (esto es, crear objetos RECORDSET secundarios dentro de objetos RECORDSET secundarios, y así sucesivamente).

Se puede tener acceso al RECORDSET jerárquico resultante con un programa o mediante un control visual adecuado.

El lenguaje SHAPE es relativamente difícil de escribir. Por ello, Microsoft proporciona una herramienta visual que genera los comandos: "El diseñador de entorno de datos" y otra herramienta visual para mostrarlos: "El Flexgrid".

Comando APPEND en SHAPE

El comando APPEND de SHAPE asigna un RECORDSET secundario a la propiedad VALUE de objetos FIELD en un RECORDSET principal.

Sintaxis:

SHAPE {comando primario} [[AS] alias de tabla]
APPEND {comando secundario}
RELATE (columna del comando primario TO columna del comando secundario)

ParteDescripción
Comando Primario
Comando Secundario		Un comando de consulta que devuelve un objeto RECORDSET. El comando se emite al proveedor principal de datos y su sintaxis depende de los requisitos de ese proveedor. Normalmente éste será SQL, aunque ADO no necesita el uso de ningún lenguaje de consulta en particular.
Columna del comando primarioUna columna del RECORDSET devuelto por el comando primario.
Columna del comando secundarioUna columna del RECORDSET devuelto por el comando secundario
Alias de tablaUn alias utilizado para referirse al RECORDSET devuelto

El motor del cursor del cliente emitirá el comando primario al proveedor, que devolverá un RECORDSET principal. Posteriormente se emite el comando secundario, que devuelve un RECORDSET secundario.

Por ejemplo, el comando primario podría devolver en conjunto de clientes de una compañía de la base de datos de clientes y el secundario los perdidos que ha realizado cada uno de ellos.

Los objetos RECORDSET secundarios y primarios deben tener una columna en común. A las columnas se les asigna un nombre en la cláusula RELATE, primero la columna del primario y después la del secundario. Las columnas pueden tener nombres distintos en los respectos objetos RECORDSET, pero deben referirse a la misma información para especificar una relación con sentido.

El motor del cursor cliente crea internamente una nueva columna y la agrega literalmente al RECORDSET principal. Los valores de los campos en la nueva columna son referencias a las filas del RECORDSET secundario que satisfacen la cláusula RELATE.

Sintaxis Global Simplificada

SHAPE {instrucción primaria}
APPEND Aggregate
|({instrucción secundaria} [AS Alias]
RELATE campo primario
TO campo secundario | marcador de parámetros
[, campo primario TO
campo secundario | marcador de parámetros ...])
[, Aggregate | ({instrucción secundaria})...]
SHAPE {instrucción sin normalizar} [AS Alias]
COMPUTE Aggregate | Alias
| ({instrucción secundaria} [As Alias] RELATE
campo primario TO campo secundario | marcador de parámetros)
[, Aggregate | Alias | ({instrucción secundaria}...)]
[BY campo de agrupación [, campo de agrupación]]
SHAPE {instrucción sin normalizar} [AS Alias]
BY campo de agrupación [, campo de agrupación]

Notas:

  1. Si selecciona campos con idéntico nombre en tablas diferentes, puede que necesite crearles un alias para asegurarse de que el analizador de SHAPE funciona.
  2. SHAPE APPEND funciona de manera similar a OUTER JOIN (combinación de datos externa), en cuanto a que se devuelve un registro primario, incluso aunque éste no tenga registros secundarios.
  3. Los elementos agregados sólo pueden operar en campos del elemento secundario inmediato del conjunto de registros. Para operar en campos de elementos secundarios no inmediatos, debe producir elementos agregados intermedios. Consulte el ejemplo Jerarquía de grupos con elementos agregados al final de este artículo como referencia.
  4. Si utiliza una función agregada con la sintaxis SHAPE APPEND, el valor agregado ocupará un campo anexado al conjunto de resultados primario, que también contiene los campos de la instrucción primaria. Como contrapartida, SHAPE COMPUTE y SHAPE BY crean un nuevo nivel primario para los elementos agregados y la instrucción sin normalizar se convierte en el conjunto de registros secundario.
  5. El proveedor de SHAPE requiere que incluya un Alias para la instrucción sin normalizar en la cláusula COMPUTE cuando se utiliza SHAPE COMPUTE. Si no lo hace, aparecerá un mensaje que comunicará que no se admite la funcionalidad, aunque no haya un error de sintaxis.

Ejemplos

Jerarquía de relación simple:

Sintaxis:

SHAPE {SELECT * FROM customers}
APPEND ({SELECT * FROM orders} AS rsOrders
RELATE customerid TO customerid)

Se obtiene como resultado un conjunto de registros primario que contienen todos los campos de la tabla Clientes y un campo llamado rsOrders. rsOrders proporciona una referencia al conjunto de registros secundario y contiene todos los campos de la tabla Pedidos.

Jerarquía parametrizada:

Sintaxis:

SHAPE {SELECT * FROM customers}
APPEND ({SELECT * FROM orders WHERE customerid = ?} AS rsOrders
RELATE customerid TO PARAMETER 0)

Esto da como resultado la misma jerarquía que la jerarquía de relación simple.

Jerarquía de relación compuesta

Este ejemplo muestra una jerarquía de tercer nivel de customers, orders y order details:

Sintaxis:

SHAPE {SELECT * FROM customers}
APPEND ((SHAPE {SELECT * FROM orders}
APPEND ({SELECT * FROM [order details]} AS rsDetails
RELATE orderid TO orderid)) AS rsOrders
RELATE customerid TO customerid)

Que da como resultado:

Customers.*
rsOrders
+----Orders.*
rsDetails
+----[Order Details].*

Jerarquía de relación múltiple

Este ejemplo muestra una jerarquía con un conjunto de registros primario y dos secundarios, uno de los cuales está parametrizado:

Sintaxis:

SHAPE {SELECT * FROM customers}
APPEND ({SELECT * FROM orders WHERE orderdate < #1/1/1998# AND customerid = ?}
RELATE customerid TO PARAMETER 0) AS rsOldOrders,
({SELECT * FROM orders WHERE orderdate >= #1/1/1998#}
RELATE customerid TO customerid) AS rsRecentOrders

Que da como resultado:

Customers.*
rsOldOrders
+----Orders.*
rsRecentOrders
+----Orders.*

Jerarquía con elementos agregados

Sintaxis:

SHAPE (SELECT * FROM orders}
APPEND ({SELECT od.orderid, od.UnitPrice * od.quantity AS ExtendedPrice
FROM [order details] AS od}
RELATE orderid TO orderid) As rsDetails,
SUM(ExtendedPrice) AS OrderTotal

Que da como resultado:

Orders.*
rsDetails
+----orderid
ExtendedPrice
OrderTotal

Jerarquía de grupo

Sintaxis:

SHAPE {SELECT customers.customerid AS cust_id, orders.*
FROM customers inner join orders on customers.customerid = orders.customerid} AS rsOrders
COMPUTE rsOrders BY cust_id

Que da como resultado:

rsOrders
+----cust_id
Orders.*
cust_id

Jerarquía de grupo con agregados

Nota: La cláusula interna SHAPE en este ejemplo es idéntica a la instrucción utilizada en el ejemplo Jerarquía con elementos agregados.

Sintaxis:

SHAPE
(SHAPE {SELECT customers.*, orders.orderid, orders.orderdate
FROM customers inner join orders on customers.customerid = orders.customerid}
APPEND ({SELECT od.orderid, od.unitprice * od.quantity AS ExtendedPrice
FROM [order details] AS od} AS rsDetails
RELATE orderid TO orderid),
SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS rsOrders
COMPUTE rsOrders,
SUM(rsOrders.OrderTotal) AS CustTotal,
ANY(rsOrders.contactname) AS Contact
BY customerid

Que da como resultado:

rsOrders
+----Customers.*
orderid
orderdate
rsDetails
+----orderid
ExtendedPrice
OrderTotal
CustomerTotal
Contact
customerid

Agrupamientos múltiples

Sintaxis:

SHAPE
SHAPE {SELECT customers.*, (od.unitprice * od.quantity) AS ExtendedPrice
FROM (customers inner join orders on customers.customerid = orders.customerid)
inner join [order details] AS od on orders.orderid = od.orderid} AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
ANY(rsDetail.region) AS Region,
SUM(rsDetail.ExtendedPrice) AS CustTotal, rsDetail
BY customerid) AS rsCustSummary
COMPUTE rsCustSummary
BY Region

Que da como resultado:

rsCustSummary
+-----Contact
Region
CustTotal
rsDetail
+----Customers.*
ExtendedPrice
customerid
Region

Total absoluto

Sintaxis:

SHAPE
(SHAPE {SELECT customers.*,
od.unitprice * od.quantity AS ExtendedPrice
FROM (customers inner join orders on customers.customerid = orders.customerid)
inner join [order details] AS od on orders.orderid = od.orderid} AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
SUM(rsDetail.ExtendedPrice) AS CustTotal, rsDetail
BY customerid) AS rsCustSummary
COMPUTE SUM(rsCustSummary.CustTotal) As GrandTotal, rsCustSummary

Que da como resultado:

Observe la ausencia de la cláusula "BY" en el resumen externo. Esto define el Total absoluto, ya que el conjunto de filas primario contiene un único registro con el total absoluto y un puntero al conjunto de registros secundario.

GrandTotal
rsCustSummary
+-----Contact
CustTotal
rsDetail
+----Customers.*
ExtendedPrice
customerid

Jerarquía compleja

Este ejemplo muestra una jerarquía que contiene un conjunto de filas primario, dos secundarios (uno de los cuales está parametrizado) y un detalle de grupo.

Sintaxis:

SHAPE {SELECT customers.* FROM customers} AS rsDetail
COMPUTE rsDetail,
ANY(rsDetail.companyname) AS Company,
({SELECT * FROM orders WHERE customerid = ?}
RELATE customerid TO PARAMETER 0) AS rsOrders,
COUNT(rsOrders.orderid) AS OrderCount
BY customerid

Que da como resultado:

rsDetail
+----Customers.*
Company
rsOrders
+----Orders.*
OrderCount
customerid

Elemento primario agrupado relacionado con un elemento secundario agrupado

Sintaxis:

SHAPE
(SHAPE {SELECT * FROM customers}
APPEND ((SHAPE {SELECT orders.*, year(orderdate) AS OrderYear,
month(orderdate) AS OrderMonth
FROM orders} AS rsOrders
COMPUTE rsOrders
BY customerid, OrderYear, OrderMonth)
RELATE customerid TO customerid) AS rsOrdByMonth) AS rsCustomers
COMPUTE rsCustomers
BY region

Que da como resultado:

rsCustomers
+-----customers.*
rsOrdByMonth
+-----rsOrders
+---- Orders.*
customerid
OrderYear
OrderMonth
region

Acceso a las filas de un RECORDSET jerárquico (Visual Basic)

Tablas de partida

Grupos
IdGrupo
Grupo
1Grupo 1
2Grupo 2
3Grupo 3
4Grupo 4

SubGrupos
IdSubGrupo
IdGrupo
SubGrupo
11SubGrupo 1.1
21SubGrupo 1.2
31SubGrupo 1.3
42SubGrupo 2.1
52SubGrupo 2.2
62SubGrupo 2.3
73SubGrupo 3.1
83SubGrupo 3.2
93SubGrupo 3.3
104SubGrupo 4.1
114SubGrupo 4.2
124SubGrupo 4.3

Conceptos
IdConcepto
IdSubgrupo
Concepto
11Concepto 1
21Concepto 2
32Concepto 3
42Concepto 4
53Concepto 5
63Concepto 6
74Concepto 7
84Concepto 8
95Concepto 9
105Concepto 10
116Concepto 11
126Concepto 12
137Concepto 13
147Concepto 14
158Concepto 15
168Concepto 16
179Concepto 17
189Concepto 18
1910Concepto 19
2010Concepto 20
2111Concepto 21
2211Concepto 22
2312Concepto 23
2412Concepto 24

Gastos
IdGasto
IdConcecepto
Importe
1110
2120
3230
4240
5350
6360
7470
8480
9590
105100
116110
126120
137130
147140
158150
168160
179170
189180
1910190
2010200
2111210
2211220
2312230
2412240
2513250
2613260
2714270
2814280
2915290
3015300
3116310
3216320
3317330
3417340
3518350
3618360
3719370
3819380
3920390
4020400
4121410
4221420
4322430
4422440
4523450
4623460
4724470
4824480

Relaciones entre las tablas:

Recuperar los datos de la primera tabla y cargarlos en un TREEVIEW

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim NewNode As Node
Dim RS As ADODB.Recordset
Dim Nivel1 As String

sSQL = "SHAPE {SELECT IdGrupo, Grupo FROM Grupos} AS LEVEL1 "
Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn

Set NewNode = tvwArbol1.Nodes.Add(, , "r", "Desglose")
NewNode.Expanded = True

Do While Not RS.EOF
Nivel1 = "g" & Trim$(Str$(RS.Fields("IdGrupo").Value))
Set NewNode = tvwArbol1.Nodes.Add("r", tvwChild, _
Nivel1, RS.Fields("Grupo").Value)
RS.MoveNext

Loop

RS.Close
Set RS = Nothing

Recuperar los datos de la primera y segunda tabla y cargarlos en un TREEVIEW

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim NewNode As Node
Dim RS As ADODB.Recordset
Dim rsNivel2 As Variant
Dim Nivel1 As String
Dim Nivel2 As String

sSQL = "SHAPE {SELECT IdGrupo, Grupo FROM Grupos} AS LEVEL1 " & _
"APPEND ({SELECT IdSubGrupo, IdGrupo, SubGrupo FROM SubGrupos} AS LEVEL2 " & _
"RELATE IdGrupo TO IdGrupo)"

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn

Set NewNode = tvwArbol2.Nodes.Add(, , "r", "Desglose")
NewNode.Expanded = True

Do While Not RS.EOF
Nivel1 = "g" & Trim$(Str$(RS.Fields("IdGrupo").Value))
Set NewNode = tvwArbol2.Nodes.Add("r", tvwChild, _
Nivel1, RS.Fields("Grupo").Value)
rsNivel2 = RS("LEVEL2")
Do While Not rsNivel2.EOF
Nivel2 = "s" & Trim$(Str$(rsNivel2.Fields("IdSubGrupo").Value))
Set NewNode = tvwArbol2.Nodes.Add(Nivel1, tvwChild, _
Nivel2, rsNivel2.Fields("SubGrupo").Value)
rsNivel2.MoveNext
Loop
rsNivel2.Close
Set rsNivel2 = Nothing
RS.MoveNext

Loop
RS.Close
Set RS = Nothing

Recuperar los datos de la primera, segunda y tercera tabla y cargarlos en un TREEVIEW

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim NewNode As Node
Dim RS As ADODB.Recordset
Dim rsNivel2 As Variant
Dim rsNivel3 As Variant
Dim Nivel1 As String
Dim Nivel2 As String
Dim Nivel3 As String

sSQL = "SHAPE {SELECT IdGrupo, Grupo FROM Grupos} AS LEVEL1 " & _
"APPEND (( SHAPE {SELECT IdSubGrupo, IdGrupo, SubGrupo FROM SubGrupos} " & _
"AS LEVEL2 APPEND ({SELECT IdConcepto, IdSubGrupo, Concepto FROM Conceptos} " & _
"AS LEVEL3 RELATE IdSubGrupo TO IdSubGrupo)) " & _
"RELATE IdGrupo TO IdGrupo)"

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"
Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn

Set NewNode = tvwArbol3.Nodes.Add(, , "r", "Desglose")
NewNode.Expanded = True
Do While Not RS.EOF
Nivel1 = "g" & Trim$(Str$(RS.Fields("IdGrupo").Value))
Set NewNode = tvwArbol3.Nodes.Add("r", tvwChild, _
Nivel1, RS.Fields("Grupo").Value)
rsNivel2 = RS("LEVEL2")
Do While Not rsNivel2.EOF
Nivel2 = "s" & Trim$(Str$(rsNivel2.Fields("IdSubGrupo").Value))
Set NewNode = tvwArbol3.Nodes.Add(Nivel1, tvwChild, _
Nivel2, rsNivel2.Fields("SubGrupo").Value)
rsNivel3 = rsNivel2("LEVEL3")
Do While Not rsNivel3.EOF
Nivel3 = "c" & Trim$(Str$(rsNivel3.Fields("IdConcepto")))
Set NewNode = tvwArbol3.Nodes.Add(Nivel2, tvwChild, _
Nivel3, rsNivel3.Fields("Concepto").Value)
rsNivel3.MoveNext
Loop
rsNivel3.Close
Set rsNivel3 = Nothing
rsNivel2.MoveNext
Loop
rsNivel2.Close
Set rsNivel2 = Nothing
RS.MoveNext

Loop

RS.Close
Set RS = Nothing

Recuperar los datos de las cuatro tablas y cargarlos en un TREEVIEW

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim NewNode As Node
Dim RS As ADODB.Recordset
Dim rsNivel2 As Variant
Dim rsNivel3 As Variant
Dim rsNivel4 As Variant
Dim Nivel1 As String
Dim Nivel2 As String
Dim Nivel3 As String
Dim Nivel4 As String

sSQL = "SHAPE {SELECT IdGrupo, Grupo FROM Grupos} AS LEVEL1 " & _
"APPEND (( SHAPE {SELECT IdSubGrupo, IdGrupo, SubGrupo FROM SubGrupos} " & _
"AS LEVEL2 APPEND (( SHAPE {SELECT IdConcepto, IdSubGrupo, Concepto FROM Conceptos} " & _
"AS LEVEL3 APPEND ({SELECT IdGasto, IdConcepto, Importe FROM Gastos} AS LEVEL4 " & _
"RELATE IdConcepto TO IdConcepto)) " & _
"RELATE IdSubGrupo TO IdSubGrupo)) " & _
"RELATE IdGrupo TO IdGrupo)"

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn

Set NewNode = tvwArbol4.Nodes.Add(, , "r", "Desglose")
NewNode.Expanded = True

Do While Not RS.EOF
Nivel1 = "g" & Trim$(Str$(RS.Fields("IdGrupo").Value))
Set NewNode = tvwArbol4.Nodes.Add("r", tvwChild, _
Nivel1, RS.Fields("Grupo").Value)
rsNivel2 = RS("LEVEL2")
Do While Not rsNivel2.EOF
Nivel2 = "s" & Trim$(Str$(rsNivel2.Fields("IdSubGrupo").Value))
Set NewNode = tvwArbol4.Nodes.Add(Nivel1, tvwChild, _
Nivel2, rsNivel2.Fields("SubGrupo").Value)
rsNivel3 = rsNivel2("LEVEL3")
Do While Not rsNivel3.EOF
Nivel3 = "c" & Trim$(Str$(rsNivel3.Fields("IdConcepto")))
Set NewNode = tvwArbol4.Nodes.Add(Nivel2, tvwChild, _
Nivel3, rsNivel3.Fields("Concepto").Value)
rsNivel4 = rsNivel3("LEVEL4")
Do While Not rsNivel4.EOF
Nivel4 = "i" & Trim$(Str$(rsNivel4.Fields("IdGasto").Value))
Set NewNode = tvwArbol4.Nodes.Add(Nivel3, tvwChild, _
Nivel4, rsNivel4.Fields("Importe").Value)
rsNivel4.MoveNext
Loop
rsNivel4.Close
Set rsNivel4 = Nothing
rsNivel3.MoveNext
Loop
rsNivel3.Close
Set rsNivel3 = Nothing
rsNivel2.MoveNext
Loop
rsNivel2.Close
Set rsNivel2 = Nothing
RS.MoveNext

Loop

RS.Close
Set RS = Nothing

El comando COMPUTE

Un comando COMPUTE de SHAPE ejecuta una función agregada en las filas del RECORDSET secundario para generar un RECORDSET principal, y a continuación, asigna el RECORDSET secundario a la propiedad VALUE de los objetos FIELD en el RECORDSET principal generado.

Sintaxis:

SHAPE {comando secundario} [[AS] Alias de tabla]
COMPUTE <Función de Agregado>
[BY lista de campos de grupo]

ParteDescripción
Comando secundarioUn comando de consulta que devuelve un objeto RECORDSET. El comando se emite al proveedor de datos principal, y su sintaxis depende de los requisitos de ese proveedor. Normalmente será SQL, aunque ADO no necesita el uso de ningún lenguaje de consulta en particular.
Alias de tablaUn alias utilizado para hacer referencia al RECORDSET devuelto por el comando secundario.
Función de agregadoUna lista de los campos en los que opera una función agregada. Se puede hacer referencia al RECORDSET mediante su alias de tabla en la función agregada. También se puede crear una columna para utilizarla como se desee, con la operación NEW.
Lista de campos de grupoUna lista de columnas que especifica el orden de las filas en el RECORDSET de nivel inferior. Si la cláusula BY no se especifica, entonces sólo se devolverá el resultado de la función agregada. Si se especifica la cláusula BY, el RECORDSET secundario se agregará al RECORDSET principal generado.

El motor de cursor cliente emitirá el comando secundario al proveedor, que devolverá un RECORDSET secundario.

La cláusula COMPUTE especifica una operación agregada para que se ejecute en las columnas especificadas (lista de campos del comando agregado) del RECORDSET secundario, como sumar todos los valores en una columna, o encontrar el valor máximo de una columna. La operación de agregación crea un RECORDSET principal.

Si no existe una cláusula BY, entonces el comando SHAPE concluye. Si existe una cláusula BY, el RECORDSET secundario se agregará al RECORDSET principal. Las filas del RECORDSET secundario serán dispuestas en grupos según se especifica en la lista de campos de grupo.

Funciones de agregadoDescripción
SUM(<alias>.<nombre de campo>)Calcula la suma de todos los valores en el campo especificado.
AVG(<alias>.<nombre de campo>)Calcula la media de todos los valores del campo especificado.
MAX(<alias>.<nombre de campo>)Calcula el valor máximo del campo especificado.
MIN(<alias>.<nombre de campo>)Calcula el valor mínimo del campo especificado.
COUNT((<alias>[.<nombre de campo>])Cuenta el número de filas en el campo especificado.
STDEV(<alias>.<nombre de campo>)Calcula la desviación en el campo especificado.
ANY(<alias>.<nombre de campo>)El valor de una columna (donde el valor de la columna es el mismo en todas las filas)
CALC(expresión)Calcula el valor de una expresión arbitraria, pero sólo en la fila actual.
NEW(tipo de campo
[(ancho|escala[,precisión])]		Añade una columna vacía del tipo especificado al RECORDSET.

Tomando como referencia las tablas del apartado anterior se describirán unos ejemplos para mostrar el cálculo de la suma de los importes en diferentes niveles, el resultado será asignado a un control MSHFlexGrid de Microsoft.

Para realizar un agrupamiento de importes por conceptos

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim RS As ADODB.Recordset

sSQL = "SHAPE {SELECT * FROM Conceptos} AS Command8 " & _
"APPEND ({SELECT * FROM Gastos} AS Command9 " & _
"RELATE IdConcepto TO IdConcepto) AS Command9, " & _
SUM(Command9.Importe) AS Total9

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn
Set MSHFlexGrid1.DataSource = RS

Para realizar un agrupamiento de importes por subgrupos y conceptos

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim RS As ADODB.Recordset

sSQL = "SHAPE {SELECT * FROM SubGrupos} AS Command5 " & _
"APPEND (( SHAPE {SELECT * FROM Conceptos} AS Command6 " & _
"APPEND ({SELECT * FROM Gastos} AS Command7 " & _
"RELATE IdConcepto TO IdConcepto) AS Command7, " & _
"SUM(Command7.Importe) AS Total7) AS Command6 " &_
"RELATE IdSubGrupo TO IdSubGrupo) AS Command6, " & _
"SUM(Command6.Total7) AS Total6"

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn
Set MSHFlexGrid1.DataSource = RS

Para realizar un agrupamiento de importes por grupos, subgrupos y conceptos

Dim Cnn As ADODB.Connection
Dim sSQL As String
Dim RS As ADODB.Recordset

SSQL = "SHAPE {SELECT * FROM Grupos} AS Command1 " & _
"APPEND (( SHAPE {SELECT * FROM SubGrupos} AS Command2 " & _
"APPEND (( SHAPE {SELECT * FROM Conceptos} AS Command3 " & _
"APPEND ({SELECT * FROM Gastos} AS Command4 " & _
"RELATE IdConcepto TO IdConcepto) AS Command4, " & _
"SUM(Command4.Importe) AS Total4) AS Command3 " & _
"RELATE IdSubGrupo TO IdSubGrupo) AS Command3, " & _
"SUM(Command3.Total4) AS Total3) AS Command2 " & _
"RELATE IdGrupo TO IdGrupo) AS Command2, " & _
"SUM(Command2.Total3) AS Total2"

Set Cnn = New ADODB.Connection
Cnn.Provider = "MSDataShape"
Cnn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mis documentos\microsoft\shape\bd.mdb"

Set RS = New ADODB.Recordset
RS.StayInSync = False
RS.Open sSQL, Cnn
Set MSHFlexGrid1.DataSource = RS

Diferencias de resultados con la cláusula BY

Supongamos una tabla llamada "demografía" con el siguiente contenido:

EstadoCiudadPoblación
WASeattle700.000
ORMedford200.000
ORPortland600.000
CALos Angeles900.000
CASan Diego400.000
WATacoma500.000
ORCorvallis300.000

Si emitimos la sentencia:

"SHAPE {select * from demografía} AS TAB1 " & _
"COMPUTE SUM(TAB1.population) AS Población, TAB1"

Obtenemos el siguiente resultado:

  Población Estado Ciudad Población
- 3.600.000WASeatle700.000
 ORMedford200.000
 ORPortland600.000
 CALos Angeles900.000
 CASan Diego400.000
 WATacoma500.000
 ORCorvallis300.000

El RECORDSET obtenido está formado por uno principal que contiene la población total y uno secundario con el total por ciudades.

Si emitimos esta otra sentencia:

"SHAPE {select * from demografía} AS TAB1 " & _
"COMPUTE SUM(TAB1.population) AS Población, TAB1 BY estado

Obtenemos el siguiente resultado:

  Población Estado Estado Ciudad Población
- 1.300.000 CA CA Los Angeles 900.000
  CA San Diego 400.000
- 1.100.000 OR OR Medford 200.000
  OR Portland 600.000
  OR Corvallis 300.000
- 1.200.000 WA WA Seatle 700.000
  WA Tacoma 500.000

En este caso el RECORDSET primario está formado por tres filas que contienen el nombre y el total de la población del estado, el secundario está formado por el detalle de la población.

Gramática formal de la cláusula SHAPE

<comandoShape>::=SHAPE <expTabla> [AS <alias>]
[<acciónShape>]
<acciónShape>::=APPEND <listaCampoAlias>
| COMPUTE <listaCampoAlias>
[BY <listaCampo>]
| BY <listaCampo>
<expTabla>::={<instrucciónSQLNativa>}
| ( <comandoShape> )
<listaCampoAlias>::=<campoAlias> [, <campoAlias...]
<campoAlias>::=<expCampo> [AS <alias>]
<expCampo>::=( <expRelación> ) | <expCalculado>
<expRelación>::=<expTabla> [AS <alias>] RELATE
<listaCondRelación>
<listaCondRelación>::=<conRelación> [, <condRelación>...]
<condRelación>::=<nombreCampo> TO <refSecundaria>
<refSecundaria>::=<nombreCampo> | PARAMETER <refParam>
<refParam>::=<nombre> | <número>
<listaCampo>::=<nombreCampo [, <nombreArchivado>]
<expCalculado>::=SUM (<nombreCampoCalificado>)
| AVG (<nombreCampoCalificado>)
| MIN (<nombreCampoCalificado>)
| MAX (<nombreCampoCalificado>)
| COUNT (<alias>)
| SDEV (<nombreCampoCalificado>)
| ANY (<nombreCampoCalificado>)
| CALC (<expresión>)
<nombreCampoCalificado>::=<alias>.<nombreCampo> | <nombreCampo>
<alias>::=<nombreCitado>
<nombreCampo>::=<nombreCitado>
<nombreCitado>::="<cadena>" | '<cadena>' | <nombre>
<nombre>::=alfabético [ alfabético | dígito | _ | # ...]
<número>::=dígito [dígito...]
<cadena>::=CarácterUnicode [carácerUnicode...]
<expresión>::=una expresión reconocida por el servicio Jet Expression cuyos operandos son otras columnas no calculadas en la misma fila.

Programa de prueba del comando SHAPE en VBA

El siguiente código de programa de VBA le permite escribir su propio comando SHAPE y mostrar la jerarquía de campo o indicar la ubicación del error de sintaxis.

  1. En el Administrador de ODBC del Panel de control agregue un DSN para el controlador ODBC de Microsoft Access 97 llamado OLE_DB_NWIND_JET señalando a la base de datos Neptuno (o NWIND).
  2. Cree un nuevo proyecto. Agregue dos cuadros de texto (Text1 y Text2) y un botón de comando (Command1).
  3. Haga los cuadros de texto lo bastante grandes como para mostrar varias líneas de texto y establezca las siguientes propiedades:
    Multiline: True (sólo en Visual Basic)
    Scrollbars: Vertical
    Font: Courier New 10 Point
  4. En el menú Proyecto, elija Referencias y agregue una referencia a la librería ActiveX Data Objects 2.0 de Microsoft.
  5. Agregue el siguiente código:
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
     
    Me!Text2.Text = ""
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = "MSDataShape"
    cn.Open "dsn=OLE_DB_NWIND_JET"
     
    On Error Resume Next
    rs.Open Me!Text1.Text, cn, adOpenStatic, adLockReadOnly, adCmdText
    If Err Then MsgBox Error
     
    ListChapteredFields rs, 0
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
     
    End Sub
     
    Private Sub LogText(ByVal sLine As String)
     
    If Me!Text2.Text = "" Then
    Me!Text2.Text = sLine
    Else
    Me!Text2.Text = Me!Text2.Text & vbCrLf & sLine
    End If
     
    End Sub
     
    Private Sub ListChapteredFields(ByVal rs As ADODB.Recordset, ByVal Level As Long)
    Dim I As Long
     
    For I = 0 To rs.Fields.Count - 1
    LogText Space$(Level * 3) & rs(I).Name
    If rs(I).Type = adChapter Then
    ListChapteredFields rs(I).Value, Level + 1
    End If
    Next I
     
    End Sub

  6. Ejecute el proyecto. Escriba el comando SHAPE en Text1, haga clic en el botón de comando y aparecerá la jerarquía en Text2.

NOTA: Si escribe incorrectamente los nombres de tabla o de campo cuando utilice el controlador ODBC o los proveedores de JOLT de Access 97, obtendrá el siguiente mensaje:

"Parámetros insuficientes. Se esperaba n. "

Con otros proveedores puede obtener un mensaje diferente.

Actualización: