Click here to Skip to main content
Search within:



Next

how to get more than zero from created view?
Sign Up to vote bad
good
See more: SQL2005SQLview
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:48
ythisbug2.1K
Edited 24 Feb '13 - 1:48
v5
Add a Solution

Comments
Sandeep Mewara - 24 Feb '13 - 4:18
Can you share the complete query you are using?
ythisbug - 24 Feb '13 - 4:54
ok.i updated query

1 solution

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  
Posted 24 Feb '13 - 2:47
Aarti Meswania19.3K
v2
Comments
ythisbug - 25 Feb '13 - 0:03
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:06
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:11
Qty is not colum..am getting as Qty
Aarti Meswania - 25 Feb '13 - 0:14
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:23
am joining all tables to get tat total quantity from different different tables quantity..as am Getting Quantity
Aarti Meswania - 25 Feb '13 - 0:24
please copy your sql query of view
ythisbug - 25 Feb '13 - 0:38
sorry that is not view that is function am using in my sp
Aarti Meswania - 25 Feb '13 - 0:45
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:03
null value is not there ..0 values are there so i dont want to display 0 values..
Aarti Meswania - 25 Feb '13 - 1:16
then use case e.g select case when qty=0 then '' else convert(varchar(max),qty) end as qty
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 300
1 Sergey Alexandrovich Kryukov 215
2 Ron Beyer 109
3 Richard MacCutchan 89
4 Zoltán Zörgő 85
0 Sergey Alexandrovich Kryukov 8,376
1 OriginalGriff 6,571
2 CPallini 3,533
3 Rohan Leuva 2,703
4 Maciej Los 2,234


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