Calling 4D methods inside the SQL code

We would now like to know something about the actors for each movie: more specifically, we are interested in finding all the movies with at least 7 actors. The result will be sorted by year.
To do this, we will use a 4D function (Find_Nr_Of_Actors) that receives the movie ID as unique parameter and returns the number of actors that played in that movie:

• The initial query in 4D code would be:
  
  
   ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($NrOfActors;$i;$vInd)
 
 $vInd:=0
 $NrOfActors:=7
 ALL RECORDS([MOVIES])
 For($i;1;Records in selection([MOVIES]))
    $vCrtActors:=Find_Nr_Of_Actors([MOVIES]ID)
    If($vCrtActors>=$NrOfActors)
       $vInd:=$vInd+1
       INSERT IN ARRAY(aMovieYear;$vInd;1)
       aMovieYear{$vInd}:=[MOVIES]Year_of_Movie
       INSERT IN ARRAY(aTitles;$vInd;1)
       aTitles{$vInd}:=[MOVIES]Title
       INSERT IN ARRAY(aDirectors;$vInd;1)
       aDirectors{$vInd}:=[MOVIES]Director
       INSERT IN ARRAY(aMedias;$vInd;1)
       aMedias{$vInd}:=[MOVIES]Media
       INSERT IN ARRAY(aSoldTickets;$vInd;1)
       aSoldTickets{$vInd}:=[MOVIES]Sold_Tickets
       INSERT IN ARRAY(aNrActors;$vInd;1)
       aNrActors{$vInd}:=$vCrtActors
    End if
    NEXT RECORD([MOVIES])
 End for
 SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors;>)

• Using SQL code, the above query becomes:
  
  
   ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($NrOfActors;$i;$vInd)
 
 $vInd:=0
 $NrOfActors:=7
 Begin SQL
    SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}
    FROM MOVIES
    WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors
    ORDER BY 1
    INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets, :aNrActors;
 End SQL
  
  
  You can see that we are able to call a 4D function inside SQL code using the syntax:
  
  
  {fn 4DFunctionName AS 4DFunctionResultType}

• Using generic SQL commands, the above query becomes:
  
  
   ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($NrOfActors;$i;$vInd)
 C_TEXT($tQueryTxt)
 
 $vInd:=0
 $NrOfActors:=7
 SQL LOGIN(SQL_INTERNAL;"";"")
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 SQL EXECUTE($tQueryTxt;aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors)
 SQL LOAD RECORD(SQL all records)
 SQL LOGOUT

• Using the QUERY BY SQL command, the above query becomes:
  
  
   ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($NrOfActors;$i;$vInd)
 
 $vInd:=0
 $NrOfActors:=7
 QUERY BY SQL([MOVIES];"{fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors")
 For($i;1;Records in selection([MOVIES]))
    $vInd:=$vInd+1
    INSERT IN ARRAY(aMovieYear;$vInd;1)
    aMovieYear{$vInd}:=[MOVIES]Year_of_Movie
    INSERT IN ARRAY(aTitles;$vInd;1)
    aTitles{$vInd}:=[MOVIES]Title
    INSERT IN ARRAY(aDirectors;$vInd;1)
    aDirectors{$vInd}:=[MOVIES]Director
    INSERT IN ARRAY(aMedias;$vInd;1)
    aMedias{$vInd}:=[MOVIES]Media
    INSERT IN ARRAY(aSoldTickets;$vInd;1)
    aSoldTickets{$vInd}:=[MOVIES]Sold_Tickets
    INSERT IN ARRAY(aNrActors;$vInd;1)
    aNrActors{$vInd}:=Find_Nr_Of_Actors([MOVIES]ID)
    NEXT RECORD([MOVIES])
 End for
 SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors;>)

• Using the SQL EXECUTE IMMEDIATE command, the query above becomes:
  
  
   ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($NrOfActors;$i;$vInd)
 C_TEXT($tQueryTxt)
 
 $vInd:=0
 $NrOfActors:=7
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets,"+" :aNrActors;"
 Begin SQL
    EXECUTE IMMEDIATE :$tQueryTxt;
 End SQL

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 Calling 4D methods button.