Menu ▾ ▴

Union All

Open Discussion
Lance
2011-11-07
2012-12-07

  • Lance

    Lance - 2011-11-07

    The sql follow:
    INSERT INTO om_grid_income
      (ent_id,
       ent_nbr,
       ent_name,
       stat_cycle,
       city_id,
       mobile_income,
       -mobile_income_plan,
       -mobile_income_comp,
       -last_mobile_income,
       -mobile_income_srate,
       -mobile_income_rate,
       pstn_income,
       -pstn_income_plan,
       -pstn_income_comp,
       -last_pstn_income,
       -pstn_income_srate,
       -pstn_income_rate,
       net_income,
       -net_income_plan,
       -net_income_comp,
       -last_net_income,
       -net_income_srate,
       -net_income_rate,
       other_income,
       -other_income_plan,
       -other_income_comp,
       -last_other_income,
       -other_income_srate,
       -other_income_rate,
       sum_income_cl,
       -sum_income_cl_plan,
       -sum_income_cl_comp,
       sum_income_zl,
       -sum_income_zl_plan,
       -sum_income_zl_comp,
       -sum_income_plan,
       -sum_income_comp,
       -last_sum_income,
       -sum_income_srate,
       -sum_income_rate,
       sum_income,
       -y_total_incom,
       -y_total_plan,
       -y_total_comp,
       -staff_id,
       -staff_name,
       -staff_tel,
       -team_id,
       -team_name,
       stat_date,
       state,
       create_date,
       mod_staff_id,
       mod_date,
       remark)
      SELECT grid_id,
             NULL cell_nbr,
             grid_name,
             -cast(rec_month_id||'01' as numeric),
             city_id,
             SUM(mobile_income),
             -NULL mobile_income_plan,
             -NULL mobile_income_comp,
             -NULL last_mobile_income,
             -NULL mobile_income_srate,
             -NULL mobile_income_rate,
             SUM(pstn_income),
             -NULL pstn_income_plan,
             -NULL pstn_income_comp,
             -NULL last_pstn_income,
             -NULL pstn_income_srate,
             -NULL pstn_income_rate,
             SUM(net_income),
             -NULL net_income_plan,
             -NULL net_income_comp,
             -NULL last_net_income,
             -NULL net_income_srate,
             -NULL net_income_rate,
             SUM(other_income),
             -NULL other_income_plan,
             -NULL other_income_comp,
             -NULL last_other_income,
             -NULL other_income_srate,
             -NULL other_income_rate,
             SUM(sum_income_cl),
             -NULL sum_income_cl_plan,
             -NULL sum_income_cl_comp,
             SUM(sum_income_zl),
             -NULL sum_income_zl_plan,
             -NULL sum_income_zl_comp,
             -NULL sum_income_plan,
             -NULL sum_income_comp,
             -NULL last_sum_income,
             -NULL sum_income_srate,
             -NULL sum_income_rate,
             SUM(income),
             -NULL y_total_incom,
             -NULL y_total_plan,
             -NULL y_total_comp,
             now() AS stat_date,
             'S0A' AS state,
             now() AS create_date,
             city_id AS mod_staff_id,
             now() AS mod_date,
             NULL AS remark
        FROM c_stat_serv_quantity_charge_m
        where grid_id >0 and rec_month_id = MONTH AND city_id = CITY and income_flag>0
       GROUP BY grid_id, grid_name, rec_month_id, city_id
      UNION ALL
      SELECT rgrid_id,
             NULL cell_nbr,
             rgrid_name,
             cast(rec_month_id||'01' as numeric),
             city_id,
             SUM(mobile_income),
             -NULL mobile_income_plan,
             -NULL mobile_income_comp,
             -NULL last_mobile_income,
             -NULL mobile_income_srate,
             -NULL mobile_income_rate,
             SUM(pstn_income),
             -NULL pstn_income_plan,
             -NULL pstn_income_comp,
             -NULL last_pstn_income,
             -NULL pstn_income_srate,
             -NULL pstn_income_rate,
             SUM(net_income),
             -NULL net_income_plan,
             -NULL net_income_comp,
             -NULL last_net_income,
             -NULL net_income_srate,
             -NULL net_income_rate,
             SUM(other_income),
             -NULL other_income_plan,
             -NULL other_income_comp,
             -NULL last_other_income,
             -NULL other_income_srate,
             -NULL other_income_rate,
             SUM(sum_income_cl),
             -NULL sum_income_cl_plan,
             -NULL sum_income_cl_comp,
             SUM(sum_income_zl),
             -NULL sum_income_zl_plan,
             -NULL sum_income_zl_comp,
             -NULL sum_income_plan,
             -NULL sum_income_comp,
             -NULL last_sum_income,
             -NULL sum_income_srate,
             -NULL sum_income_rate,
             SUM(income),
             -NULL y_total_incom,
             -NULL y_total_plan,
             -NULL y_total_comp,
             now() AS stat_date,
             'S0A' AS state,
             now() AS create_date,
             city_id AS mod_staff_id,
             now() AS mod_date,
             NULL AS remark
        FROM c_stat_serv_quantity_charge_m
       where rgrid_id  >0 and rec_month_id = MONTH AND city_id = CITY and income_flag>0
       GROUP BY rgrid_id, rgrid_name, rec_month_id, city_id
       UNION ALL
      SELECT vgrid_id,
             NULL cell_nbr,
             vgrid_name,
             cast(rec_month_id||'01' as numeric),
             city_id,
             SUM(mobile_income),
             -NULL mobile_income_plan,
             -NULL mobile_income_comp,
             -NULL last_mobile_income,
             -NULL mobile_income_srate,
             -NULL mobile_income_rate,
             SUM(pstn_income),
             -NULL pstn_income_plan,
             -NULL pstn_income_comp,
             -NULL last_pstn_income,
             -NULL pstn_income_srate,
             -NULL pstn_income_rate,
             SUM(net_income),
             -NULL net_income_plan,
             -NULL net_income_comp,
             -NULL last_net_income,
             -NULL net_income_srate,
             -NULL net_income_rate,
             SUM(other_income),
             -NULL other_income_plan,
             -NULL other_income_comp,
             -NULL last_other_income,
             -NULL other_income_srate,
             -NULL other_income_rate,
             SUM(sum_income_cl),
             -NULL sum_income_cl_plan,
             -NULL sum_income_cl_comp,
             SUM(sum_income_zl),
             -NULL sum_income_zl_plan,
             -NULL sum_income_zl_comp,
             -NULL sum_income_plan,
             -NULL sum_income_comp,
             -NULL last_sum_income,
             -NULL sum_income_srate,
             -NULL sum_income_rate,
             SUM(income),
             -NULL y_total_incom,
             -NULL y_total_plan,
             -NULL y_total_comp,
             now() AS stat_date,
             'S0A' AS state,
             now() AS create_date,
             city_id AS mod_staff_id,
             now() AS mod_date,
             NULL AS remark
        FROM c_stat_serv_quantity_charge_m
       where vgrid_id  >0 and rec_month_id = MONTH AND city_id = CITY and income_flag>0
       GROUP BY vgrid_id, vgrid_name, rec_month_id, city_id
       UNION ALL
      SELECT cgrid_id,
             NULL cell_nbr,
             cgrid_name,
             cast(rec_month_id||'01' as numeric),
             city_id,
             SUM(mobile_income),
             -NULL mobile_income_plan,
             -NULL mobile_income_comp,
             -NULL last_mobile_income,
             -NULL mobile_income_srate,
             -NULL mobile_income_rate,
             SUM(pstn_income),
             -NULL pstn_income_plan,
             -NULL pstn_income_comp,
             -NULL last_pstn_income,
             -NULL pstn_income_srate,
             -NULL pstn_income_rate,
             SUM(net_income),
             -NULL net_income_plan,
             -NULL net_income_comp,
             -NULL last_net_income,
             -NULL net_income_srate,
             -NULL net_income_rate,
             SUM(other_income),
             -NULL other_income_plan,
             -NULL other_income_comp,
             -NULL last_other_income,
             -NULL other_income_srate,
             -NULL other_income_rate,
             SUM(sum_income_cl),
             -NULL sum_income_cl_plan,
             -NULL sum_income_cl_comp,
             SUM(sum_income_zl),
             -NULL sum_income_zl_plan,
             -NULL sum_income_zl_comp,
             -NULL sum_income_plan,
             -NULL sum_income_comp,
             -NULL last_sum_income,
             -NULL sum_income_srate,
             -NULL sum_income_rate,
             SUM(income),
             -NULL y_total_incom,
             -NULL y_total_plan,
             -NULL y_total_comp,
             now() AS stat_date,
             'S0A' AS state,
             now() AS create_date,
             city_id AS mod_staff_id,
             now() AS mod_date,
             NULL AS remark
        FROM c_stat_serv_quantity_charge_m
       where cgrid_id  >0 and rec_month_id = MONTH AND city_id = CITY and income_flag>0
       GROUP BY cgrid_id, cgrid_name, rec_month_id, city_id;

    the exception follows:
    net.sf.jsqlparser.JSQLParserException
    at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)
    at com.wds.jsqlparse.analysis.InsertAnalysis.analyse(InsertAnalysis.java:48)
    at com.wds.jsqlparse.SQLParser.parse(SQLParser.java:33)
    at com.wds.jsqlparse.SQLParserTest2.main(SQLParserTest2.java:48)
    Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "UNION" "UNION "" at line 110, column 3.
    Was expecting one of:
        <EOF>
        "LIMIT" …
        "ORDER" …
        "OFFSET" …
        "HAVING" …
        ";" …
        "," …
        ")" …
        "." …
        "||" …
        "^" …
        "." …
        "." …
        "^" …
        "||" …
        "," …
        "HAVING" …
        "ORDER" …
        "LIMIT" …
        "OFFSET" …
        "LIMIT" …
        "." …
        "." …
        "^" …
        "||" …
        "." …
        "." …
        "^" …
        "." …
        "." …
       
    at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:6447)
    at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:6306)
    at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:148)
    at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
    … 3 more
    Caused by:
    net.sf.jsqlparser.parser.ParseException: Encountered " "UNION" "UNION "" at line 110, column 3.
    Was expecting one of:
        <EOF>
        "LIMIT" …
        "ORDER" …
        "OFFSET" …
        "HAVING" …
        ";" …
        "," …
        ")" …
        "." …
        "||" …
        "^" …
        "." …
        "." …
        "^" …
        "||" …
        "," …
        "HAVING" …
        "ORDER" …
        "LIMIT" …
        "OFFSET" …
        "LIMIT" …
        "." …
        "." …
        "^" …
        "||" …
        "." …
        "." …
        "^" …
        "." …
        "." …

     

  • Lance

    Lance - 2011-11-07

    how can I resovle?

     

Log in to post a comment.