変換関数と条件式の使用
問５
PRODUCTS表の構造とデータを確認してください。
NAME NULL? TYPE
--------------------- -------- ------------------
PROD_ID NOT NULL NUMBER
PRICE VARCHAR2(15)
PROD_ID PRICE
---------- ---------------
1 $300.25
2 $20.00
3 $5,420.75
あなたは、製品価格に20％の割引を適用し、製品価格と同じ書式で割引後の額を出力する必要があります。必要な結果を得るための最適なSQL文を選択しなさい。
-
a. SELECT prod_id,price*0.8
FROM products; -
b. SELECT prod_id, TO_CHAR(price*(1-0.2),'$999,999.90')
FROM products; -
c. SELECT prod_id, TO_CHAR(TO_NUMBER(price)*0.8,'$9,999.90')
FROM products; -
d. SELECT prod_id, TO_CHAR(TO_NUMBER(price,'$999,999.90')*(1-0.2),'$999,999.90')
FROM products;
変換関数は第4回でしっかり解説しましたので、日付の書式などは皆さんバッチリでしょう。しかし、データ型については復習が必要だと思い、用意したのがこの設問です。
設問のデータ構造からprice列は文字列型であることがわかります。文字列型に対して加減乗除を行うと、次の実行結果のとおりエラーになります（選択肢a不正解）。
SQL> --選択肢a
SQL> SELECT prod_id,price*0.8
2 FROM products;
SELECT prod_id,price*0.8
*
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。
だから、選択肢bのようにTO_CHAR関数の第1引数に当てはめて、書式設定をしようとしたらエラーになります（以下は選択肢bでの実行例）。TO_CHAR関数の第1引数は、日付型か数値型です。
SQL> --選択肢b
SQL> SELECT prod_id,
2 TO_CHAR(price*(1-0.2),'$999,999.90')
3 FROM products;
TO_CHAR(price*(1-0.2),'$999,999.90')
*
行2でエラーが発生しました。:
ORA-01722: 数値が無効です。
文字列型を数値型に変換するときに使うのはTO_NUMBER関数です。対象となる文字列に数値しか含まれていないなら書式の省略は可能ですが、選択肢cのように記号が含まれているならば、書式を指定しないとエラーになります（以下は選択肢cでの実行例）。
SQL> --選択肢c
SQL> SELECT prod_id,
2 TO_CHAR(TO_NUMBER(price)*0.8,'$9,999.90')
3 FROM products;
TO_CHAR(TO_NUMBER(price)*0.8,'$9,999.90')
*
行2でエラーが発生しました。:
ORA-01722: 数値が無効です。
TO_NUMBER関数の第2引数において「先頭に通貨記号があり、3桁ごとにカンマがあり小数点以下は2桁だよ」とOracleに言ってやってはじめて数値に変換され、加減乗除が可能になります。設問では「製品価格と同じ書式で割引後の額を出力する必要」があると言っているので、最後にTO_CHAR関数で書式指定をするのも忘れないでください。これらを満たす選択肢dは正解です（以下は選択肢dでの実行例）。
SQL> --選択肢d
SQL> SELECT prod_id,
2 TO_CHAR(TO_NUMBER(price,'$999,999.90')*(1-0.2),'$999,999.90')
3 FROM products;
PROD_ID TO_CHAR(TO_NUMBER(PRICE,
---------- ------------------------
1 $240.20
2 $16.00
3 $4,336.60
