jeffmace
asked on
Whats best way to insert '0' instead of have a 'Null' value in SQL 2000 database??
If I am doing a update query:
<cfoutput query="countPrevAdPages">
<cfquery name="updatePrevAdPageCoun
UPDATE prevAdSales
SET prevtotal='#totalpages#'
WHERE salesrep = #salesrep# AND issueID = #magazine_id#
</cfquery>
</cfoutput>
and the is no value for a few users, it automatically inserts a NULL value in the database. What is the best way to have it insert a '0' instead of have a null value in the database.
Thanks,
Jeff
<cfoutput query="countPrevAdPages">
<cfquery name="updatePrevAdPageCoun
UPDATE prevAdSales
SET prevtotal='#totalpages#'
WHERE salesrep = #salesrep# AND issueID = #magazine_id#
</cfquery>
</cfoutput>
and the is no value for a few users, it automatically inserts a NULL value in the database. What is the best way to have it insert a '0' instead of have a null value in the database.
Thanks,
Jeff
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
anandkp, the only problem with this is, if the current value of totalpages is lets say 12, and he wants to update it to null (zero actually is what he wants) then your query will not run and update the value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or he could create a trigger.
That is activated by an update to that table.
That if the field contained a null to update it to 0.
I have not written triggers, but i know stored procedure. Somehow it has to pass what records were updated. Then create a stored procedure to update that field.
create procedure update_total
(@record_id in integer)
as
declare @p_total varchar;
begin
cursor read_total local as
select prevtotal
from prevadsales
where id = @record_id
open read_total
fetch read_total into @p_total
if @p_total is null then
update prevtotalsales
set prevtotal = 0
where id = @record_id
end if;
close read_total
deallocate read_total
end;
That is activated by an update to that table.
That if the field contained a null to update it to 0.
I have not written triggers, but i know stored procedure. Somehow it has to pass what records were updated. Then create a stored procedure to update that field.
create procedure update_total
(@record_id in integer)
as
declare @p_total varchar;
begin
cursor read_total local as
select prevtotal
from prevadsales
where id = @record_id
open read_total
fetch read_total into @p_total
if @p_total is null then
update prevtotalsales
set prevtotal = 0
where id = @record_id
end if;
close read_total
deallocate read_total
end;
Hi jeff,
ne-luck with this one ???
K'Rgds
Anand
ne-luck with this one ???
K'Rgds
Anand
hi jeff ...
still working on this one ?
Let me know
K'Rgds
Anand
still working on this one ?
Let me know
K'Rgds
Anand
I think he forget to close the question as it is not a big problem to still working on it.
yeah i know - just asking if ne-thing more is required !
& in the query u chk
<cfif Len(Trim(totalpages))>
<cfquery name="updatePrevAdPageCoun
UPDATE prevAdSales
SET prevtotal='#totalpages#'
WHERE salesrep = #salesrep#
AND issueID = #magazine_id#
</cfquery>
</cfif>
that way u avoid the query being executed as well.
K'Rgds
Anand