Union Query question

[hazelsisson]

Hi guys,

I have a union query in my stored procedure as follows, which inserts the results into a database over a database link.

insert into zcmintranet@hrtest( zcmi_con_no, zcmi_car_reg, zcmi_occ_no, zcmi_car_sch, zcmi_car_cc, zcmi_mls_ntx, zcmi_mls_tax )
--Part one of the query:
select u.emp_num, j.reg_num, '01', j.scheme_id, j.cc, (sum(j.total_miles) - sum(j.h2o_miles)) nt, 0 tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and (j.total_miles - j.h2o_miles) > 0
group by u.emp_num, j.scheme_id , j.reg_num, j.cc
union
--Part two of the query:
select u.emp_num, j.reg_num, '02', j.scheme_id, j.cc, 0 nt, sum(j.taxable_miles) tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and j.taxable_miles > 0
group by u.emp_num, j.scheme_id , j.reg_num, j.cc;
commit;

My problem is the occurrence number that's inserted "zcmi_occ_no", which is always "01" for rows from the first part of the query, but could vary for the second part (in the query it's just set to "02" at the moment).

Is there any way I can change the value of this based on whether there are any results from the first part of the query? I would like it to be "01" if there are no rows returned from the first half of the query and "02" if there are 1 or more rows.

Many thanks,
Hazel

 

[carp]

I think that

SELECT emp_num, reg_num, max(THE_LEVEL), scheme_id, cc, nt, tx FROM
(
select u.emp_num, j.reg_num, '01' THE_LEVEL, j.scheme_id, j.cc, (sum(j.total_miles) - sum(j.h2o_miles)) nt, 0 tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and (j.total_miles - j.h2o_miles) > 0
group by u.emp_num, j.scheme_id , j.reg_num, j.cc
union
--Part two of the query:
select u.emp_num, j.reg_num, '02', j.scheme_id, j.cc, 0 nt, sum(j.taxable_miles) tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and j.taxable_miles > 0
group by u.emp_num, j.scheme_id , j.reg_num, j.cc
)
GROUP BY emp_num, reg_num, scheme_id, cc, nt, tx;

should get you close to what you want.

 

[hazelsisson]

Hi Carp,

thanks very much for your suggestion, it looks very close to what I think I need!

I haven't managed to get it to work yet; it still returns the same results as my first try.

E.g. if I run it on some test data which should return one row (which is a result of the second half of the union) the zcmi_occ_no is still '02' rather than '01'.
Maybe this is because the first part of the query has not produced any results, therefore the "Max" function only has '02' to choose from (I tried MIN too).

I will keep fiddling with it, I think I can see the idea behind it but let me know if you have any other suggestions/hints!

(I'm not in tomorrow but will check back the day after).

Thanks again,
Hazel

 

[carp]

Of course! What an idiot I am! If the other fields differ at all from one row to the next, you would get exactly the same results! Still recovering from a heart attack, so I am (obviously) on top of my game yet! Sorry about that, Hazel!

 

[carp]

I think this one should work - hopefully, your unions don't take too much time to run!
Be advised, I haven't run this, so you may need to track down a syntax error or two (e.g., surplus parethesis).

SELECT * FROM 
(
select u.emp_num, j.reg_num, '01' THE_LEVEL, j.scheme_id, j.cc, (sum(j.total_miles) - sum(j.h2o_miles)) nt, 0 tx
  from cm_users u, cm_claims c, cm_journeys j
 where u.emp_num=c.emp_num
   and c.claim_num=j.claim_num
   and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
   and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
   and (j.total_miles - j.h2o_miles) > 0
 group by u.emp_num, j.scheme_id , j.reg_num, j.cc
union
 --Part two of the query:
select u.emp_num, j.reg_num, '02', j.scheme_id, j.cc, 0 nt, sum(j.taxable_miles) tx
  from cm_users u, cm_claims c, cm_journeys j
 where u.emp_num=c.emp_num
   and c.claim_num=j.claim_num
   and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
   and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
   and j.taxable_miles > 0
 group by u.emp_num, j.scheme_id , j.reg_num, j.cc
) v 
WHERE (emp_num, the_level) IN 
(SELECT emp_num, max(the_level) 
 FROM (select u.emp_num, '01' THE_LEVEL
         from cm_users u, cm_claims c, cm_journeys j
        where u.emp_num=c.emp_num
          and c.claim_num=j.claim_num
          and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
          and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
          and (j.total_miles - j.h2o_miles) > 0
        group by u.emp_num
        union
        select u.emp_num, '02'
           from cm_users u, cm_claims c, cm_journeys j
          where u.emp_num=c.emp_num
            and c.claim_num=j.claim_num
            and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
            and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
            and j.taxable_miles > 0
          group by u.emp_num, j.scheme_id , j.reg_num, j.cc
       ) v2
  GROUP BY emp_num
);

 

[hazelsisson]

Hi Carp,

thanks very much for your response.

Unfortunately that still didn't work, but I've managed to find a solution which does work!

I think it's quite long-winded and is probably not very good from a performance point of view, but here it is:

--First part of query
select u.emp_num, j.reg_num, '01', j.scheme_id, j.cc, (sum(j.total_miles) - sum(j.h2o_miles)) nt, 0 tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and (j.total_miles - j.h2o_miles) > 0
group by u.emp_num, j.scheme_id , j.reg_num, j.cc
union
--Second part of query
select 1, payno, pay_date, u.emp_num, j.reg_num, '02', j.scheme_id, j.cc, 0 nt, sum(j.taxable_miles) tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate) 
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
and j.taxable_miles > 0
and u.emp_num in (select u.emp_num 
		from cm_users u, cm_claims c, cm_journeys j
		where u.emp_num=c.emp_num
		and c.claim_num=j.claim_num
		and trunc(j.approval_date) between trunc(sdate) and trunc(edate)  
		and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
		and (j.total_miles - j.h2o_miles) > 0
		group by u.emp_num, j.scheme_id , j.reg_num, j.cc)
group by u.emp_num, j.scheme_id , j.reg_num, j.cc
union
--Third part of query
select u.emp_num, j.reg_num, '01', j.scheme_id, j.cc, 0 nt, sum(j.taxable_miles) tx
from cm_users u, cm_claims c, cm_journeys j
where u.emp_num=c.emp_num
and c.claim_num=j.claim_num
and trunc(j.approval_date) between trunc(sdate) and trunc(edate)  
and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 		
and j.taxable_miles > 0
and u.emp_num not in (select u.emp_num 
		from cm_users u, cm_claims c, cm_journeys j
		where u.emp_num=c.emp_num
		and c.claim_num=j.claim_num
		and trunc(j.approval_date) between trunc(sdate) and trunc(edate)  
		and (j.scheme_id = 'DC' or j.scheme_id = 'DE') 
		and (j.total_miles - j.h2o_miles) > 0
		group by u.emp_num, j.scheme_id , j.reg_num, j.cc)
group by u.emp_num, j.scheme_id , j.reg_num, j.cc;

The first part of the query is unchanged; the second part of the query just retrieves rows where I want zcmi_occ_no to be '02'; and the third part of the query just returns rows where the zcmi_occ_no should be '01'.

I appreciate your help Carp so thanks for spending time on it, I think I'll be able to use your ideas in future queries!

Thanks,
Hazel