Utilizar la cláusula HAVING

Ahora queremos obtener el número total de entradas vendidas por año a partir de 1979, sin incluir las películas con más de 10,000,000 boletos vendidos. El resultado se ordenará por año.
Para hacer esto, debemos sumar el total de boletos vendidos para cada películas desde 1979, eliminar las entradas cuyo total de boletos vendidos sea mayor a 10,000,000, y luego ordenar el resultado por año.

• El código 4D inicial sería:
  
  
   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
  ` Inicializa el resto de las columnas para mostrar la información
 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))

• Utilizando código 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
  ` Inicializa el resto de las columnas para mostrar la información
 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))

• Utilizando comandos SQL genéricos:
  
  
   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
  ` Inicializa el resto de las columnas para mostrar la información
 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))

• Utilizando el comando 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
  ` Inicializa el resto de las columnas para mostrar la información
 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))

• Utilizando el comando 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
  ` Inicializa el resto de las columnas para mostrar la información
 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))

Para probar todos los ejemplos anteriores, lance la base "4D SQL Code Samples" y vaya a la ventana principal. A continuación, puede elegir el modo de búsqueda y presionar el botón HAVING clause.