4D Doc Center : ORDER BY句の使用

4D v14

ORDER BY句の使用

ここでは、1960年以降に公開されたすべての映画に対し、公開年、タイトル、ディレクタ、メディア、チケット販売数の情報を取得します。結果は公開年で並び替えます。

4Dコードは以下のようになります:

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

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

汎用SQLコマンドの場合:

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

QUERY BY 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) &NBSP;&NBSP;&NBSP;&NBSP; 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;>)

SQLの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

これらをテストするには、4D SQL Code Samplesデータベースを起動してメインウィンドウを表示させます。クエリモードを選択してORDER BY clauseボタンをクリックします。