Fórum Problemas ADOQuery #400804
10/05/2011
0
if CBSearch.Items[CBSearch.ItemIndex] = 'Operador' then
begin
Consulta1:= EditSearch.Text;
with DM.ADOQueryMANST do
begin
Close;
SQL.Clear;
SQL.Add('declare @tbTeste table (tempototal varchar(14))');
SQL.Add('insert into @tbTeste select M.TempTotal from TB_MAN M');
SQL.Add('WHERE (Mec1 = ' + QuotedStr(Consulta1));
SQL.Add(' OR Mec2 = ' +QuotedStr(Consulta1));
SQL.Add('OR Mec3 = ' + QuotedStr(Consulta1)+ ' OR Mec4 = ' + QuotedStr(Consulta1));
SQL.Add('OR Mec5 = ' + QuotedStr(Consulta1)+ ' OR Mec6 = ' + QuotedStr(Consulta1));
SQL.Add('OR Mec7 = ' + QuotedStr(Consulta1)+ ' OR Mec8 = ' + QuotedStr(Consulta1));
SQL.Add('OR Mec9 = ' + QuotedStr(Consulta1)+ ') ' );
SQL.Add(';with cte_dados as');
SQL.Add('(select');
SQL.Add('TabHora.ColXML.value('@Ind', 'int') AS Hora,');
SQL.Add('TabMin.ColXML.value('@Ind', 'int') AS Minuto,');
SQL.Add(' TabSeg.ColXML.value('@Ind', 'int') AS Segundo');
SQL.Add('FROM (SELECT *,');
SQL.Add('CONVERT(XML, '<Test Ind="' + Replace(tempototal, ':','"/><Test Ind="') + '"/>') AS ColXML');
SQL.Add('FROM @tbTeste) AS Tab');
SQL.Add('CROSS APPLY Tab.ColXML.nodes('/Test[1]') As TabHora (ColXML)');
SQL.Add('CROSS APPLY Tab.ColXML.nodes('/Test[2]') As TabMin (ColXML)');
SQL.Add('CROSS APPLY Tab.ColXML.nodes('/Test[3]') As TabSeg (ColXML)');
SQL.Add('), cte_soma as(');
SQL.Add('select SUM(hora) 'hora' , SUM(minuto) 'minuto' , SUM(segundo) 'segundo' from ');
SQL.Add('cte_dados)');
SQL.Add('select hora+(minuto/60)as 'hora', case When minuto>59 then minuto%60 else ');
SQL.Add('minuto end as 'minuto',segundo from cte_soma');
Open;
Edit1.Text:= DM.AdoQueryMANST.Fieldbyname('Hor H/Maq').AsString + ':' + Formatfloat('00',StrtoInt(DM.AdoQueryMANST.Fieldbyname('Min H/Maq').AsString));
end;
end; end; OBS: Uso o RAD Studio 2010 Att, Moacir Rachid
Moacir Rachid
Curtir tópico
+ 0Posts
10/05/2011
Rafael Mattos
Gostei + 0
10/05/2011
Moacir Rachid
Checking project dependencies...
Compiling PSYSQUALY.dproj (Debug configuration)
[DCC Error] UGerNC.pas(279): E2029 ')' expected but '@' found
[DCC Error] UGerNC.pas(283): E2038 Illegal character in input file: '"' (#$22)
[DCC Error] UGerNC.pas(283): E2038 Illegal character in input file: '"' (#$22)
[DCC Error] UGerNC.pas(283): E2038 Illegal character in input file: '"' (#$22)
[DCC Error] UGerNC.pas(303): E2029 '.' expected but ';' found
[DCC Hint] UGerNC.pas(33): H2219 Private symbol 'FullTimeToStr' declared but never used
[DCC Hint] UGerNC.pas(34): H2219 Private symbol 'TOTALIZATEMPO' declared but never used
[DCC Fatal Error] PSYSQUALY.dpr(31): F2063 Could not compile used unit 'UGerNC.pas'
Failed
Elapsed time: 00:00:00.7
Output
Build started 10/05/2011 16:48:17.
Gostei + 0
10/05/2011
Rodrigo Mattos
Gostei + 0
10/05/2011
Rafael Mattos
SQL.Add('TabHora.ColXML.value('@Ind', 'int') AS Hora,'); <-- @IND ISSO NÃO DEVERIA TA DENTRO DA STRING?
SQL.Add('TabMin.ColXML.value('@Ind', 'int') AS Minuto,'); <-- @IND ISSO NÃO DEVERIA TA DENTRO DA STRING?
SQL.Add(' TabSeg.ColXML.value('@Ind', 'int') AS Segundo'); <-- @IND ISSO NÃO DEVERIA TA DENTRO DA STRING?
A MESMA COISA AQUI
SQL.Add('CONVERT(XML, '<Test Ind="' + Replace(tempototal, ':','"/><Test Ind="') + '"/>') AS ColXML');
Gostei + 0
10/05/2011
Moacir Rachid
Gostei + 0
10/05/2011
Rafael Mattos
<Test Ind=
isso ta fora da String deveria ser dentro da String
Gostei + 0
10/05/2011
Moacir Rachid
insert into @tbTeste select M.TempTotal from TB_MAN M
WHERE M.OS LIKE '%10%' ;with cte_dados as
(
select TabHora.ColXML.value('@Ind', 'int') AS Hora,
TabMin.ColXML.value('@Ind', 'int') AS Minuto,
TabSeg.ColXML.value('@Ind', 'int') AS Segundo FROM (SELECT *,
CONVERT(XML, '<Test Ind="' + Replace(tempototal, ':','"/><Test Ind="') + '"/>') AS ColXML
FROM @tbTeste) AS Tab
CROSS APPLY Tab.ColXML.nodes('/Test[1]') As TabHora (ColXML)
CROSS APPLY Tab.ColXML.nodes('/Test[2]') As TabMin (ColXML)
CROSS APPLY Tab.ColXML.nodes('/Test[3]') As TabSeg (ColXML)
), cte_soma as(
select SUM(hora) 'hora' , SUM(minuto) 'minuto' , SUM(segundo) 'segundo' from cte_dados) select hora+(minuto/60)as 'hora', case When minuto>59 then minuto%60 else minuto end as 'minuto',segundo from cte_soma
Gostei + 0
11/05/2011
Moacir Rachid
Gostei + 0
11/05/2011
Wilson Junior
Espero ter colaborado.
Gostei + 0
11/05/2011
Moacir Rachid
Gostei + 0
Clique aqui para fazer login e interagir na Comunidade :)