Mr xxq94813 one more wish.

Hi,

U did a good job.

It turns out that most of time is spent on the "join". I am afraid to say that table design got some problem.

sales_invoice_mst(inv_no varchar(8),
                 inv_date date,cust_code varchar(4))
sales_invoice_det(inv_no varhcar(8),quantity numeric(12,2),grade varchar(1)

I believe that these 2 tables both have inv_no as primary key, it doesn't make any sense to separate them into 2 tables, then do a join of inv_no on 2 tables (creating a view in this situation doesn't help much).

Strictly speaking, your report is a common data warehouse report, which is already beyond what database can handle.

In Data Warehouse, we set up 3 dimension, cust_code, grade and time dimension, which will easily make your report.

Let me know how u think of this, or anything i can do for u.

Thanks.

Hi, xxq4813

My tables are normal master detail tables.One to may
relation.
I have given you the table in brief from, otherwise
table has got more columns.
You know one customer may purchase product of
different grade on the same date.Therefore customer
and date should be in the master table and product,grade etc should come in the detail table.

So far , with your help, I am able to produce my report
although taking time. I have ginve the report to the
management. So please take time and give me some idea other
than view.
I like sql may be nested may have union all.
I have thought an idea and will try some time.

My idea is
1(step). select cust_code,sum(quantity) alias tqty sales tables
uion all cust_code,sum(qyantity*-1) sales return tables
      Alias  table1
2(step). select cust_code,grade,
    case grade='A' then  sum(quantity) alias qty_a
    case grade='B' and 'C' and 'K' and 'S' (four cases)
    from sales table
    and similarly for sales return table with -1 as
    multiplying factor.
    Alias table2

the above tow tables come in the from clause


3.(Actually first)
select table1.cust_code,sum(table1.tqty),sum(tabl2.qty_A),sum(tabl2.qty_b....
from step1,step2
where step1.cust_code=step2.cust_code

Presently I have a lot of assignments so I will try
the aobve later.

Thanks for your comment.

 

Thanks to Mr David/dtodd from which I learnt
from level select. The following sql is working
and retrieving data within seconds.


select #table1.cust_code,
       sum(#table1.quantity) t_qty,
       sum(#table2.a_qty) a_qty,
       sum(#table2.b_qty) b_qty,
       sum(#table2.c_qty) c_qty,
       sum(#table2.k_qty) d_qty,
       sum(#table2.s_qty) s_qty
from
(select sales_invoice_mst.cust_code as cust_code,
       sum(sales_invoice_det.quantity) as quantity
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code
union all
select sales_return_mst.cust_code,sum(sales_return_det.quantity*-1)
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code) as #table1,
(select sales_invoice_mst.cust_code as cust_code,
       case when sales_invoice_det.grade = 'A' then
            sum(sales_invoice_det.quantity) else 0 end  a_qty,
       case when sales_invoice_det.grade = 'B' then
            sum(sales_invoice_det.quantity) else 0 end  b_qty,
       case when sales_invoice_det.grade = 'C' then
            sum(sales_invoice_det.quantity) else 0 end  c_qty,
       case when sales_invoice_det.grade = 'K' then
            sum(sales_invoice_det.quantity) else 0 end  k_qty,
       case when sales_invoice_det.grade = 'S' then
            sum(sales_invoice_det.quantity) else 0 end  s_qty
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code,
         sales_invoice_det.grade
union all
select sales_return_mst.cust_code,
       case when sales_return_det.grade = 'A' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'B' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'C' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'K' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'S' then
            sum(sales_return_det.quantity*-1) else 0 end
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code, sales_return_det.grade) as #table2      
where #table1.cust_code = #table2.cust_code
group by #table1.cust_code
order by t_qty desc

Any other idea will be appreciated.

Thanks to Mr David/dtodd from which I learnt
from level select. The following sql is working
and retrieving data within seconds.


select #table1.cust_code,
       sum(#table1.quantity) t_qty,
       sum(#table2.a_qty) a_qty,
       sum(#table2.b_qty) b_qty,
       sum(#table2.c_qty) c_qty,
       sum(#table2.k_qty) d_qty,
       sum(#table2.s_qty) s_qty
from
(select sales_invoice_mst.cust_code as cust_code,
       sum(sales_invoice_det.quantity) as quantity
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code
union all
select sales_return_mst.cust_code,sum(sales_return_det.quantity*-1)
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code) as #table1,
(select sales_invoice_mst.cust_code as cust_code,
       case when sales_invoice_det.grade = 'A' then
            sum(sales_invoice_det.quantity) else 0 end  a_qty,
       case when sales_invoice_det.grade = 'B' then
            sum(sales_invoice_det.quantity) else 0 end  b_qty,
       case when sales_invoice_det.grade = 'C' then
            sum(sales_invoice_det.quantity) else 0 end  c_qty,
       case when sales_invoice_det.grade = 'K' then
            sum(sales_invoice_det.quantity) else 0 end  k_qty,
       case when sales_invoice_det.grade = 'S' then
            sum(sales_invoice_det.quantity) else 0 end  s_qty
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code,
         sales_invoice_det.grade
union all
select sales_return_mst.cust_code,
       case when sales_return_det.grade = 'A' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'B' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'C' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'K' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'S' then
            sum(sales_return_det.quantity*-1) else 0 end
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code, sales_return_det.grade) as #table2      
where #table1.cust_code = #table2.cust_code
group by #table1.cust_code
order by t_qty desc

Any other idea will be appreciated.