4D v13.4
Using CAST
- 4D SQL Reference
-
- Tutorial
-
- Introduction
- Receiving an SQL query result in a variable
- Using the WHERE clause
- Receiving an SQL query result into arrays
- Using CAST
- Using the ORDER BY clause
- Using the GROUP BY clause
- Using Statistical functions
- Using the HAVING clause
- Calling 4D methods inside the SQL code
- Joins
- Using Aliases
- Subqueries
- SQL code error tracking and debugging
- Data Definition Language
- External connections
- Connection to the 4D SQL engine via the ODBC Driver
Using CAST
The SQL standard has fairly restrictive rules about combining data of different types in expressions. Usually the DBMS is in charge of automatic conversion. However, the SQL standard requires that the DBMS must generate an error if you try to compare numbers and character data. In this context the CAST expression is very important, especially when we use SQL within a programming language whose data types do not match the types supported by the SQL standard.
You will find below the query of the Receiving an SQL query result into arrays section modified slightly in order to use the CAST expression.
- The initial query in 4D code would be:
ARRAY LONGINT(aSoldTickets;0)
ARRAY INTEGER(aMovieYear;0)
ARRAY TEXT(aTitles;0)
ARRAY TEXT(aDirectors;0)
ARRAY TEXT(aMedias;0)
REDUCE SELECTION([MOVIES];0)
$MovieYear:=Num("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)
` Initialize the rest of the list box columns in order to display the information
ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
- Using SQL code, the above query becomes:
ARRAY LONGINT(aSoldTickets;0)
ARRAY INTEGER(aMovieYear;0)
ARRAY TEXT(aTitles;0)
ARRAY TEXT(aDirectors;0)
ARRAY TEXT(aMedias;0)
Begin SQL
SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets
FROM MOVIES
WHERE Year_of_Movie >= CAST('1960' AS INT)
INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;
End SQL
` Initialize the rest of the list box columns in order to display the information
ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
- Using generic SQL commands, the above query becomes:
ARRAY LONGINT(aSoldTickets;0)
ARRAY INTEGER(aMovieYear;0)
ARRAY TEXT(aTitles;0)
ARRAY TEXT(aDirectors;0)
ARRAY TEXT(aMedias;0)
C_TEXT($tQueryTxt)
REDUCE SELECTION([MOVIES];0)
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 >= CAST('1960' AS INT)"
SQL EXECUTE($tQueryTxt;aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets)
SQL LOAD RECORD(SQL All Records)
SQL LOGOUT
` Initialize the rest of the list box columns in order to display the information
ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
- Using the QUERY BY SQL command, the above query becomes:
ARRAY LONGINT(aSoldTickets;0)
ARRAY INTEGER(aMovieYear;0)
ARRAY TEXT(aTitles;0)
ARRAY TEXT(aDirectors;0)
ARRAY TEXT(aMedias;0)
REDUCE SELECTION([MOVIES];0)
QUERY BY SQL([MOVIES];"Year_of_Movie >= CAST('1960' AS INT)")
SELECTION TO ARRAY([MOVIES]Year_of_Movie;aMovieYear;[MOVIES]Title;aTitles;[MOVIES]Director;aDirectors;
[MOVIES]Media;aMedias;[MOVIES]Sold_Tickets;aSoldTickets)
` Initialize the rest of the list box columns in order to display the information
ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
- Using the SQL EXECUTE IMMEDIATE command, the query above becomes:
ARRAY LONGINT(aSoldTickets;0)
ARRAY INTEGER(aMovieYear;0)
ARRAY TEXT(aTitles;0)
ARRAY TEXT(aDirectors;0)
ARRAY TEXT(aMedias;0)
C_TEXT($tQueryTxt)
REDUCE SELECTION([MOVIES];0)
$tQueryTxt:=""
$tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets"
$tQueryTxt:=$tQueryTxt+" FROM MOVIES"
$tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= CAST('1960' AS INT)"
$tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;"
Begin SQL
EXECUTE IMMEDIATE :$tQueryTxt;
End SQL
` Initialize the rest of the list box columns in order to display the information
ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
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 Using CAST button.
Product: 4D
Theme: Tutorial
