CHAPTER 10: 関数

Empressの関数には、集計関数、組込み関数、数学関数、ユーザ定義関数、ユーザ定義集計関数に分類できます。

集計関数は、COUNT、MAX、MIN、SUM、AVGです。

以下に組込み関数を示します。

使用可能な数学関数は、システムに依存します。以下に示す関数のサブセットとなります。

ユーザ定義関数、ユーザ定義集計関数については、Empress:ユーザ定義関数の マニュアルを参照してください。

SELECTコマンド、あるいは式においてレコードやグループに適用できる 関数は、COUNT、MAX、MIN、SUM、AVGの 5つがあります。

SELECTまたは式で、COUNT関数を使用すると、各グループの レコード数がカウントされます。コマンドにGROUP BY句がない場合には、 選択された全てのレコードを1つのグループとして、関数はそのレコード数を 返します。

COUNT関数は、選択する項目のリストおよびHAVING句に指定 できます。

Syntax

COUNT [

|(*) |expr |(DISTINCT |

|attr |expr

|) |

|] | | |

注意

DISTINCTまたはUNIQUEが指定された場合、重複値はカウント されません。選択する項目のリストに条件を設定する場合、DISTINCTは1度 しか使用できません。DISTINCTをCOUNT関数の外部で指定した場合、 カウントには重複値もカウントされます。

例

1. シンプルな例

   loansテーブルのレコード数をカウントします。

      SELECT COUNT FROM loans;
   
   

   以下のような表示をします。

      COUNT (*)
   
      12
   
   

   または

      SELECT COUNT (*) FROM loans;
   
   

   と指定しても、同一の結果を返します。

2. フィールド値がユニークなレコードの数をカウントします。

   loansテーブルの社員数をカウントします。

      SELECT COUNT (DISTINCT name) FROM loans;
   
   

   以下のような結果を返します。

      COUNT (DISTINCT name)
   
      6
   
   

3. フィールドの値を条件を付けてカウントします。

   loansテーブルで、nameフィールドがMoscaのレコード をカウントします。

      SELECT COUNT FROM loans WHERE name = 'Mosca';
   
   

   以下のような結果を返します。

      COUNT (*)
   
      2
   
   

   personnelテーブルで、phoneフィールド値が'961'で始まる レコードをカウントします。

      SELECT COUNT FROM personnel
         WHERE phone match "961*";
   
   

   以下のような結果を返します。

      COUNT (*)
   
      1
   
   

4. 他の検索項目と共に関数を使用します。

   COUNT関数は、他の検索項目と共に使用できます。

      SELECT name, amount, COUNT FROM loans;
   
   

   以下のような結果を返します。

      name          amount
      
      Mosca         $150.00
      Jones          $33.95
      Kilroy        $250.00
      Wladislaw      $55.00
      Jones          $25.00
      Mosca         $200.00
      Wladislaw      $25.00
      Peterson       $50.00
      Wladislaw      $75.00
      Jones         $300.00
      Scarlatti     $150.00
      Scarlatti     $125.00
      
      COUNT (*) = 12
   
   

AVG、MAX、MIN、SUM関数は、グループに 関する指定されたフィールドの値に適用されます。これらの関数は、以下の ような1つの値を返します。

SUM	グループ内の集計値です。
AVG	グループ内の平均値です。
MAX	グループ内の最大値です。
MIN	グループ内の最小値です。

Syntax

|AVG |MAX |MIN |SUM

| ([DISTINCT] attr) | | |

注意

1. SUMとAVGは数値フィールドにのみ適用されます。 UNIQUEまたはDISTINCTが指定された場合、重複値は 除外されます。

2. MAXまたはMINに、DISTINCTを指定しても効力がない ことに注意してください。

例

1. MAX関数の例

   loansテーブル中の、amountフィールドの最大値を検索します。

      SELECT MAX (amount) FROM loans;
   
   

   以下のような結果を表示します。

      MAX (amount)
   
      $300.00
   
   

2. MIN関数の例

   loansテーブル中の、dateフィールドの最も古い日付を検索し ます。

      SELECT MIN (date) FROM loans;
   
   

   以下のような結果を表示します。

      MIN (date)
   
      2 February 1990
   
   

3. 条件付きのMAX関数の例

   personnelテーブル中の、nameフィールドの先頭文字がA-Mで 始まるレコードの中で、credit_limmitの最大値を検索します。

      SELECT MAX (credit_limit) FROM personnel
         WHERE name < 'N';
   
   

   以下のような結果を表示します。

      MAX (credit_limit)
   
      $750.00
   
   

4. AVG関数の例

   loansテーブルの、amountフィールドの平均値を検索します。

      SELECT AVG (amount) FROM loans;
   
   

   以下のような結果を表示します。

      AVG (amount)
   
      119.91
   
   

5. 条件付きのSUM関数の例

   loansテーブル中の、nameフィールドが"Mosca"のamount フィールドの集計値を検索します。

      SELECT SUM (amount) FROM loans
         WHERE name = 'Mosca';
   
   

   以下のような結果を表示します。

      SUM (amount)
   
      350.00
   
   

6. 関数の組み合わせ例

   関数は組み合わせて使用できます。

      SELECT COUNT, SUM (amount), AVG (amount), MAX (amount),
         MIN (amount) FROM loans;
   
   

   以下のような結果を表示します。

      COUNT(*)  SUM (amount)  AVG (amount)  MAX (amount)  MIN(amount)
   
      12        1438.95       119.91        $300.00       $25.00
   
   

キーワードALLを使用したり、省略値を使用してフィールド値を検索した 場合、重複値が発生する場合があります。 この結果は、データベース処理として矛盾するものではありませんが、必ずしもユーザが希望する結果であるとは限りません。

   SELECT name FROM loans;

上記のコマンドでは、12レコードが検索されます。この検索結果には同一の名前が 複数含まれています。重複のないフィールド値を検索するには、SELECT コマンドにDISTINCTキーワードを指定します。

キーワードDISTINCTは、関数SUM、AVG、 COUNTの中で指定できます。

以下のコマンドは、重複値のないレコードを検索する例です。

    SELECT DISTINCT name FROM loans;

以下のような結果を表示します。

   name

   Mosca
   Jones
   Kilroy
   Wladislaw
   Peterson
   Scarlatti

loansテーブル中の、nameフィールドに違う名前がいくつあるか 検索するには、以下のコマンドを使用します。

   SELECT COUNT (DISTINCT name) FROM loans;

以下のような結果を表示します。

   COUNT (DISTINCT name)

   6

以下に、Empressの組込み関数とその要約を示します。

Table 10-1: 組込み関数

詳細または例題は、Empress組込み関数を参照してください。

数値データの絶対値を返します。

Syntax

abs (number)

説明

number

数値か数値フィールドを指定します。

注意

NULLフィールドは処理しません。

例

以下は750を返します。

   abs (750-1500)

以下は、amoutフィールドの絶対値を返します。

   SELECT abs (amount) FROM loans;

指定された10進数値を、切り上げて返します。

Syntax

ceiling (decimal [, digits])

説明

decimal	数値、数値フィールド、数値式の結果。
digits	小数位の後の数字の数。正の数または負の数になります。省略値は0です。

注意

NULL値は処理しません。

例

   ceiling (12.345)

上記は13.0を返します。

   ceiling (12.345, 2)

上記は12.35を返します。

   ceiling (12.345, -1)

上記は20.0を返します。

指定された日付の、次に指定された日付を返します。

Syntax

datenext (date,

|day_name |day_number

|) |

説明

date	日付文字列、またはDATE、TIMEデータタイプのフィールド。
day_name	曜日。例 Monday, Tuesday, etc。
day_number	曜日数。例 1 is Monday, 2 is Tuesday, etc。

注意

1. 日付の数値付けは、日曜日を0としてはじめます。

2. datenext()は、NULL値は処理しません。

3. キーワードTODAYとNOWは、日付として使用できます。

例

今日がMonday, February 2,1998である場合、

   datenext ("TODAY", "Monday")

Febryary 9, 1998（TIMEフォーマットでは19980209000000)を返します。

今日がMonday, February 2,1998である場合、

   datenext ("TODAY", 1)

1はMondayを意味するため、前の例を同一の値を返します。

今日がMonday, February 2,1998である場合、

datenext ("TODAY", 2)

February 3, 1998を返します。

fllor()は、指定された小数部の桁数まで切り捨てた値を返します。

Syntax

floor (number [, digits])

説明

number	数値または数値フィールド。
digit	小数位の後の数字の数。それは正の数または負の数になります。省略値は0です。

注意

1. DOLLARデータは、FLOATに変換されます。

2. NULL値は処理しません。

