4D v13.4

Subqueries

Home

 
4D v13.4
4D SQL Reference
Tutorial
Subqueries

Subqueries  


 

 

We would now like to get some statistical information regarding the tickets sold: what are the movies where the tickets sold are greater than the average tickets sold for all the movies. To execute this query in SQL, we will use a query within a query, in other words, a subquery.

  • The initial query in 4D code would be:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
     C_LONGINT($i;$vInd;$vAvgSoldTickets)
     
     $vInd:=0
     ALL RECORDS([MOVIES])
     $vAvgSoldTickets:=Average([MOVIES]Sold_Tickets)
     For($i;1;Records in selection([MOVIES]))
        If([MOVIES]Sold_Tickets>$vAvgSoldTickets)
           $vInd:=$vInd+1
           INSERT IN ARRAY(aTitles;$vInd;1)
           aTitles{$vInd}:=[MOVIES]Title
           INSERT IN ARRAY(aSoldTickets;$vInd;1)
           aSoldTickets{$vInd}:=[MOVIES]Sold_Tickets
        End if
        NEXT RECORD([MOVIES])
     End for
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aDirectors;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
     SORT ARRAY(aTitles;aDirectors;aMovieYear;aMedias;aSoldTickets;aNrActors;>)
  • Using SQL code, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
     Begin SQL
        SELECT Title, Sold_Tickets
        FROM MOVIES
        WHERE Sold_Tickets > (SELECT AVG(Sold_Tickets) FROM MOVIES)
        ORDER BY 1
        INTO :aTitles, :aSoldTickets;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aDirectors;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
     SORT ARRAY(aTitles;aDirectors;aMovieYear;aMedias;aSoldTickets;aNrActors;>)
  • Using generic SQL commands, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
     C_TEXT($tQueryTxt)
     
     SQL LOGIN(SQL_INTERNAL;"";"")
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Title, Sold_Tickets"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE Sold_Tickets > (SELECT AVG(Sold_Tickets) FROM MOVIES)"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     SQL EXECUTE($tQueryTxt;aTitles;aSoldTickets)
     SQL LOAD RECORD(SQL All Records)
     SQL LOGOUT
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aDirectors;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
     SORT ARRAY(aTitles;aDirectors;aMovieYear;aMedias;aSoldTickets;aNrActors;>)
  • Using the QUERY BY SQL command, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
      
     QUERY BY SQL([MOVIES];"Sold_Tickets > (SELECT AVG(Sold_Tickets) FROM MOVIES)")
     ORDER BY([MOVIES];[MOVIES]Title;>)
     SELECTION TO ARRAY([MOVIES]Title;aTitles;[MOVIES]Sold_Tickets;aSoldTickets)
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aDirectors;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
     SORT ARRAY(aTitles;aDirectors;aMovieYear;aMedias;aSoldTickets;aNrActors;>)
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
     C_TEXT($tQueryTxt)
     
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Title, Sold_Tickets"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE Sold_Tickets > (SELECT AVG(Sold_Tickets) FROM MOVIES)"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     $tQueryTxt:=$tQueryTxt+" INTO :aTitles, :aSoldTickets"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aDirectors;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))

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 Subqueries button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial