Click here to Skip to main content
15,392,442 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello friends, I must admit that transact-sql is not my forte, this is a string in sql server 2005 but i want to run in 2012

SQL
SELECT 
	CORRECTIVOS.Nro_OTC AS [Nro OT], 
	CORRECTIVOS.EQU +'-'+ CORRECTIVOS.SUB +'-'+ CORRECTIVOS.COM AS Codigo, 
	COMPONENTE.Descripcion AS Descripcion, 
	CORRECTIVOS.Fecha_OTC AS [Fecha Creacion], 
	CORRECTIVOS.Fecha_Aviso AS [Fecha Aviso], 
	CORRECTIVOS.Comentario_Falla AS [Falla Reportada], 
	CORRECTIVOS.Cerrada AS Cerrada, 
	CORRECTIVOS.Reparacion AS [Reparacion Efectuada], 
	iif(CORRECTIVOS.Mejora = 'True', 'Mejora', iif(CORRECTIVOS.Apoyo = 'True', 'Otro','Reparacion')) AS [Tipo OT] 
FROM CORRECTIVOS, COMPONENTE
WHERE 
	CORRECTIVOS.COM= COMPONENTE.Codigo AND 
	CORRECTIVOS.SUB= COMPONENTE.Vinculado_Sb AND 
	CORRECTIVOS.EQU= COMPONENTE.Vinculado_Eq AND 
	(
		CORRECTIVOS.Fecha_OTC BETWEEN Convert(datetime, '05/01/2011', 103) AND Convert(datetime, '16/01/2015', 103)
	) AND
	(
		iif( '' <> '', COMPONENTE.Vinculado_Eq LIKE '', COMPONENTE.Vinculado_Eq LIKE '%') AND 
		iif( '' <> '', COMPONENTE.Vinculado_Sb LIKE '', COMPONENTE.Vinculado_Sb LIKE '%') AND
		iif( '' <> '', COMPONENTE.Codigo LIKE '', COMPONENTE.Codigo LIKE '%' )
	) AND 
	(
		iif(''= 'A', CORRECTIVOS.Cerrada = 'False', 
			iif( ''= 'C', CORRECTIVOS.Cerrada = 'True', CORRECTIVOS.Cerrada = 'True' OR CORRECTIVOS.Cerrada = 'False')
		)
	) AND
	(
		iif(''='M', CORRECTIVOS.Mejora = 'True', 
			iif(''='O', CORRECTIVOS.Apoyo = 'True', 
				iif(''='R', CORRECTIVOS.Mejora = 'False' AND CORRECTIVOS.Apoyo = 'False', 1=1)
			)
		)
	)
ORDER BY CORRECTIVOS.Nro_OTC ASC


I must say that the query works if i quit all the "iff" statements in "where" clause can someone explain the problem please?
Posted
Comments
[no name] 17-Jan-15 12:19pm
   
And the error message is???
elecsvz 17-Jan-15 12:34pm
   
sorry bruno the error message is: "Incorrect syntax near the keyword 'LIKE'."
[no name] 17-Jan-15 12:38pm
   
No Need to be sorry. Now the question is which of the six "LIKE" :)
elecsvz 17-Jan-15 12:45pm
   
http://i1078.photobucket.com/albums/w482/Eleazar_Celis/Captura_zps58ed6ba3.png~original

in every one
[no name] 17-Jan-15 12:55pm
   
no es capaz de ver el problema, un rato por favor
elecsvz 17-Jan-15 12:58pm
   
:)
[no name] 17-Jan-15 13:06pm
   
ver todos "iif" no tienen sentido para mí, pueden explicar las comparaciones?
Tomas Takac 17-Jan-15 12:46pm
   
You cannot use LIKE inside IIF. It expects value in the true/false part while this is a boolean expression. You have to loose the IIF and do the condition properly using ANDs and ORs.
Tomas Takac 17-Jan-15 12:49pm
   
