empty string to Null

[Alex]

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

[WizKid]

Use...

Select
id_Guest, NULLIF(orderName,'&#39
from v_GuestOrder



------------
Alex at 7/11/01 11:32:59 AM

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

[Jun]

Use the following statements, you will be able to replace your
zero length with NULL (By the way, NULL can not be used to assign a
value in CASE - check the SQL BOL).

SELECT Select
id_Guest,
NULLIF (orderName, '&#39
FROM v_GuestOrder

Check the SQL BOL for how to use NULLIF. Hope this wors for you!

Jun



------------
Alex at 7/11/01 11:32:59 AM

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

[Alex]

Thank a lot
Nullif work just fine
Alex


------------
Jun at 7/11/01 12:09:00 PM

Use the following statements, you will be able to replace your
zero length with NULL (By the way, NULL can not be used to assign a
value in CASE - check the SQL BOL).

SELECT Select
id_Guest,
NULLIF (orderName, '&#39
FROM v_GuestOrder

Check the SQL BOL for how to use NULLIF. Hope this wors for you!

Jun



------------
Alex at 7/11/01 11:32:59 AM

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks