Information générale
Titre:
String+NULL = NULL but not in SQL Server
SELECT company.name + person.name as desc
FROM employees
LEFT OUTER JOIN company ON employees.compid = company.id
LEFT OUTER JOIN person ON employees.persid = person.id
In SQL Server (query analyzer) name will allways be filled as long as one of the two joins succeed.
In VFP (SPT) name will only be filled if both joins succeed, otherwise name is NULL. (Regardless of ANSI_NULL setting)
I know that String+NULL = NULL in VFP but not in SQL Server!
Even if i modify the statement to the following name will be null in VFP:
SELECT CASE employees.compid WHEN NULL THEN '' ELSE company.name END + CASE employees.persid WHEN NULL THEN '' ELSE person.name as name END AS desc
FROM employees
LEFT OUTER JOIN company ON employees.compid = company.id
LEFT OUTER JOIN person ON employees.persid = person.id
An interesting point is that when i say ... CASE 1 WHEN 1 THEN '*' ELSE .... I do get the asterix but when i say ... CASE NULL WHEN NULL THEN '*' i don't.
Does anybody know what is happening here?
Thanks in advance,
Marco.
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement