Click here to Skip to main content
15,918,889 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

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]
 
Share this answer
 
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 :)
 
Share this answer
 
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
 
Share this answer
 

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