4D v16

Using the ORDER BY clause

Home

 
Home
4D v16
4D SQL Reference
Tutorial
Using the ORDER BY clause

Using the ORDER BY clause  


 

 

This time we would like to get all the movies that are released in 1960 or more recently, and for each movie we also want additional information such as the year, title, director, media used and tickets sold. The result must be sorted by the year.

  • The initial query in 4D code would be:

     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;>)
  • Using SQL code, the above query becomes:

     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
  • Using generic SQL commands, the above query becomes:

     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
  • Using the QUERY BY SQL command, the above query becomes:

     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;>)
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     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

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 ORDER BY clause button.

Previous page
Next page

 
PROPERTIES 

Product: 4D
Theme: Tutorial

 
HISTORY 

 
ARTICLE USAGE

4D SQL Reference ( 4D v16)