>>>Hi to all, I'm trying to create a remote view in SQL Server, this is my SELECT
>>>
>>>
>>>SELECT Domicilios.idDomicilio, LTRIM(Domicilios.bairro) AS colonia,;
>>> LTRIM(Individuo.nome_individuo) AS nombre, Domicilios.idCidade,;
>>> Cidade.descricao AS ciudad,;
>>> LTRIM(Domicilios.logradouro) AS direccion;
>>> FROM ;
>>> dbo.Domicilios Domicilios ;
>>> INNER JOIN dbo.Cidade Cidade ;
>>> ON Domicilios.idCidade = Cidade.idCidade ;
>>> INNER JOIN dbo.Paineis_Domicilios Paineis_domicilios ;
>>> ON Domicilios.idDomicilio = Paineis_domicilios.idDomicilio ;
>>> INNER JOIN dbo.Individuo Individuo ;
>>> ON Domicilios.idDomicilio = Individuo.idDomicilio;
>>> WHERE ( ( ( LEFT(Domicilios.iddomicilio,1) = ( ?Codigo_Pais );
>>> AND Paineis_domicilios.Causa_Saida IS NULL );
>>> AND Individuo.idIndividuo = ( 1 ) );
>>> AND IIF(IsEmpty(?cCodigo_Ciudad),Domicilios.idCidade!="99999999",Domicilios.idCidade = ( ?cCodigo_Ciudad )) );
>>> AND Domicilios.Bairro LIKE ( ?cNombre_colonia );
>>> ORDER BY 3, Domicilios.idDomicilio
>>>
>>>
>>>But I keep getting errors from the SQL ODBC Driver. I use to create Local Views using IIF() in the where Clause without problem, why I can't in a Remote View?.
>>>Thanks
>>>Luis Navas
>>
>>Luis,
>>AFAIK neither IIF() nor ISEMPTY() are supported by SQL server (excluding analysis services).
>>For ISEMPTY: It'd be "" (0 length string or null (if allowed)
>>For IIF check case, coalesce, if..else
>>Cetin
>
>Thanks, I will try.
>Luis
for the IIF try to replace it with CASE here's the sintax
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
.......
DO WHILE .T.
ME.Work()
ENDDO
