how to get more than zero from created view?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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?

SQL

@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

ythisbug

Updated 24-Feb-13 1:48am

v5

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2013-02-24T02:47:00

Solution 1

1. if getting null values then use isnull() function
e.g.

SQL

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!
:)

Posted 24-Feb-13 2:47am

Aarti Meswania

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