Sql com parametro em tempo de execu��o

Delphi

04/10/2005

Utilizao ADO e SQL_SERVER
Gostaria de montar e sql abaixo em tempo de execu��o.
Fa�o o teste para ver se o combobox do laboratiorio esta preenchido se sim executa o codigo e assim por diante.

if dblookupcombobox1.text<>�� then
begin
dmc3.QconsultaFormacaoCurso.SQL.Add(�and MT.laboratorio=:plab�);
dmc3.QconsultaFormacaoCurso.ParamByName(�pcurso�).AsInteger:=DBLookupComboBox1.Keyvalue;
end

Outro problema - nao estou conseguindo passar parametro para a seguinte instrucao
(dataref between �03/08/2005� and �03/09/2005�)
tentei
(dataref between =:pdataInicial and =:pdatafinal

select
MT.codigo,
MT.descricao,
MT.apresentacao,
(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref>=:pdatainicial1 order by codigo asc) as estoque_inicial,
(
(select sum(qtde) from mt_movimentacao where (tipo=�E� or tipo=�D�) and (dataref between �03/08/2005� and �03/09/2005�) and (material=MT.codigo))-(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref>=:pdatafinal21 order by codigo asc)
) entrada,
(select sum(qtde) from mt_movimentacao where (tipo=�S� or tipo=�X�) and (dataref between �03/08/2005� and �03/09/2005�) and (material=MT.codigo)) saida ,
(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref<=:pdatafinal4 order by codigo desc) as estoque_final,
(select minimo from mt_materialcomp where material=MT.codigo ) as estoque_minimo

from
mt_material as MT
where
(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref<=�2005-09-20� order by codigo desc)>0

)

Brahma

Curtidas 0

Respostas

Rjun

04/10/2005

Por que vc precisa montar essa query dinamicamente?

GOSTEI 0

Brahma

04/10/2005

Aos interessados esta ai a solucao!

dmconsulta.QconsultaConsumomedio.close;
dmconsulta.QconsultaConsumomedio.SQL.Clear;
dmconsulta.QconsultaConsumomedio.SQL.Add(� select MT.codigo, MT.descricao,MT.apresentacao,�);
dmconsulta.QconsultaConsumomedio.SQL.Add(� (select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref >= :pdatainicial1 order by codigo asc) as estoque_inicial,�);
dmconsulta.QconsultaConsumomedio.SQL.Add(� ((select sum(qtde) from mt_movimentacao where (tipo=�+quotedstr(�E�)+�or tipo=�+quotedstr(�D�)+�) and (dataref >= :pdatainicial2 and dataref <= :pdatafinal2) and �);
dmconsulta.QconsultaConsumomedio.SQL.Add(� (material=MT.codigo))-(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref >= :pdatafinal21 order by codigo asc)) entrada, �);
dmconsulta.QconsultaConsumomedio.SQL.Add(�(select sum(qtde) from mt_movimentacao where (tipo=�+quotedstr(�S�)+� or tipo=�+quotedstr(�X�)+�) and (dataref >= :pdatainicial3 and dataref <=:pdatafinal3) and (material=MT.codigo)) saida , �);
dmconsulta.QconsultaConsumomedio.SQL.Add(�(select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref<= :pdatafinal4 order by codigo desc) as estoque_final, �);
dmconsulta.QconsultaConsumomedio.SQL.Add(� (select minimo from mt_materialcomp where material=MT.codigo ) as estoque_minimo �);
dmconsulta.QconsultaConsumomedio.SQL.Add(� from mt_material as MT where (select top 1 qtdereal from mt_movimentacao where material=MT.codigo and dataref <=:pdatafinal5 order by codigo desc)>0 �);

if DBLookupComboBox1.Text <> �� then
begin
dmconsulta.QconsultaConsumomedio.SQL.Add(�and MT.codigo =:pmaterial�);
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pmaterial�).value :=DBLookupComboBox1.KeyValue;
end;

if DBLookupComboBox2.Text <> �� then
begin
dmconsulta.QconsultaConsumomedio.SQL.Add(�and MT.subgrupo =:psubgrupo�);
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�psubgrupo�).value :=DBLookupComboBox2.KeyValue;
end;

if DBLookupComboBox3.Text <> �� then
begin
dmconsulta.QconsultaConsumomedio.SQL.Add(�and MT.laboratorio =:plaboratorio�);
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�plaboratorio�).value :=DBLookupComboBox3.KeyValue;
end;

//datas
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdataInicial1�).value :=DateTimePicker1.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatainicial2�).value :=DateTimePicker1.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatafinal2�).value :=DateTimePicker2.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatafinal21�).value :=DateTimePicker2.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatainicial3�).value :=DateTimePicker1.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatafinal3�).value :=DateTimePicker2.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatafinal4�).value :=DateTimePicker2.Date;
dmconsulta.QconsultaConsumoMedio.Parameters.ParamByName(�pdatafinal5�).value :=DateTimePicker2.Date;

showmessage(dmconsulta.QconsultaConsumomedio.SQL.Text);
dmconsulta.QconsultaConsumomedio.open;

GOSTEI 0

POSTAR