BTW you realize that '' <> '' is always false, right? Further down you use comparisons the same (wrong) way e.g. iif(''= 'A', CORRECTIVOS.Cerrada = 'False'...
elecsvz 17-Jan-15 12:57pm
   
yes, with what little I know of transact-sql i not understood too, why this query is performed. This is an application that developers they did it with sql server 2005 and the string is built dynamically, and i got it to me to take it to other platforms. I now understand that iff statements in the "where" are not necessary, is true?
Tomas Takac 17-Jan-15 13:01pm
   
The string is built dynamically, that's the key. See my comment below. You want to evaluate the IIF while you are creating the SQL command, not during it's execution. Show the code where you are generating the query.
Tomas Takac 17-Jan-15 12:57pm
   
Is this code generated? I'm asking because of all the literal comparisons '' <> '', ''= 'A', ''='O' etc. Maybe you intended to evaluate the IIF in the "generator" and only write the appropriate condition in the result. Am I right?

Have a look at the case statement to replace your iifs.

Like so:

case when CORRECTIVOS.Mejora = 'True' then 'Mejora' when CORRECTIVOS.Apoyo = 'True' then 'Otro' else 'Reparacion' end AS [Tipo OT]
   
v2
Comments
elecsvz 17-Jan-15 13:01pm
   
thank you very much koorevs for answer me, but that line is fine as it was, work fine. check the capture: http://i1078.photobucket.com/albums/w482/Eleazar_Celis/Captura_zps58ed6ba3.png
kmoorevs 17-Jan-15 13:58pm
   
Just wanted you to know that I learned something from your question! I was not aware that iif worked in SQL. Thank you!
You can't use IIF to change the columns in conditions nor you can use it to change operators. But you can use it to change the values for conditions.

I don't quite understand the logic for the LIKE statements. In another case you have
SQL
COMPONENTE.Vinculado_Eq LIKE ''

and in another
SQL
COMPONENTE.Vinculado_Eq LIKE '%'

Neither of these make sense to me since the first one searches for empty strings and the second one for anything.

But if we have a look at the condition
SQL
iif( ''= 'C', CORRECTIVOS.Cerrada = 'True', CORRECTIVOS.Cerrada = 'False')

This could be transformed as
SQL
CORRECTIVOS.Cerrada = IIF(condition goes here, 'True', 'False')


So perhaps the where clause should be something like (not certain about the logic)
SQL
WHERE 
	CORRECTIVOS.COM= COMPONENTE.Codigo AND 
	CORRECTIVOS.SUB= COMPONENTE.Vinculado_Sb AND 
	CORRECTIVOS.EQU= COMPONENTE.Vinculado_Eq AND 
	(
		CORRECTIVOS.Fecha_OTC BETWEEN Convert(datetime, '05/01/2011', 103) AND Convert(datetime, '16/01/2015', 103)
	) AND
	(
		COMPONENTE.Vinculado_Eq LIKE iif( '' <> '', '', '%') AND 
		COMPONENTE.Vinculado_Sb LIKE iif( '' <> '', '', '%') AND
		COMPONENTE.Codigo LIKE iif( '' <> '', '', '%')
	) AND 
	(
		CORRECTIVOS.Cerrada = iif(''= 'A', 'False', 'True') OR
                CORRECTIVOS.Cerrada = iif(''= 'C', 'False', 'False')
		)
	) AND
	(
		CORRECTIVOS.Mejora = iif(''='M', 'True', 'False') AND 
                CORRECTIVOS.Apoyo = iif(''='O', 'True', 'False')
			)
		)
	)

Sorry about the typos :)
   
v2
thanks to all guys!

Bruno Sprecher
kmoorevs

especially to
Tomas Takac, he made me understand the error
Mika Wendelius, He gave me an example

now works fine:
SQL
SELECT 
	CORRECTIVOS.Nro_OTC AS [Nro OT], 
	CORRECTIVOS.EQU +'-'+ CORRECTIVOS.SUB +'-'+ CORRECTIVOS.COM AS Codigo, 
	COMPONENTE.Descripcion AS Descripcion, 
	CORRECTIVOS.Fecha_OTC AS [Fecha Creacion], 
	CORRECTIVOS.Fecha_Aviso AS [Fecha Aviso], 
	CORRECTIVOS.Comentario_Falla AS [Falla Reportada], 
	CORRECTIVOS.Cerrada AS Cerrada, 
	CORRECTIVOS.Reparacion AS [Reparacion Efectuada], 
	iif(CORRECTIVOS.Mejora = 'True', 'Mejora', iif(CORRECTIVOS.Apoyo = 'True', 'Otro','Reparacion')) AS [Tipo OT] 
FROM CORRECTIVOS, COMPONENTE
WHERE 
	CORRECTIVOS.COM= COMPONENTE.Codigo AND 
	CORRECTIVOS.SUB= COMPONENTE.Vinculado_Sb AND 
	CORRECTIVOS.EQU= COMPONENTE.Vinculado_Eq AND 
	(
		CORRECTIVOS.Fecha_OTC BETWEEN Convert(datetime, '05/01/2011', 103) AND Convert(datetime, '16/01/2015', 103)
	) AND
	(
		COMPONENTE.Vinculado_Eq LIKE iif( '' <> '', '', '%') AND 
		COMPONENTE.Vinculado_Sb LIKE iif( '' <> '', '', '%') AND
		COMPONENTE.Codigo LIKE iif( '' <> '', '', '%')
	) AND 
	(
		CORRECTIVOS.Cerrada = iif(''= 'A', 'False', 'True') OR
		CORRECTIVOS.Cerrada = iif(''= 'C', 'False', 'False')
	) AND
	(
		CORRECTIVOS.Mejora = iif(''='M', 'True', 'False') AND 
        CORRECTIVOS.Apoyo = iif(''='O', 'True', 'False')
	)
ORDER BY CORRECTIVOS.Nro_OTC ASC
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900