SQL 文の生成

GeneXus は、式を含む For Each 文における項目属性すべてを取り出す単一の SQL 文を生成します。これはすべての Aggregate 式 (集計式) (sum/count/find/min/max) に対して行われ、ほかの式項目属性に対して定義されている式の場合も同様です。これにより、アプリケーションのパフォーマンスを向上します。

例

次のトランザクションを定義するとします。

Customer
{
   CustomerId*
   CustomerName
   CustomerTotal = sum(InvoiceTotal)
}

Invoice
{
   InvoiceId*
   CustomerId
   {
      ItemId*
      ItemPrice
      InvoiceLineQty
      InvoiceLineTotal = ItemPrice * InvoiceLineQty
   }

   InvoiceTotal = sum(InvoiceLineTotal)
}

Item
{
   ItemId*
   ItemPrice
}

その後、次のようにプロシージャーを作成します。

For Each
&CustomerTotal = CustomerTotal
&CustomerName = CustomerName
Endfor
GeneXus が SQL サーバーに対する次の SQL 文を作成します。

SELECT T1. [ CustomerId ] , COALESCE( T2. [ CustomerTotal ] , 0) AS CustomerTotal, T1. [ CustomerName ]  
        FROM ( [ CUSTOMER ]  T1  
        LEFT JOIN (
            SELECT SUM(COALESCE( T4. [ InvoiceTotal ] , 0)) AS CustomerTotal, T3. [ CustomerId ]  
                FROM ( [ INVOICE ]  T3  
                LEFT JOIN (
                    SELECT SUM(T6. [ ItemPrice ]  * T5. [ InvoiceLineQty ] ) AS InvoiceTotal, T5. [ InvoiceId ]  
                    FROM ( [ INVOICELEVEL1 ]  T5 
                        INNER JOIN  [ ITEM ]  T6  ON T6. [ ItemId ]  = T5. [ ItemId ] )
                    GROUP BY T5. [ InvoiceId ]  ) T4 
                ON T4. [ InvoiceId ]  = T3. [ InvoiceId ] )
                GROUP BY T3. [ CustomerId ]  ) T2 ON T2. [ CustomerId ]  = T1. [ CustomerId ] ) 
        ORDER BY T1. [ CustomerId ]

基本的に、内部の SELECT は InvoiceLines から InvoiceTotal を計算し、次の SELECT は InvoiceTotal から CustomerTotal を計算し、最上部の SQL 文は Customer のデータを読み込んで 2 つ目のレベルと組み合わせ、式の値を取得します。

For Each コマンドのベーステーブルが Customer ではなく Invoice の場合は次のとおりです:

For Each
    &CustomerTotal = CustomerTotal
    &CustomerName = CustomerName
    &InvoiceDate= InvoiceDate
EndFor

その場合、SQL サーバーに対する SQL 文は次のとおりです:

SELECT T1. [ CustomerId ] , T1. [ InvoiceId ] , COALESCE( T4. [ InvoiceTotal ] , 0) AS InvoiceTotal, 
     COALESCE( T3. [ CustomerTotal ] , 0) AS CustomerTotal, T1. [ InvoiceDate ] , T2. [ CustomerName ]  
       FROM ((( [ INVOICE ]  T1  INNER JOIN  [ CUSTOMER ]  T2  ON T2. [ CustomerId ]  = T1. [ CustomerId ] ) 
       LEFT JOIN (
            SELECT SUM(COALESCE( T6. [ InvoiceTotal ] , 0)) AS CustomerTotal, T5. [ CustomerId ]  
                   FROM ( [ INVOICE ]  T5  
                   LEFT JOIN (
					   SELECT SUM(T8. [ ItemPrice ]  * T7. [ InvoiceLineQty ] ) AS InvoiceTotal, T7. [ InvoiceId ] , T7. [ ItemId ]  
					   FROM ( [ INVOICELEVEL1 ]  T7  
					         INNER JOIN  [ ITEM ]  T8  ON T8. [ ItemId ]  = T7. [ ItemId ] ) 
					   GROUP BY T7. [ InvoiceId ] , T7. [ ItemId ]  ) T6 
				   ON T6. [ InvoiceId ]  = T5. [ InvoiceId ] ) 
				   GROUP BY T5. [ CustomerId ]  ) T3 ON T3. [ CustomerId ]  = T1. [ CustomerId ] ) 
		LEFT JOIN (
		     SELECT SUM(T6. [ ItemPrice ]  * T5. [ InvoiceLineQty ] ) AS InvoiceTotal, T5. [ InvoiceId ]  
		            FROM ( [ INVOICELEVEL1 ]  T5  
		            INNER JOIN  [ ITEM ]  T6  ON T6. [ ItemId ]  = T5. [ ItemId ] ) 
		            GROUP BY T5. [ InvoiceId ]  ) T4 
		     ON T4. [ InvoiceId ]  = T1. [ InvoiceId ] ) 
	  ORDER BY T1. [ InvoiceId ]

その他の例:

For Each
     Order CustomerTotal                  // Order 節に含まれる式
     Where CustomerTotal > &CustomerTotal // 条件に含まれる式
    &CustomerName = CustomerName
    &InvoiceDate= InvoiceDate
EndFor

For Each
    &DescriptionForMostExpensiveItem = max(ItemPrice, 1=1, 0, ItemDescription)  // InvoiceLines から最大値を返す式 
    //InvoiceLines の拡張テーブル内の項目属性を返す
    &InvoiceDate= InvoiceDate
EndFor

For Each
    &DescriptionForMostExpensiveItem = max(ItemPrice, ItemId > &ItemId, 0, ItemDescription)  // 式の条件に含まれる変数
    &InvoiceDate= InvoiceDate
EndFor

For Each
    &InvoiceTotalAverage = sum(InvoiceTotal) / Count(InvoiceDate)  // 式を含むエクスプレッション (式に対して定義される)
    &CustomerName = CustomerName
EndFor

For Each
    Where Sum(InvoiceTotal) >  100 // 集計式でフィルタリング
    &CustomerName = CustomerName
EndFor

For Each
    Where Sum(InvoiceTotal) / Count(InvoiceDate) >  100 // 複合集計式でフィルタリング
    &CustomerName = CustomerName
EndFor