SQLの書き方 (ID:37622)

名前

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

本文

sql.add('select');
    sql.add('   sum(age0) as 十歳未満');
    sql.add(' , sum(age10) as 十代');
    sql.add(' , sum(age20) as 二十代');
    sql.add(' , sum(age30) as 三十代');
    sql.add(' , sum(age40) as 四十代');
    sql.add(' , sum(age50) as 五十代');
    sql.add(' , sum(age60) as 六十代');
    sql.add(' , sum(age70) as 七十代');
    sql.add(' , sum(age80) as 八十歳以上');
    sql.add(' from (select');
    sql.add('          1 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month');
    sql.add('        from table1 where age < 10 ');
    sql.add('       union all ');
    sql.add('       select');
    sql.add('          0 as age0');
    sql.add('        , 1 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month');
    sql.add('        from table1 where age between 10 and 19');
    sql.add('      union all');
    sql.add('      select');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 1 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age between 20 and 29 ');
    sql.add('      union all');
    sql.add('      select');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 1 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');    //抜けている
    sql.add('       from table1 where age between 30 and 39 ');
    sql.add('      union all');
    sql.add('      select');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 1 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age between 40 and 49 ');
    sql.add('      union all ');
    sql.add('      select ');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20);   //*全角スペースが入っていた
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 1 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age between 50 and 59 ');
    sql.add('      union all ');
    sql.add('      select');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 1 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age between 60 and 69 ');
    sql.add('       union all ');
    sql.add('       select ');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 1 as age70');
    sql.add('        , 0 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age between 70 and 79');//79unionとくっついていた
    sql.add('       union all ');
    sql.add('       select ');
    sql.add('          0 as age0');
    sql.add('        , 0 as age10');
    sql.add('        , 0 as age20');
    sql.add('        , 0 as age30');
    sql.add('        , 0 as age40');
    sql.add('        , 0 as age50');
    sql.add('        , 0 as age60');
    sql.add('        , 0 as age70');
    sql.add('        , 1 as age80');
    sql.add('        , month ');
    sql.add('       from table1 where age > 80)');
    sql.add('       where month = :hensu_month');
のように記述すれば、後から見るときに見やすくなり、バグの発見に繋がります。
この様にSQLを組み立てた後、sql.savetofileでテキストに落としてSQLPlusで確かめれば、どこがおかしいかよくわかります。

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

戻る