Click here to Skip to main content
12,401,984 members (57,434 online)
Rate this:
 
Please Sign up or sign in to vote.
hi
i created view and am using that view in one sp to get sum..but for that i want to get only more than zero..some fields is 0 but i dont want that to display in my report..so can any one suggest me?

@CategoryID int=null
--@DateFrom datetime=null,
--@DateTo datetime=null
AS BEGIN
if  @CategoryID is not null
Begin
SELECT 
(SELECT dbo.udf_GetQty(invID)) Qty,
(SELECT dbo.udf_GetAverageCost(invID)) AvgCost,
(SELECT dbo.udf_GetTotalStockValue(invID)) TotalStockValue,
 tblStockDetails.*,tblStockHeader.*,tblUnitMaster.*,tblInventoryMaster.*
FROM
tblStockDetails
INNER JOIN
tblStockHeader ON tblStockDetails.sdDID = tblStockHeader.shDID AND  
tblStockDetails.sdDocType = tblStockHeader.shDocType
 
left outer join tblUnitMaster on tblStockDetails.sdUnitID=tblUnitMaster.unID 
left outer join tblInventoryMaster on tblStockDetails.sdInventoryID=tblInventoryMaster.invID 
WHERE 
 tblStockHeader.shIsDelete=0 
ORDER BY tblStockHeader.shDocumentDateTime

this am using in my sp so Qty i want More than zero so how can i use this?
for Qty some fields are null so i dont want that zero values.i want more than zero values for Qty

thank you
Posted 23-Feb-13 18:48pm
ythisbug2.1K
Updated 24-Feb-13 1:48am
v5
Comments
Sandeep Mewara 24-Feb-13 4:18am
   
Can you share the complete query you are using?
ythisbug 24-Feb-13 4:54am
   
ok.i updated query

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

1.
if getting null values then use isnull() function
e.g.
select isnull(fldnm,0) --this will give 0 if value is null
2.
if not want rows which having qty = 0 then filter records using where condition

Happy Coding!
Smile | :)
  Permalink  
v2
Comments
ythisbug 25-Feb-13 0:03am
   
thank you..but am using view inside sp..so how can i use dat Qty to filter with where condition
Aarti Meswania 25-Feb-13 0:06am
   
yes if from view you are getting column qty column from select list then you can filter it using where condition
ythisbug 25-Feb-13 0:11am
   
Qty is not colum..am getting as Qty
Aarti Meswania 25-Feb-13 0:14am
   
no problem when execute view
you are getting a table in that table do you have column which have name QTY?

ythisbug 25-Feb-13 0:23am
   
am joining all tables to get tat total quantity from different different tables quantity..as am Getting Quantity
Aarti Meswania 25-Feb-13 0:24am
   
please copy your sql query of view
ythisbug 25-Feb-13 0:38am
   
sorry that is not view that is function am using in my sp
Aarti Meswania 25-Feb-13 0:45am
   
select * from
(

SELECT
--Isnull for convert null val to 0
IsNull((SELECT dbo.udf_GetQty(invID)),0) Qty,
(SELECT dbo.udf_GetAverageCost(invID)) AvgCost,
(SELECT dbo.udf_GetTotalStockValue(invID)) TotalStockValue,
tblStockDetails.*,tblStockHeader.*,tblUnitMaster.*,tblInventoryMaster.*
FROM
tblStockDetails
INNER JOIN
tblStockHeader ON tblStockDetails.sdDID = tblStockHeader.shDID AND
tblStockDetails.sdDocType = tblStockHeader.shDocType

left outer join tblUnitMaster on tblStockDetails.sdUnitID=tblUnitMaster.unID
left outer join tblInventoryMaster on tblStockDetails.sdInventoryID=tblInventoryMaster.invID
WHERE
tblStockHeader.shIsDelete=0
ORDER BY tblStockHeader.shDocumentDateTime

)--this will display records having qty > 0
as a_tbl where qty > 0
ythisbug 25-Feb-13 1:03am
   
null value is not there ..0 values are there so i dont want to display 0 values..
Aarti Meswania 25-Feb-13 1:16am
   
then use case
e.g select case when qty=0 then '' else convert(varchar(max),qty) end as qty

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 24 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100