Link to home
Start Free TrialLog in
Avatar of westdh
westdhFlag for United States of America

asked on

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

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"  
Avatar of westdh
westdh
Flag of United States of America image

ASKER

this is the code that refers to the question
Avatar of Anthony Perkins
>>this is the code that refers to the question<<
I am sorry but I cannot see it.  Do you want to try again?
Never mind I see it now. :)
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
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

Avatar of westdh

ASKER

that work for the integer but not for the money field 0.00
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of westdh

ASKER

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

Thanks
>>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.
Avatar of westdh

ASKER

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"
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