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:
$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;>)
$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:
$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;>)
$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.