Home » SQL & PL/SQL » SQL & PL/SQL » how to supress zero from query
how to supress zero from query [message #317228] Wed, 30 April 2008 02:10 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts


i made one query for report and report runs successfully and giving me correct results but only one thing that is an issue. see the query below
select a.organization_id, a.subinventory_code, a.inventory_item_id, a.item_cd, a.primary_uom_code, 
a.description, a.item_type, a.item_category, a.item_segment, a.item_cost,

nvl(b.open_baln,0) open_baln,
nvl(c.qty_porv,0)  porcv_qty,
nvl(d.qty_recv,0)  otrcv_qty,
nvl(e.qty_issu,0)  otiss_qty,
nvl(f.qty_cogs,0)   cogs_qty,
nvl((nvl(b.open_baln,0)+nvl(c.qty_porv,0)+nvl(d.qty_recv,0)+nvl(e.qty_issu,0)+nvl(f.qty_cogs,0)),0) clos_baln,
nvl(g.onhd_qty,0)   onhd_qty,

nvl(b.open_baln,0)*nvl(a.item_cost,0) opbl_val, 
nvl(c.qty_porv,0) *nvl(a.item_cost,0) porv_val,
nvl(d.qty_recv,0) *nvl(a.item_cost,0) recv_val,
nvl(e.qty_issu,0) *nvl(a.item_cost,0) issu_val,
nvl(f.qty_cogs,0) *nvl(a.item_cost,0) cogs_val,

(nvl(b.open_baln,0)*nvl(a.item_cost,0))+
(nvl(c.qty_porv,0)*nvl(a.item_cost,0))+
(nvl(d.qty_recv,0)*nvl(a.item_cost,0))+
(nvl(e.qty_issu,0)*nvl(a.item_cost,0))+ 
nvl(f.qty_cogs,0) *nvl(a.item_cost,0) Clbl_val,

nvl(g.onhd_qty,0) *nvl(a.item_cost,0) onhd_val,

nvl((nvl(b.open_baln,0)+nvl(c.qty_porv,0)+nvl(d.qty_recv,0)+nvl(e.qty_issu,0)+nvl(f.qty_cogs,0)),0)- nvl(g.onhd_qty,0) diff_qty,
((nvl(b.open_baln,0)*nvl(a.item_cost,0))+
(nvl(c.qty_porv,0)*nvl(a.item_cost,0))+
(nvl(d.qty_recv,0)*nvl(a.item_cost,0))+
(nvl(e.qty_issu,0)*nvl(a.item_cost,0))+ 
nvl(f.qty_cogs,0) *nvl(a.item_cost,0))-(nvl(g.onhd_qty,0) *nvl(a.item_cost,0)) diff_val
from 
(select 
a.organization_id,
a.SECONDARY_INVENTORY_NAME subinventory_code, 
b.inventory_item_id, b.segment1 item_cd, b.primary_uom_code, b.description,
c.segment1 item_type, c.segment4 item_category, c.segment5 item_segment,
nvl(d.item_cost,0) item_cost
from
mtl_secondary_inventories a,
mtl_system_items b,
(select 
a.inventory_item_id, a.organization_id, a.category_id, 
b.segment1 ,b.segment2 ,b.segment3 ,b.segment4 ,b.segment5
from 
mtl_item_categories a, mtl_categories_b b
where
a.category_id=b.category_id ) c,
cst_item_costs d
where
a.organization_id=b.organization_id and
b.organization_id=c.organization_id and
b.inventory_item_id=c.inventory_item_id and
b.inventory_item_id=d.inventory_item_id and
d.cost_type_id=1 and d.organization_id=104) a,
---- Item Opening Balance 
(select subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) open_baln from mtl_material_transactions 
where 
transaction_date<:P_dt_from
group by  subinventory_code, organization_id, inventory_item_id) b,
---- Item P.O. Recv 
(select  subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) qty_porv from mtl_material_transactions 
where 
transaction_date>=:P_dt_from and transaction_date<=:p_dt_to and
transaction_type_id in (18,36 )
group by  subinventory_code, organization_id, inventory_item_id) c,
---- Item Other Recv
(select subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) qty_recv from mtl_material_transactions 
where 
transaction_date>=:P_dt_from and transaction_date<=:p_dt_to and
transaction_type_id  in ( 2,24,42,52,64) and transaction_quantity>0
--and transaction_quantity>0
group by  subinventory_code, organization_id, inventory_item_id) d,
---- Item issuence --------
(select  subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) qty_issu from mtl_material_transactions 
where 
transaction_date>=:P_dt_from and transaction_date<=:p_dt_to and
transaction_type_id  in ( 2,24,32,52,64) and transaction_quantity<0 
---and transaction_quantity<0
group by  subinventory_code, organization_id, inventory_item_id) e,
---- item_cogs ------------
(select subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) qty_cogs from mtl_material_transactions 
where 
transaction_date>=:P_dt_from and transaction_date<=:p_dt_to and
transaction_type_id  in ( 15, 33) 
group by subinventory_code, organization_id, inventory_item_id) f,
---- Onhand ---------------
(select  subinventory_code, organization_id, inventory_item_id, sum(transaction_quantity) onhd_qty  from MTL_ONHAND_QUANTITIES_DETAIL
where 
date_received<=:p_dt_to 
group by subinventory_code, organization_id, inventory_item_id ) g
where
a.ORGANIZATION_ID=b.organization_id(+) and
a.subinventory_code=b.subinventory_code(+) and
a.INVENTORY_ITEM_ID=b.inventory_item_id(+) and
a.ORGANIZATION_ID=c.organization_id(+) and
a.subinventory_code=c.subinventory_code(+) and
a.INVENTORY_ITEM_ID=c.inventory_item_id(+) and
a.ORGANIZATION_ID=d.organization_id(+) and
a.subinventory_code=d.subinventory_code(+) and
a.INVENTORY_ITEM_ID=d.inventory_item_id(+) and
a.ORGANIZATION_ID=e.organization_id(+) and
a.subinventory_code=e.subinventory_code(+) and
a.INVENTORY_ITEM_ID=e.inventory_item_id(+) and
a.ORGANIZATION_ID=f.organization_id(+) and
a.subinventory_code=f.subinventory_code(+) and
a.INVENTORY_ITEM_ID=f.inventory_item_id(+) and
a.ORGANIZATION_ID=g.organization_id(+) and
a.subinventory_code=g.subinventory_code(+) and
a.INVENTORY_ITEM_ID=g.inventory_item_id(+) and
a.subinventory_code between nvl(:p_subinventory_from, a.subinventory_code) and nvl(nvl(:p_subinventory_to,:p_subinventory_from), a.subinventory_code) and
a.item_cd between nvl(:p_item_cd_from, a.item_cd) and nvl(nvl(:p_item_cd_to, :p_item_cd_from),a.item_cd) and
a.item_type between nvl(:p_item_type_from, a.item_type) and nvl(nvl(:p_item_type_to, :p_item_type_from), a.item_type) and
a.item_category between nvl(:p_item_category_from, a.item_category) and nvl(nvl( :p_item_category_to, :p_item_category_from), a.item_category) and
a.item_segment between nvl(:p_item_segment_from, a.item_segment) and nvl(nvl( :P_item_segment_to, :p_item_segment_from), a.item_segment)

now the question is how can i supperss 0 results in one row. I dont want to display those results which have 0 in all columns
from opening to difference columns. for the results u can see the picture below .


Please help me out of this

regards

anwer
./fa/4203/0/
  • Attachment: rep1.JPG
    (Size: 230.88KB, Downloaded 409 times)
Re: how to supress zero from query [message #317236 is a reply to message #317228] Wed, 30 April 2008 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member

SELECT * 
FROM  (<your current query>)
WHERE (field_1 != 0 and field_2 != 0....)
Re: how to supress zero from query [message #317240 is a reply to message #317236] Wed, 30 April 2008 03:24 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As this question regards Reports Builder, perhaps you could create a FORMAT TRIGGER which would do what JRowbottom suggested:
RETURN NOT(:field_1 = 0 and :field_2 = 0 and ...);
In other words: if all of "field_x" fields are 0, you'd return FALSE and record would not be displayed. Otherwise, format trigger returns TRUE and record is displayed.
Previous Topic: Performance query on where clause whether to provide the argument in begining or at ending statement
Next Topic: Best (fastest) way to get the number of rows from a table?
Goto Forum:
  


Current Time: Sat Dec 10 07:15:32 CST 2016

Total time taken to generate the page: 0.06390 seconds