HAVING句の使用

ここでは1979年以降、販売されたチケットの総数を年ごとに取得します。ただし販売数が10,000,000を上回る年は除きます。結果は年で並び替えられます。
これを行うために、1979年以降の年ごとにすべての映画のチケット総販売数を計算し、販売数が10,000,000を上回る年を取り除き、年で並び替えます。

• 4Dコードによるクエリは以下のようになります:
  
  
   ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$vCrtMovieYear;$i;$MinSoldTickets;$vInd)
  
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 $MinSoldTickets:=10000000
 QUERY([MOVIES];[MOVIES]Year_of_Movie>=$MovieYear)
 ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
 $vCrtMovieYear:=0
 $vInd:=Size of array(aMovieYear)
 For($i;1;Records in selection([MOVIES]))
    If([MOVIES]Year_of_Movie#$vCrtMovieYear)
       $vCrtMovieYear:=[MOVIES]Year_of_Movie
       If(aSoldTickets{$vInd}<$MinSoldTickets)
          $vInd:=$vInd+1
          INSERT IN ARRAY(aMovieYear;$vInd;1)
          aMovieYear{$vInd}:=$vCrtMovieYear
          INSERT IN ARRAY(aSoldTickets;$vInd;1)
       Else
          aSoldTickets{$vInd}:=0
       End if
    End if
    aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
    NEXT RECORD([MOVIES])
 End for
 If(aSoldTickets{$vInd}>=$MinSoldTickets)
    DELETE FROM ARRAY(aSoldTickets;$vInd;1)
    DELETE FROM ARRAY(aMovieYear;$vInd;1)
 End if
  // 情報を表示するために、残りのリストボックス列を初期化します
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• SQLコードを使用すると、上のクエリは以下のようになります:
  
  
   ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$MinSoldTickets)
 
 $MovieYear:=1979
 $MinSoldTickets:=10000000
 Begin SQL
    SELECT Year_of_Movie, SUM(Sold_Tickets)
    FROM MOVIES
    WHERE Year_of_Movie >= :$MovieYear
    GROUP BY Year_of_Movie
    HAVING SUM(Sold_Tickets) < :$MinSoldTickets
    ORDER BY 1
    INTO :aMovieYear,  :aSoldTickets;
 End SQL
  // 情報を表示するために、残りのリストボックス列を初期化します
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• 汎用SQLコマンドを使用すると、上のクエリは以下のようになります:
  
  
   C_TEXT($tQueryTxt)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$MinSoldTickets)
  
 $MovieYear:=1979
 $MinSoldTickets:=10000000
 SQL LOGIN(SQL_INTERNAL;"";"")
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
 $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
 $tQueryTxt:=$tQueryTxt+" HAVING SUM(Sold_Tickets) < :$MinSoldTickets"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 SQL EXECUTE($tQueryTxt;aMovieYear;aSoldTickets)
 SQL LOAD RECORD(SQL all records)
 SQL LOGOUT
  // 情報を表示するために、残りのリストボックス列を初期化します
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• QUERY BY SQLコマンドを使用すると、上のクエリは以下のようになります:
  
  
   C_TEXT($tQueryTxt)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$MinSoldTickets;$vCrtMovieYear;$vInd;$i)
     
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 $MinSoldTickets:=10000000
 QUERY BY SQL([MOVIES];"Year_of_Movie >= :$MovieYear")
 ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
 $vCrtMovieYear:=0
 $vInd:=Size of array(aMovieYear)
 For($i;1;Records in selection([MOVIES]))
    If([MOVIES]Year_of_Movie#$vCrtMovieYear)
       $vCrtMovieYear:=[MOVIES]Year_of_Movie
       If(aSoldTickets{$vInd}<$MinSoldTickets)
          $vInd:=$vInd+1
          INSERT IN ARRAY(aMovieYear;$vInd;1)
          aMovieYear{$vInd}:=$vCrtMovieYear
          INSERT IN ARRAY(aSoldTickets;$vInd;1)
       Else
          aSoldTickets{$vInd}:=0
       End if
    End if
    aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
    NEXT RECORD([MOVIES])
 End for
 If(aSoldTickets{$vInd}>=$MinSoldTickets)
    DELETE FROM ARRAY(aSoldTickets;$vInd;1)
    DELETE FROM ARRAY(aMovieYear;$vInd;1)
 End if
  // 情報を表示するために、残りのリストボックス列を初期化します
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• SQLのEXECUTE IMMEDIATEコマンドを使用すると、上のクエリは以下のようになります:
  
  
   C_TEXT($tQueryTxt)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$MinSoldTickets)
 
 $MovieYear:=1979
 $MinSoldTickets:=10000000
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
 $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
 $tQueryTxt:=$tQueryTxt+" HAVING SUM(Sold_Tickets) < :$MinSoldTickets"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aSoldTickets;"
 Begin SQL
    EXECUTE IMMEDIATE :$tQueryTxt;
 End SQL
  // 情報を表示するために、残りのリストボックス列を初期化します
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

これらをテストするには、"4D SQL Code Samples"データベースを起動してメインウィンドウを表示させます。そしてクエリモードを選択してHAVING clauseボタンをクリックします。