4D v13.4

Using the HAVING clause

Home

 
4D v13.4
Using the HAVING clause

Using the HAVING clause  


 

 

We would now like to get the total amount of tickets sold per year starting with 1979, but not including those with over 10,000,000 tickets sold. The result will be sorted by year.
To do this, we must total all the tickets sold for every movie in each year more recent than 1979, remove those where the total amount of tickets sold is greater than 10,000,000, and then sort the result by year.

  • The initial query in 4D code would be:

     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
      ` Initialize the rest of the list box columns in order to display the information
     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))
  • Using SQL code, the above query becomes:

     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
      ` Initialize the rest of the list box columns in order to display the information
     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))
  • Using generic SQL commands, the above query becomes:

     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
      ` Initialize the rest of the list box columns in order to display the information
     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))
  • Using the QUERY BY SQL command, the above query becomes:

     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
      ` Initialize the rest of the list box columns in order to display the information
     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))
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     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
      ` Initialize the rest of the list box columns in order to display the information
     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))

To test all the above examples, launch the "4D SQL Code Samples" database and go to the main window. You can then choose the query mode and press the HAVING clause button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial