Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Change Temporary Table to variable

can someone help me to change the temporary table into a temperary value. save and load the data from memory has the fast response time and reduce the cpu usage.
Select distinct (co.cust_num),do_seq.ref_num
into #TempA
from co 
inner join do_seq on co.co_num = do_seq.ref_num
 
select co.cust_num,co.co_num,count(co.co_num)as Order_Lines,sum(coitem.qty_ordered) as Qty_Ordered
into #TempB
from co
inner join coitem on co.co_num = coitem.co_num
inner join #TempA on co.cust_num = #TempA.cust_num and co.co_num = #TempA.ref_num
group by co.cust_num,co.co_num
 
drop table #TempA
 
select distinct cust_num, 
count(cust_num)as No_of_orders,
sum(order_lines)as No_of_Lines,sum(Qty_Ordered) as TotalQty 
into #TempC 
from #TempB 
Group by cust_num order by cust_num
 
drop table #TempB
 
select * from #tempc
 
drop table #TempC

Open in new window

0
wanlijun
Asked:
wanlijun
1 Solution
 
yuchingCommented:
try this

Select Distinct cust_num,
  Count(cust_num)as No_of_orders,
  Sum(order_lines)as No_of_Lines,
  Sum(Qty_Ordered) as TotalQty
From(
      select co.cust_num,co.co_num,
            count(co.co_num)as Order_Lines,
            sum(coitem.qty_ordered) as Qty_Ordered
      from co
      inner join coitem on co.co_num = coitem.co_num
      inner join (
            Select distinct (co.cust_num),do_seq.ref_num
            from co
            inner join do_seq on co.co_num = do_seq.ref_num
      )tempA on co.cust_num = tempA.cust_num and co.co_num = tempA.ref_num
      group by co.cust_num,co.co_num
)
Group by cust_num order by cust_num
0
 
Anthony PerkinsCommented:
This is what you asked for, however I don't suspect this is the solution you need:

Declare @TempA table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              ref_num varchar(20)
                        )                  

Insert      @TempA (cust_num, ref_num)
Select      distinct
            c.cust_num,
            s.ref_num
from      co c
            inner join do_seq s on c.co_num = s.ref_num

Declare @TempB table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              co_num varchar(20),
                              Order_Lines integer,
                              Qty_Ordered integer
                        )                  

Insert      @TempB (cust_num, co_num, Order_Lines, Qty_Ordered)
select      c.cust_num,
            c.co_num,
            count(*) Order_Lines,
            sum(i.qty_ordered) Qty_Ordered
from      co c
            inner join coitem i on c.co_num = i.co_num
            inner join @TempA a on c.cust_num = a.cust_num and c.co_num = a.ref_num
group by
            c.cust_num,
            c.co_num
 

Declare @TempC table (                                          -- Change data types appropriately
                              cust_num varchar(20),
                              No_of_orders integer,
                              No_of_Lines integer,
                              TotalQty integer
                        )

Insert      @TempC (cust_num, No_of_orders, No_of_Lines, TotalQty)
select      cust_num,
            count(*) No_of_orders,
            sum(order_lines) No_of_Lines,
            sum(Qty_Ordered) as TotalQty
from      @TempB
Group by cust_num
order by cust_num
 
0
 
wanlijunAuthor Commented:
Thanks so much
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now