Chamar os métodos 4D desde o código SQL

Agora queremos realizar pesquisas relativas aos atores de cada filme: especificamente, estamos interessados em encontrar todas os filmes com pelo menos 7 atores. O resultado será ordenado por ano.
Para fazer isto, utilizamos uma função 4D (Find_Nr_Of_Actors) que recebe o ID do filme e devolve o número de atores do filme:

• O código 4D inicial seria:
  
  
   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;>)

• Utilizando código SQL:
  
  
   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
  
  
  Pode ver que podemos chamar a uma função 4D dentro do código SQL utilizando a sintaxes:
  
  
  {fn 4DFunctionName AS 4DFunctionResultType}

• Utilizando comandos SQL genéricos:
  
  
   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

• Utilizando o comando QUERY BY SQL:
  
  
   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;>)

• Utilizando o comando SQL EXECUTE IMMEDIATE:
  
  
   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

Para provar todos os exemplos anteriores, lance a base "4D SQL Code Samples" e vá até a janela principal. A continuação, pode escolher o modo de pesquisa e pressionar o botão Calling 4D methods.