2 Replies Latest reply: Jan 25, 2013 8:57 AM by DiamonEdge RSS

    Sql union all combining Help

    985437
      Hi everyone

      We get data from 200 traffic station everyday and all stations have different table name like (KGM_000000000001_PVR). All stations have same column sorting with same column name. Always we get reports for different wish and we combine stations with union all as shown below example. For all time we combine them with union all and it is a bit hard. Is there a any other easy way to get this like report.

      Levent

      Thanks

      Report code example:
      -----------------------------

      SELECT count(class) from C2.KGM_000000000001_PVR WHERE class=9 and RECTIME BETWEEN TO_DATE('01.01.2012','DD.MM.YYYY') AND TO_DATE('01.01.2013','DD.MM.YYYY')
      union all
      SELECT count(class) from C2.KGM_000000000002_PVR WHERE class=9 and RECTIME BETWEEN TO_DATE('01.01.2012','DD.MM.YYYY') AND TO_DATE('01.01.2013','DD.MM.YYYY')
      union all
      SELECT count(class) from C2.KGM_000000000003_PVR WHERE class=9 and RECTIME BETWEEN TO_DATE('01.01.2012','DD.MM.YYYY') AND TO_DATE('01.01.2013','DD.MM.YYYY')
      union all
      .
      .
      .
      SELECT count(class) from C2.KGM_000000000200_PVR WHERE class=9 and RECTIME BETWEEN TO_DATE('01.01.2012','DD.MM.YYYY') AND TO_DATE('01.01.2013','DD.MM.YYYY')

      -------------------------
        • 1. Re: Sql union all combining Help
          Mike Parr
          Have you considered the possibility of using dynamic SQL? That way you could specify the query once and either build it prior to running or run multiple times changing the table name each time.

          Edited by: Mike Parr on 24-Jan-2013 08:03
          • 2. Re: Sql union all combining Help
            DiamonEdge
            Greets !

            I guess the missing piece of your puzzle is the oracle data dictionnary :)
            select 'SELECT count(class) from '||a.owner||'.'||a.table_name||'WHERE class=9 and RECTIME BETWEEN TO_DATE('''||'01.01.0001'
             ||''',''DD.MM.YYYY'') AND TO_DATE('''||'01.01.0002' ||''',''DD.MM.YYYY'')union all'
            from dba_all_tables a
            where a.table_name like 'YOURFILTER%'
            and a.owner = 'YOUR OPTIONNAL OWNER';
            This should help you print out your result then take out the last line's union all and replace it by a semi-colon *;* and execute it :).

            This should do the trick only using SQL engine. Although I definitly agree with Mike, PL/SQL particularly with DBMS_SQL package can tremendously help automate theses actions :)

            Hope this is accurate and/or helps a bit !

            Diamon