westdh
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(strRegistrationStatus ID 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_ Registrati onUser u left outer join i2Integration_EventRegv45_ Registrati on 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.strRegistrationStatusI D = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a"
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(strRegistrationStatus
>>this is the code that refers to the question<<
I am sorry but I cannot see it. Do you want to try again?
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
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,
ASKER
that work for the integer but not for the money field 0.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It work for the integer.
I tried again for the money field 0.00
and it still di not work.
Thanks
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.
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.
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_ Registrati onUser u left outer join i2Integration_EventRegv45_ Registrati on 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.strRegistrationStatusI D = 'pnd') OR (r.strRegistrationStatusID = 'cmp' ))) a order by dtmCreated"
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,
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
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
ASKER