例

   floor (12.345)

上記は12.0を返します。

   floor (12.345, 2)

上記は12.34を返します。

   floor (12.345, -1)

上記は10.0を返します。

真を返した場合、精度を失うことなく文字列をEmpressの10進数値に変換 します。

Syntax

is_decimal (string [, max_digits [, decimal_places]])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
max_digits	変換値のために許可された数字の最大数。省略値は15。
decimal_places	変換値のために許可された小数位の数。省略値は15。

注意

1. NULL値は処理しません。

2. is_decimal関数は、WHERE句で使用されます。

例

   is_decimal ("12.345", 5, 3)

上記は真を返します。

   is_decimal ("12.3450", 5, 3)

上記は真を返します。

   is_decimal ("12.3456", 5, 3)

上記は偽を返します。

   SELECT FROM loans WHERE is_decimal (name);

上記はレコードを検索しません。

指定された文字列を、精度を失うことなくEmpressの整数値に変換した場合、 真の値を返します。

Syntax

is_integer (string [, nbytes])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
nbytes	バイト数。省略値は4。

注意

1. バイト数を指定すると、変換値が指定されたバイト数で収まる場合のみ、真の値を返します。指定できるバイト数 は1〜4であり、省略時は4となります。

2. NULL値は処理しません。

3. この関数はWHERE句で使用します。

例

T1テーブルは、最大4文字のフィールドで、データとして"abcd"、"1234"、"123"の3レコードを持ちます。

   SELECT FROM T1 WHERE is_integer (ATTR1);

上記は、123と1234を返します。

   SELECT FROM T1 WHERE is_integer (ATTR1, 1);

上記は123を返します。

指定された文字列に、スペース、タブ、行接続詞、改行、垂直タブなどの文字がある場合、真を返します。

Syntax

is_white (string)

説明

string

任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。

注意

1. NULL値を処理しません。

2. WHERE句で使用します。

例

T1テーブルは、テキストフィールドがあり、3レコードを持っています。1レコードは、スペースとタブ、2行目は全て スペース、3行目はタブを持っています。

   SELECT FROM T1 WHERE is_white (ATTR1);

上記は、2レコート目と3レコード目が該当します。

   SELECT FROM T1 WHERE not (is_white (ATTR1));

上記は、1レコード目が該当します。

指定された文字列のバイト長を返します。

Syntax

length (string)

説明

string

任意のデータタイプのフィールドの文字列。

注意

NULL値は処理しません。

例

テーブルT1は、最大10バイトのCharタイプのフィールドを持つテーブル で、Jim、John、Fredという3レコードを持っています。

   SELECT T1 WHERE length (name) > 3;

上記は、JohnとFredを返します。

   length ("abc")

上記は、3を返します。

指定された文字列の左端に、指定された文字を指定された個数追加します。

Syntax

