CODEWITHSUNDEEP
sqloracle-database
Sunitha
Sunitha

Reputation: 135

order of columns from the query results

thank you very much as usual support from all of you..

select t.* from (

select * from (
select   'Total#Days in a month : ' as a,
        count(1) as "Total"
       from (
 select trunc(sysdate,'MON')+level-1 as my_date
  from dual
  connect by level <= to_char(last_day(sysdate),'DD')
) a
union
select  'Sundays :' as a,

count(decode(TO_CHAR(my_date,'DY'),'SUN',1)) as sunday
from (
 select trunc(sysdate,'MON')+level-1 as my_date
  from dual
  connect by level <= to_char(last_day(sysdate),'DD')
  )
)
union
select
nvl(NoofDays,'Working Days'),decode(NoofDays,'', to_number(to_char(last_day(sysdate),'dd'))-count(*),nvl(count(*),0))
FROM (SELECT (CASE WHEN activity_type IN( 'SL','AL','2','OL') then 'Leave'
                   WHEN activity_type = 'BH' then 'BankHoliday'
                   WHEN activity_type = 'H'  then 'Holidays'
                   WHEN activity_type = 'IM'  then 'Internal Meeting'
                   WHEN activity_type = 'TR'  then 'Training'
                   WHEN activity_type = 'ISM'  then 'Office Work'
                   WHEN activity_type IN ('CS', '10', 'SAV','CME','9','19','15','20') then 'External Meeting'
                   WHEN activity_type = '12'  then 'Transit'
                   WHEN activity_type IN ('11','H')  then 'Others'
                   WHEN activity_type = '13'  then 'Flu Action day'
                   WHEN activity_type = '14'  then 'Corp. Immunization'
                   WHEN activity_type = '16'  then 'Inclinic Meeting'
                   WHEN activity_type = '17'  then 'UC'
                   WHEN activity_type = '18'  then 'CSD'
                   WHEN activity_type = '19'  then 'ESM'
                   ELSE activity_type   END) as NoofDays
      FROM planner_activity pa
      INNER JOIN employee e ON pa.employee_id=e.employee_id
      INNER JOIN  alignment a ON a.employee_id=e.employee_id
      WHERE a.tenant_id = 500020 and  a.alignment_id in(select alignment_id from alignment where manager_alignment_id=1006387678 )
      and  trunc(start_date_time) between trunc(to_date(&d1,'mm/dd/yyyy')) and trunc(to_date(&d2,'mm/dd/yyyy'))
     ) t
GROUP BY cube(NoofDays)
union
select 'No of Working days as for Vacci MI : 'as a,
 count(*)
from coaching c
inner join employee e on c.employee_id=e.employee_id
inner join alignment a on e.employee_id=a.employee_id
where a.tenant_id=500020 and a.alignment_id in(select alignment_id from alignment where manager_alignment_id=1006387678 )
and c.status='CLOS'
and  trunc(create_date) between trunc(to_date(&&d1,'mm/dd/yyyy')) and trunc(to_date(&&d2,'mm/dd/yyyy'))
union
select 'No Of Calls As Per Vacci MI :' as a,
        count(1)
from    event ev
inner join employee e on e.employee_id=ev.employee_id
inner join alignment a on e.employee_id=a.employee_id
where a.tenant_id=500020 and a.alignment_id in(select alignment_id from alignment where manager_alignment_id=1006387678 )
and  ev.ACCOMPANIED_BY='DM'
and  trunc(start_date_time) between trunc(to_date(&&d1,'mm/dd/yyyy')) and trunc(to_date(&&d2,'mm/dd/yyyy'))
union
select 'No Of Working Days As Per Vacc MI :' as a,
        count(distinct ev.start_date_time)
from    event ev
inner join employee e on e.employee_id=ev.employee_id
inner join alignment a on e.employee_id=a.employee_id
where a.tenant_id=500020 and a.alignment_id in(select alignment_id from alignment where manager_alignment_id=1006387678 )
and  ev.ACCOMPANIED_BY='DM'
and  trunc(start_date_time) between trunc(to_date(&&d1,'mm/dd/yyyy')) and trunc(to_date(&&d2,'mm/dd/yyyy'))
) t
order by 1;

I want to sort the order of the columns..from result of the query

External Meeting    2
Internal Meeting    1
Leave   3
No Of Calls As Per Vacci MI :   32
No Of Working Days As Per Vacc MI : 32
No of Working days as for Vacci MI :    1
Sundays :   4
Total#Days in a month :     30
Training    4
Working Days    20

from the above list i want to give the correct order of the column like below..

Total#Days in a month :     30
Sundays :   4
Leave   3
Internal Meeting    1
External Meeting    2
Training    4
Working Days    20
No Of Working Days As Per Vacc MI : 32
No of Working days as for Vacci MI :    1
No Of Calls As Per Vacci MI :   32

Many Thanks for your help..

sunitha..

Upvotes: 1

Views: 69

Answers (2)

Rajesh Bhat
Rajesh Bhat

Reputation: 811

As you are using UNION it will automatically gets sorted before it displays the results. Hence by replacing UNION with UNION ALL and removing ORDER statement at the end will give you required result.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191295

You can define the order as part of the union construct. Add an extra column to every branch, with a value that reflects the order you want, like:

select a, "Total" from (
select   1 as order_col,
         'Total#Days in a month : ' as a,
...
union
select   2 as order_col,
         'Sundays :' as a,
...
)
order by order_col

It doesn't really matter if the branches are in the final order you want within the union (and, incidentally, you should probably be using union all here as there are no duplicates to suppress?), and they don't seem to be in the order you want now. Just put the right order_col value in each branch. The values don't have to be contiguous. It looks like you'll need another CASE to set the order_col for the noof values, based on the activity type, with the default value set higher if you want Working days to come last in that section.

Just removing the order by 1 would at least require you to change the order of the clauses in the query, but still wouldn't guarantee the result would be in that order - since there is no inherent order without an order by clause.

You don't want select * for the outer query as that would show the order_col value; so instead specify the columns you do want, which seems to be a and "Total".

Upvotes: 0

Related Questions