4D v20

Using the WHERE clause

Home

 
Home
4D v20
4D SQL Reference
Tutorial
Using the WHERE clause

Using the WHERE clause  


 

 

If we now want to know how many movies more recent or equal to 1960 are in the Video Library.
The code 4D would be:

 C_LONGINT($NoMovies)
 
 $NoMovies:=0
 REDUCE SELECTION([MOVIES];0)
 QUERY([MOVIES];[MOVIES]Year_of_Movie>=1960)
 $NoMovies:=Records in selection([MOVIES])
 ALERT("The Video Library contains "+String($NoMovies)+" movies more recent or equal to 1960")

  • Using SQL code, the above query becomes:

     C_LONGINT($NoMovies)
     
     $NoMovies:=0
     REDUCE SELECTION([MOVIES];0)
     Begin SQL
        SELECT COUNT(*)
        FROM MOVIES
        WHERE Year_of_Movie >= 1960
        INTO :$NoMovies;
     End SQL
     ALERT("The Video Library contains "+String($NoMovies)+" movies more recent or equal to 1960")
  • Using the generic SQL commands, the above query becomes:

     C_LONGINT($NoMovies)
     $NoMovies:=0
     REDUCE SELECTION([MOVIES];0)
     
     SQL LOGIN(SQL_INTERNAL;"";"")
     SQL EXECUTE("SELECT COUNT(*) FROM MOVIES WHERE Year_of_Movie >= 1960";$NoMovies)
     SQL LOAD RECORD(SQL all records)
     SQL LOGOUT
     ALERT("The Video Library contains "+String($NoMovies)+" movies more recent or equal to 1960")
  • Using the QUERY BY SQL command, the above query becomes:

     C_LONGINT($NoMovies)
     
     $NoMovies:=0
     REDUCE SELECTION([MOVIES];0)
     QUERY BY SQL([MOVIES];"Year_of_Movie >= 1960")
     $NoMovies:=Records in selection([MOVIES])
     ALERT("The Video Library contains "+String($NoMovies)+" movies more recent or equal to 1960")
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     C_LONGINT($NoMovies)
     C_TEXT($tQueryTxt)
     
     $NoMovies:=0
     REDUCE SELECTION([MOVIES];0)
     $tQueryTxt:="SELECT COUNT(*) FROM MOVIES WHERE Year_of_Movie >= 1960 INTO :$NoMovies;"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
     ALERT("The Video Library contains "+String($NoMovies)+" movies more recent or equal to 1960")

As in the previous section, in order to test all the above examples, simply launch the "4D SQL Code Samples" database and go to the main window. You can then choose the query mode and press the WHERE clause button.

Previous page
Next page

 
PROPERTIES 

Product: 4D
Theme: Tutorial

 
PAGE CONTENTS 
 
HISTORY 

 
ARTICLE USAGE

4D SQL Reference ( 4D v20)