SQLの書き方 (ID:37612)

名前

ホームページ(ブログ、Twitterなど)のURL (省略可)

本文

いろいろな情報ありがとうございます。
前回書いたsql文は、微調整したら、機能するようになりました。
ただ、where句で、項目のmonthのある一定の値をもっているデータを抽出するようにsql文を組むと、「ORA-01789  問い合わせブロックにある結果の列数が無効です」というエラーがでてしまいます。Delphiフォーム上のTEditに入力した値を項目month(数値型です）が持つデータを抽出したいと思っています。理由お分かりになるかたいらっしゃいますか？

with datamodule1.query1d do
  begin

close;
    sql.clear;
    sql.add('select sum(age0) as 十歳未満, sum(age10) as 十代, 
    sum(age20) as 二十代, sum(age30) as 三十代, sum(age40)
    as 四十代, sum(age50)');
    sql.add('as 五十代, sum(age60) as 六十代, sum(age70) as 七十代,
    sum(age80) as 八十歳以上 from (select 1 as age0, 0 as age10, 0
    as age20, 0 as');

sql.add('age30, 0 as age40, 0 as age50, 0 as age60, 0 as age70, 
    0 as age80 month from table1 where age < 10 union all select
    0  as age0, 1 as age10, 0');

sql.add('as age20, 0 as age30, 0 as age40, 0 as age50, 0 as 
    age60, 0 as age70, 0 as age80, month from table1 where age 
    between 10 and 19  union all');

sql.add('select 0 as age0, 0 as age10, 1 as age20, 0 as age30,
    0 as age40, 0 as age50, 0 as age60, 0 as age70, 0 as age80, 
    month from table1 where age');

sql.add('between 20 and 29 union all select 0 as age0, 0 as
    age10, 0 as age20, 1 as age30, 0 as age40, 0 as age50, 0 as
    age60, 0 as age70, 0 as');

sql.add('age80 from table1 where age between 30 and 39 union all
    select 0 as age0, 0 as age10, 0 as age20, 0 as age30, 1 as 
    age40, 0 as age50');

sql.add(', 0 as age60, 0 as age70, 0 as age80, month from 
    table1 where age between 40 and 49 union all select 0 as age0, 
    0 as age10, 0 as  age20, 0 as');

sql.add('age30, 0 as age40, 1 as age50, 0 as age60, 0 as age70, 
    0 as age80, month from table1 where age between 50 and 59 union
    all select 0 as age0');

sql.add(', 0 as age10, 0 as age20, 0 as age30, 0 as age40, 0 as 
    age50, 1 as age60, 0 as age70, 0 as age80, month from table1
    where age between 60 and 69');

sql.add('union all select 0 as age0, 0 as age10, 0 as age20, 0 
    as age30, 0 as age40, 0 as age50, 0 as age60, 1 as age70, 0 as
    age80, month from table1');

sql.add('where age between 70 and 79union all select 0 as age0, 
    0 as age10, 0 as age20, 0 as age30, 0 as age40, 0 as age50, 0 as 
    age60, 0 as');

sql.add('age70, 1 as age80, month from table1 where age > 80)');

sql.add('where month = :hensu_month');
    parambyName('hensu_month').asInteger := strtoint(editmonth.Text);
    active := true;

←解決時は質問者本人がここをチェックしてください。

戻る