4D Doc Center : Using the WHERE clause

Home

4D v14

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 ALERTE("The Video Libraontains "+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.