这句话Transact-SQL有什么问题? [英] What is wrong in this sentence Transact-SQL?
问题描述
朋友们,我必须承认transact-sql不是我的强项,这是sql server 2005中的一个字符串,但我想在2012年运行
< pre lang =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',< span class =code-string>' 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 < span class =code-keyword>转换( datetime ,' 05/01/2011' , 103 ) AND 转换( datetime ,' 16/01/2015', 103 )
) AND
(
iif(' '<> ' ',COMPONENTE.Vinculado_Eq LIKE < span class =code-string>' ',COMPONENTE.Vinculado_Eq LIKE ' %') AND
iif (' '<> ' ',COMPONENTE.Vinculado_Sb LIKE ' < span class =code-string>',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(' ' = ' < span class =code-string> M',CORRECTIVOS.Mejora = ' True',
iif(' ' = ' O',CORRECTIVOS.Apoyo = ' 真',
iif(' ' = ' R',CORRECTIVOS.Mejora = ' False' AND CORRECTIVOS.Apoyo = ' False',1 = 1)
)
)
)
ORDER BY CORRECTIVOS.Nro_OTC ASC
如果我退出wheref中的所有iff语句,我必须说查询有效有人可以解释这个问题吗?
查看一下case语句来替换你的iifs。
喜欢这样:
当CORRECTIVOS.Mejora ='True'然后'Mejora'当CORRECTIVOS.Apoyo ='True'然后'Otro'其他'时Reparacion'end AS [Tipo OT]
您不能使用IIF来更改条件中的列,也不能使用它来更改运算符。但是你可以用它来改变条件的值。
我不太明白LIKE语句的逻辑。在另一种情况下,你有
COMPONENTE.Vinculado_Eq LIKE ' '
和另一个
COMPONENTE.Vinculado_Eq LIKE ' %'
这些对我来说都没有意义,因为第一个搜索空字符串而第二个搜索任何东西。
但是如果我们查看条件
iif(' ' = ' C',CORRECTIVOS.Cerrada = < span class =code-string>' True',CORRECTIVOS.Cerrada = ' False')
这可以转换为
CORRECTIVOS.Cerrada = IIF(条件在这里,' True',< span class =code-string>' False')
所以也许where子句应该是(不确定逻辑)
WHERE
CORRECTIVOS.COM = COMPONENTE.Codigo AND
CORRECTIVOS.SUB = COMPONENTE.Vinculado_Sb AND
CORRECTIVOS.EQU = COMPONENTE.Vinculado_Eq AND
(
CORRECTIVOS.Fecha_OTC BETWEEN 转换( datetime ,' 05/01/2011', 103 ) AND 转换( datetime ,' 16/01/2015' , 103 )
) AND
(
COMPONENTE.Vinculado_Eq LIKE iif(' ' <> ' ',' ',' %') AND
COMPONENTE.Vinculado_Sb LIKE iif(' '<> ' ',' ',' %') AND
COMPONENTE.Codigo LIKE iif( ' '<> ' < span class =code-string>',' ',' % ')
) 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')
)
)
)
对于错别字感到抱歉: )
感谢所有人!
Bruno Sprecher
kmoorevs
特别是对于
Tomas Takac,他让我明白了错误
Mika Wendelius,他给了我一个例子
现在运行正常:
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 转换( datetime ,' 05/01/2011', 103 ) AND 转换( datetime ,' 16/01/2015', 103 )
) AND
(
COMPONENTE.Vinculado_Eq LIKE iif(' '<> ' ',' ',' %') AND
COMPONENTE.Vinculado_Sb LIKE iif(' '<> ' ',' ',' %') AND
COMPONENTE.Codigo LIKE iif( ' '<> ' < span class =code-string>',' ',' % ')
) 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',' 错误')
)
ORDER BY CORRECTIVOS.Nro_OTC < span class =code-keyword> ASC
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
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?
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]
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
COMPONENTE.Vinculado_Eq LIKE ''
and in another
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
iif( ''= 'C', CORRECTIVOS.Cerrada = 'True', CORRECTIVOS.Cerrada = 'False')
This could be transformed as
CORRECTIVOS.Cerrada = IIF(condition goes here, 'True', 'False')
So perhaps the where clause should be something like (not certain about the logic)
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 :)
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:
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
这篇关于这句话Transact-SQL有什么问题?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!