Hide zero quantities in sql query

Question

Hide zero quantities in sql query

ann 31613

SSC Rookie

Points: 32
More actions
June 17, 2012 at 10:53 pm

#258183

Hi All,
I have written a html query (please see below) and would like the query to hide On hand Qty which are zero. Can someone help me? :ermm:
CAST ( ( SELECT'center' AS 'td/@align',
td=IUOM.ITEM,'',
'left' AS 'td/@align',
td=ITEM_DESC,'',
'center' AS 'td/@align',
td=LI.COMPANY,'',
'center' AS 'td/@align',
td=LI.LOT,'',
'center' AS 'td/@align',
td=CONVERT(CHAR(10),LI.RECEIVED_DATE,105),'',
'center' AS 'td/@align',
td=CONVERT (CHAR(10),LI.MANUFACTURED_DATE,105),'',
'center' AS 'td/@align',
td=CONVERT (CHAR(10),LI.EXPIRATION_DATE,105),'',
'center' AS 'td/@align',
td=IUOM.QUANTITY_UM,'',
'right' AS 'td/@align',
td=CAST(ROUND(SUM(LI.ON_HAND_QTY)/(IUOM.CONVERSION_QTY),2,1)as DECIMAL (18,0)),'',
'right' AS 'td/@align',
td=CAST(ROUND(DATEDIFF(DAY,LI.MANUFACTURED_DATE,GETDATE())/30.41,2,1) as DECIMAL (18,2)),'',
'right' AS 'td/@align',
td=CAST(ROUND(DATEDIFF(DAY,LI.RECEIVED_DATE,GETDATE())/30.41,2,1) as Decimal (18,2))

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

ann 31613 (6/17/2012)
Hi All,
I have written a html query (please see below) and would like the query to hide On hand Qty which are zero. Can someone help me? :ermm:
CAST ( ( SELECT'center' AS 'td/@align',
td=IUOM.ITEM,'',
'left' AS 'td/@align',
td=ITEM_DESC,'',
'center' AS 'td/@align',
td=LI.COMPANY,'',
'center' AS 'td/@align',
td=LI.LOT,'',
'center' AS 'td/@align',
td=CONVERT(CHAR(10),LI.RECEIVED_DATE,105),'',
'center' AS 'td/@align',
td=CONVERT (CHAR(10),LI.MANUFACTURED_DATE,105),'',
'center' AS 'td/@align',
td=CONVERT (CHAR(10),LI.EXPIRATION_DATE,105),'',
'center' AS 'td/@align',
td=IUOM.QUANTITY_UM,'',
'right' AS 'td/@align',
td=CAST(ROUND(SUM(LI.ON_HAND_QTY)/(IUOM.CONVERSION_QTY),2,1)as DECIMAL (18,0)),'',
'right' AS 'td/@align',
td=CAST(ROUND(DATEDIFF(DAY,LI.MANUFACTURED_DATE,GETDATE())/30.41,2,1) as DECIMAL (18,2)),'',
'right' AS 'td/@align',
td=CAST(ROUND(DATEDIFF(DAY,LI.RECEIVED_DATE,GETDATE())/30.41,2,1) as Decimal (18,2))

Welcome To SSC. 🙂

Yes, would love to help you. But in order to get help, you would need to post the DDL of your tables, some readily consumable Sample Data and the Expected Result Set.

Please have a look at the link in my signature to know how to post DDL, Sample Data and the Expected Result Set.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ann 31613 SSC Rookie Points: 32 More actions · Answer 2

ann 31613

SSC Rookie

Points: 32

June 18, 2012 at 2:26 am

#1502114

Hi Vijay

Managed to solve the issue. Thank you.

vinu512 SSCoach Points: 15729 More actions · Answer 3

ann 31613 (6/18/2012)
Hi Vijay
Managed to solve the issue. Thank you.

You're very welcome.

I'm glad it worked out for you.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