Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Do multiple "case" lines run against each line or is each run against the table ?

Status
Not open for further replies.

KaronW

MIS
Oct 7, 2008
12
0
0
US
I have informix 9.40.FC7. I have a 31 million line table that I extract information from. It takes up to 7 days now to finish. I don't know if it runs the 4 "case" lines against each row or if it is running 1 "case" against the 31 million rows and then moves to the next "case". Any ideas? I'd appreciate any assistance! Thanks!

Code:
set isolation to dirty read;

select f.patkey,f.invno,f.orderno,f.dx_orderno
from findiag f
into temp temp1 with no log;

select t.patkey,t.invno,
    case
      when t.orderno = 1 then p.dxcode else " " end diag1,
    case
      when t.orderno = 2 then p.dxcode else " " end diag2,
    case
      when t.orderno = 3 then p.dxcode else " " end diag3,
    case
      when t.orderno = 4 then p.dxcode else " " end diag4
from temp1 t,patdiag p
where t.patkey = p.patkey
  and t.dx_orderno = p.order_no
  and p.active = 'Y'
into temp temp2 with no log;

unload to 'trandiag1.txt'
select * from temp2;    

unload to 'trandiag.txt'
select t.patkey,t.invno,MAX(diag1) as d1,
        MAX(diag2) as d2,
        MAX(diag3) as d3,
        MAX(diag4) as d4
from temp2 t
group by 1,2;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top