Utilizar a cláusula ORDER BY

4D v15

Esta vez nos gostaria conseguir todas os filmes que foram lançados no ano 1960 ou mais recente, e para cada filme obter informação adicional, como o ano, título, diretor, os meios utilizados e boletos vendidos. O resultado deve ser organizado por ano.

O código 4D inicial seria:

ARRAY LONGINT(aNrActors;0) ARRAY LONGINT(aSoldTickets;0) ARRAY INTEGER(aMovieYear;0) ARRAY TEXT(aTitles;0) ARRAY TEXT(aDirectors;0) ARRAY TEXT(aMedias;0) C_LONGINT($MovieYear) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 QUERY([MOVIES];[MOVIES]Year_of_Movie>=$MovieYear) SELECTION TO ARRAY([MOVIES]Year_of_Movie;aMovieYear;[MOVIES]Title;aTitles;[MOVIES]Director;aDirectors; [MOVIES]Media;aMedias;[MOVIES]Sold_Tickets;aSoldTickets) SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;>)

Utilizando código SQL:

ARRAY LONGINT(aNrActors;0) ARRAY LONGINT(aSoldTickets;0) ARRAY INTEGER(aMovieYear;0) ARRAY TEXT(aTitles;0) ARRAY TEXT(aDirectors;0) ARRAY TEXT(aMedias;0) C_LONGINT($MovieYear) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 Begin SQL SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets FROM MOVIES WHERE Year_of_Movie >= :$MovieYear ORDER BY 1 INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets; End SQL

Utilizando comandos SQL genéricos:

C_TEXT($tQueryTxt) ARRAY LONGINT(aNrActors;0) ARRAY LONGINT(aSoldTickets;0) ARRAY INTEGER(aMovieYear;0) ARRAY TEXT(aTitles;0) ARRAY TEXT(aDirectors;0) ARRAY TEXT(aMedias;0) C_LONGINT($MovieYear) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 SQL LOGIN(SQL_INTERNAL;"";"") $tQueryTxt:="" $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets" $tQueryTxt:=$tQueryTxt+" FROM MOVIES" $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear" $tQueryTxt:=$tQueryTxt+" ORDER BY 1" SQL EXECUTE($tQueryTxt;aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets) SQL LOAD RECORD(SQL all records) SQL LOGOUT

Utilizando o comando QUERY BY SQL:

ARRAY LONGINT(aNrActors;0) ARRAY LONGINT(aSoldTickets;0) ARRAY INTEGER(aMovieYear;0) ARRAY TEXT(aTitles;0) ARRAY TEXT(aDirectors;0) ARRAY TEXT(aMedias;0) C_LONGINT($MovieYear) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 QUERY BY SQL([MOVIES];"Year_of_Movie >= :$MovieYear") SELECTION TO ARRAY([MOVIES]Year_of_Movie;aMovieYear;[MOVIES]Title;aTitles;[MOVIES]Director;aDirectors; [MOVIES]Media;aMedias;[MOVIES]Sold_Tickets;aSoldTickets) SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;>)

Utilizando o comando SQL EXECUTE IMMEDIATE:

ARRAY LONGINT(aNrActors;0) C_TEXT($tQueryTxt) ARRAY LONGINT(aSoldTickets;0) ARRAY INTEGER(aMovieYear;0) ARRAY TEXT(aTitles;0) ARRAY TEXT(aDirectors;0) ARRAY TEXT(aMedias;0) C_LONGINT($MovieYear) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 $tQueryTxt:="" $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets" $tQueryTxt:=$tQueryTxt+" FROM MOVIES" $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear" $tQueryTxt:=$tQueryTxt+" ORDER BY 1" $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;" Begin SQL EXECUTE IMMEDIATE :$tQueryTxt; End SQL

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