Click here to Skip to main content
Rate this: bad
good
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 19:48pm
ythisbug2.1K
Edited 24-Feb-13 2:48am
v5
Comments
Sandeep Mewara at 24-Feb-13 4:18am
   
Can you share the complete query you are using?
ythisbug at 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 at 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 at 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 at 25-Feb-13 0:11am
   
Qty is not colum..am getting as Qty
Aarti Meswania at 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 at 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 at 25-Feb-13 0:24am
   
please copy your sql query of view
ythisbug at 25-Feb-13 0:38am
   
sorry that is not view that is function am using in my sp
Aarti Meswania at 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 at 25-Feb-13 1:03am
   
null value is not there ..0 values are there so i dont want to display 0 values..
Aarti Meswania at 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
0 Marcin Kozub 330
1 OriginalGriff 256
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 24 Feb 2013
Copyright © CodeProject, 1999-2014
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