Need to know how to set a cell to blank when the value is an integer like  relace 0 and 0.00 with a blank

westdh
westdh used Ask the Experts™
on
Edit 66  Cambridge Systematics, Inc.                 xxxxxum@caxxys.com       25.00  pnd  Christopher Wornum           1/21/2010  
Edit 82  Sensible Environmental Solutions Inc.  xxxxta@sexxxxleinc.net     50.00  pnd   Robert Sutton                     1/22/2010  
Edit   0                                                                                                           0.00           Christa Johnson                  1/22/2010  
Edit 86  PGAdesign                                             xxxxwxn@pxxxesxn.com  50.00  pnd   Maura Baldwin                     1/22/2010  
Edit   0                                                                                                           0.00           Cindy Angers                       1/22/2010  
===================================================================================

this is the sql code

Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, case when rn = 1 then cast(intRegistrationID as varchar) else char(32) end as intRegistrationID, case when rn = 1 then strEmail else char(32) end as strEmail, case when rn = 1 then cast(fltOrderAmount as varchar) else char(32) end as fltOrderAmount, case when rn = 1 then cast(strRegistrationStatusID as varchar) else char(32) end as strRegistrationStatusID, case when rn = 1 then cast(dtmCreated as varchar) else char(32) end as  dtmCreated from ( SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated  FROM i2Integration_EventRegv45_Event e, i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a"  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
this is the code that refers to the question
Top Expert 2012

Commented:
>>this is the code that refers to the question<<
I am sorry but I cannot see it.  Do you want to try again?
Top Expert 2012

Commented:
Never mind I see it now. :)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

you can directly do it from SQL Query itself.

SELECT case when NumberValue<=0 then ' ' else cast(NumberValue as varchar) end as NumberValue FROM table
Top Expert 2012

Commented:
Instead of something like this:
        CASE WHEN rn = 1 THEN CAST(intRegistrationID AS varchar)
             ELSE CHAR(32)
        END AS intRegistrationID,

Do this:
        CASE WHEN rn = 1 THEN 
				CASE
					WHEN intRegistrationID != 0 THEN CAST(intRegistrationID AS varchar(20))
					ELSE SPACE(1)
				END
             ELSE SPACE(1)
        END intRegistrationID,

Or:
        CASE WHEN rn = 1 THEN 
				CASE
					WHEN intRegistrationID != 0 THEN CAST(intRegistrationID AS varchar(20))
					ELSE ''
				END
             ELSE ''
        END intRegistrationID,

Open in new window

Author

Commented:
that work for the integer but not for the money field 0.00
Top Expert 2012
Commented:
It worked for me.

Author

Commented:
It work for the integer.
I tried again for the money field 0.00
and it still di not work.

Thanks
Top Expert 2012

Commented:
>>It work for the integer.
I tried again for the money field 0.00
and it still di not work.
<<
Then post your current query and we can see what it is you are doing wrong.

Author

Commented:
this is my current query the code for the fltOrderAmount is the one that prints 0.00
This scrip is not modify to fix it.
The integer intRegistrationID filed his modfied and it works...thanks
=====================================================
Dim strSelectCommand As String = "select case when rn = 1 then strName else char(32) end as strName, strRegistrationUserName, CASE WHEN rn = 1 THEN CASE WHEN intRegistrationID != 0 THEN CAST(intRegistrationID AS varchar(20)) ELSE SPACE(1) END ELSE SPACE(1) END intRegistrationID, case when rn = 1 then strEmail else char(32) end as strEmail, case when rn = 1 then fltOrderAmount else char(32) end as fltOrderAmount, case when rn = 1 then strRegistrationStatusID else Null end as strRegistrationStatusID, dtmCreated from ( SELECT r.strName, u.strRegistrationUserName, row_number() over (partition by r.strName order by r.strName) rn, r.intRegistrationID, r.strEmail, r.fltOrderAmount, r.strRegistrationStatusID, r.dtmCreated  FROM i2Integration_EventRegv45_Event e, i2Integration_EventRegv45_RegistrationUser u left outer join i2Integration_EventRegv45_Registration r on r.intRegistrationID = u.intRegistrationID where e.intEventID = r.intEventID and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') and r.strPaymentMethodID = 'Check' and ((r.strRegistrationStatusID = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a order by dtmCreated"
Top Expert 2012

Commented:
That is because you only changed intRegistrationID, you also need to change the code for fltOrderAmount and for any other column your want to make blank for 0, as in change:
case when rn = 1 then fltOrderAmount else char(32) end as fltOrderAmount

To:
CASE WHEN fltOrderAmount != 0 THEN CONVERT(varchar(20), fltOrderAmount, 1) ELSE SPACE(1) END ELSE SPACE(1) END  fltOrderAmount

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial