4D v14

Utilizar la cláusula HAVING

Inicio

 
4D v14
Utilizar la cláusula HAVING

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.

 
PROPIEDADES 

Producto: 4D
Tema: Tutorial

 
ARTICLE USAGE

Manual de SQL ( 4D v11 SQL Release 4 )
Manual de SQL ( 4D v14)
Manual de SQL ( 4D v12.1)
Manual de SQL ( 4D v13.4)
Manual de SQL ( 4D v14 R2)
Manual de SQL ( 4D v14 R3)
Manual de SQL ( 4D v14 R4)