lpad (string, pad_string [, [occurrence])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
pad_string	任意の文字列。
occurrence	整数番号。

注意

1. 指定された個数分文字を挿入します。

2. 埋め込み数が指定されていない場合、および指定されたものが文字列か TEXTデータフィールドである場合、埋め込みは1回だけ行なわれます。

   指定文字列が、Charフィールドの場合、埋め込み回数は、データタイプの 最大値まで行なわれます。

例

コマンド

   lpad ("program", "MY")

上記は"111program"を返します。

コマンド

   lpad ("program", "1", 3)

上記は"111program"を返します。

T1テーブルが、char(10,1)のデータタイプを持つCフィールドを持ち、データとして"abc"と"123456789"を持つ 場合に、以下のコマンドを発行した場合、

   SELECT lpad (C, "MY") FROM T1;

以下の値を返します。

   MYMYMYabc
   123456789

指定された文字列の中で、指定番目に見つかった、指定文字の開始位置を返します。

Syntax

lscan (string, search_string [, occurrence ])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
search_string	任意の文字列。
occurrence	整数番号。

注意

1. 指定文字列の、先頭文字位置は1です。

2. 何番目かの指定がない場合、最初に見つかったものの先頭位置を返します。

3. 検索文字列が見つからなかった場合、0を返します。

4. lscan()という関数名の代わりに、strindex()も使用できます。

5. NULL値は処理しません。

例

コマンド

   lscan ("abcdef", "cd")

上記は、3を返します。

コマンド

   lscan ("abcdaef", "a", 2)

上記は、5を返します。

指定された文字列から、指定した文字を削除して返します。

Syntax

ltrim (string [, trim_set])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
trim_set	任意の文字列。省略値はスペース、タブ、改行文字、垂直タブ、フォームフィード を含む、ホワイトスペースです。

注意

1. NULL値は処理しません。

2. 削除する文字の指定順は関係ありません。"xy"の指定と"yx"の指定は、同一の結果を返します。

例

以下の例は、

   ltrim ("abcdef", "ab")

上記は、"cdef"を返します。

以下の例は、

   ltrim ("   abc")

上記は、"abc"を返します。

指定フィールドの値がNULL値の場合、nullval()に設定した値が 返されます。NULL値でない場合には、フィールド値が返されます。

Syntax

nullval (attr, default_value)

説明

default_value

attrと同じ汎用データタイプの任意の文字列または、 任意のフィールド。

例

T1テーブルは、最大10バイトのCharタイプのnameフィールドを 持ち、データとして、NULL、"John"、"Fred"という3レコードがある場合。

   SELECT nullval (name, "X") FROM T1;

上記は、"X"、"John"、""Fred"を返します。

指定された10進数を、指定された小数部の桁数で四捨五入します。

Syntax

round (number [, digits])

説明

number	任意の数値または数値フィールド。
digits	小数位の後の数字の数。それは正の値にも負の値にもなります。省略値は0です。

注意

1. DOLLARデータは、FLOATに変換されます。

2. NULL値は処理しません。

例

以下の例は、

   round (12.345)

上記は、12.0を返します。

以下の例は、

   round (12.345, 2)

上記は、12.35を返します。

以下の例は、

   round (12.345, -1)

上記は、10.0を返します。

指定された文字列の右端に、指定した文字列を、指定回数追加した文字列を返します。

Syntax

rpad (string, pad_string [, [occurrence])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
pad_string	任意の文字列。
occurrence	整数番号。

注意

1. 指定された回数分、文字列に追加します。

2. 回数が指定されなかった場合、追加する回数は1回です。

   文字列にフィールド名が指定された場合、データタイプの最大値まで、追加することができます。

例

以下の例は、

   rpad ("program", "MY")

上記は、"programMY"を返します。

以下の例は、

   rpad ("program", "1", 3)

上記は、"program111"を返します。

T1テーブルに、char(10,1)タイプのCフィールドを持っていて、"abc"と"123456789"というレコードを格納している 場合、以下のコマンドは、

   SELECT rpad (C, "MY") FROM T1;

以下を表示します。

   abcMYMYMY
   123456789

指定された文字列の右端から、指定された回数目の指定文字列の開始位置を返します。

Syntax

rscan (string, search_string [, occurrence])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
search_string	任意の文字列。
occurrence	整数番号。

注意

1. 検索は、右端から左へ行なわれ、左端の文字位置を1とします。

2. 回数目を指定しない場合は、最初に見つかった文字列の開始位置を返します。

3. 指定された文字列に、検索文字が見つからなかった場合には、0を返します。

4. rscan()の代わりに、rstrindex()を使用できます。

5. NULL値は処理しません。

例

以下は、10を返します。

   rscan ("John and John", "John")

以下は、1を返します。

   rscan ("John and John", "John", 2)

以下は、0を返します。

   rscan (("John and John", "Jim")

指定された文字列の右端から、指定された回数目の指定文字列の開始位置を返します。

Syntax

rstrindex (string, search_string [, occurrence])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
search_string	任意の文字列。
occurrence	整数番号。

注意

1. 検索は、右端から左へ行なわれ、左端の文字位置を1とします。

2. 回数目を指定しない場合は、最初に見つかった文字列の開始位置を返します。

3. 指定された文字列に、検索文字が見つからなかった場合には、0を返します。

4. rstrindex()の代わりに、rscan()を使用できます。

5. NULL値は処理しません。

例

以下は、10を返します。

   rstrindex ("John and John", "John")

以下は、1を返します。

   rstrindex ("John and John", "John", 2)

以下は、0を返します。

   rstrindex (("John and John", "Jim")

指定された文字列の右端から、指定された文字を削除して返します。

Syntax

rtrim (string [, trim_set])

説明

string	任意の文字列、またはINTEGER、CHARACTER、TEXT データタイプのフィールド。
trim_set	任意の文字列。省略値は、スペース、タブ、改行、垂直タブ、フォームフィードを 含むホワイトスペースです。

注意

1. 指定された文字を削除した残りの文字全てが、結果として返されます。

2. NULL値は処理しません。

3. 削除文字に指定する順番は処理に影響を与えません。"xy"と指定しても、"yx"と指定しても同一に結果を 返します。

例

以下は、"abcdef"を返します。

   rtrim ("abcdef")

以下は、"abcd"を返します。

   rtrim ("abcdef", "fe")

以下は、"abcd"を返します。

   rtrim ("abcdef", "ef")

指定された数値の符号に合わせて値を返します。

Syntax

sign (number)

説明

number

任意の数値、数値データタイプのフィールド、式の結果。

注意

1. 正の値は1、ゼロは0、負の値は-1を返します。

2. NULL値は処理しません。

例

以下は、-1を返します。

   sign (150-700)

以下は、0を返します。

   sign (150-150)

以下は、1を返します。

   sign (700-500)

指定された文字列から、指定位置から指定長さの文字列を削除して返します。

Syntax

strdel (string, position [, length])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
position	文字列中の位置。
length	文字列のpositionから、削除する文字数。. INTEGERフィールドも指定できます。省略値は1。

注意

1. 有効なデータタイプは、CHAR、TEXT、BULKです。

2. NULL値は処理しません。

例

以下の値は、"ac"を返します。

   strdel ("abc", 2)

指定された文字列の左端から、指定番目に見つかった、指定文字列の開始位置を返します。

Syntax

strindex (string, search_string [, occurrence])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
search_string	任意の文字列。
occurrence	整数番号。

注意

1. 文字列の開始文字位置は、1です。

2. 何番目かの指定がない場合、最初に見つかった文字列の開始位置を返します。

3. 検索文字が見つからなかった場合、0を返します。

4. strindex()の代わりに、lscan()を使用できます。

5. NULL値は処理しません。

例

以下は、3を返します。

   strindex ("abcdef", "cd")

以下は、5を返します。

   strindex ("abcdaef", "a", 2)

指定された文字列に、指定された挿入文字列を挿入して返します。

Syntax

strins (string1, position, string2)

説明

string1	任意の文字列。
string2	任意の文字列。
position	整数番号。

注意

1. 文字列の先頭文字の位置は1です。

2. BULKデータタイプでは、機能しません。BULKデータタイプで 同一の処理を行なうには、substr()関数とconcatを使用します。

3. NULL値は処理しません。

例

以下は、"abcd"を返します。

   strins ("ad", 2, "bc")

指定された文字列の、指定開始位置から、指定長さの文字列を抜き出し返します。

Syntax

substr (string, position [, length ])

説明

string	任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。
position	整数番号。
length	整数番号。省略値は文字列の長さ。

注意

1. 有効なデータタイプは、CHAR、TEXT、BULKです。

2. NULL値は処理しません。

例

以下は、"bcde"を返します。

   substr ("abcde", 2)

以下は、"bc"を返します。

   substr ("abcde", 2, 2)

以下は、"4XX56"を返します。

   strins (subtr ("12345678910", 4, 3), 2, "XX")

指定された文字列の英字部分を、全て小文字に変換して返します。

Syntax

tolower (string)

説明

string

任意の文字列、またはCHARACTER、TEXTデータタイプのフィールド。

例

以下は、"foobar7"を返します。

   tolower ("FooBar7")

指定された文字列の英字部分を、全て大文字に変換して返します。

Syntax

toupper (string)

説明

string

任意の文字列、またはCHARACTER、TEXTデータタイプの フィールド。

注意

NULL値は処理しません。

例

以下は、"FOOBAR7"を返します。

   toupper ("Foobar 7")

指定された数値を、指定された小数部の桁数で切り捨てます。

Syntax

trunc (number [, digits])

説明

number	任意の数値、数値フィールド、式の結果。
digits	小数位の後の数字の数。それは正の数または負の数になります。省略値は0です。

注意

NULL値は処理しません。

例

以下は、12.00を返します。

   trunc (12.345)

以下は、12.34を返します。

   trunc (12.345, 2)

以下は、12500.00を返します。

   trunc (12506.78, -2)

以下は、30.00を返します。

   trunc (10+20.25)

使用可能な数学関数は、システムに依存します。使用できる関数は、以下に示す関数のサブセットとなります。各関数の 詳細については、使用しているシステムのマニュアルを参照してください。

Table 10-2: 数学関数