4D v20

Utiliser la clause ORDER BY

Accueil

 
Accueil
4D v20
4D - Référence SQL
Prise en main
Utiliser la clause ORDER BY

Utiliser la clause ORDER BY  


 

 

Nous souhaitons désormais récupérer tous les films tournés depuis 1960 et, pour chaque film, l'année, le titre, le réalisateur, le support media et le nombre d'entrées. Le résultat devra être trié par année.
Voici le code 4D correspondant à cette requête :

 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;>)

  • En utilisant du code 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
  • En utilisant les commandes SQL génériques :

     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
  • En utilisant la commande CHERCHER PAR 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;>)
  • En utilisant la commande SQL dynamique 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

    Pour tester tous ces exemples, lancez la base "4D SQL Code Samples" et affichez la boîte de dialogue principale. Choisissez le mode d'interrogation du moteur de 4D et cliquez sur le bouton Clause ORDER BY.

Page préc.
Page suiv.

 
PROPRIÉTÉS 

Produit : 4D
Thème : Prise en main

 
PAGE CONTENTS 
 
HISTORIQUE 

 
UTILISATION DE L'ARTICLE

4D - Référence SQL ( 4D v20)