Problem with tabel input ->text file output

[leomuler]

Hi!

I´ve two step: tabel input -> text file output.
At table input i´m using this SQL instruction (SQL Server):

"
with
baixados as (
select
PM.cdProcesso,
max(PM.nuSeqDistrib) as nuSeqDistrib,
convert(DateTime, substring(max(convert(char, PM.dtMovimento,120) + substring(replicate('0',5),1,5-len(nuSeqDistrib))+convert(char,PM.nuSeqProcessoMv) + PM.cdSituacaoProcesso), 1, 10),120) as dtContabilizacao
from
saj.efpgProcessoMv PM
where
PM.cdSituacaoProcesso in ('B', 'R') and
PM.dtExclusao is null and
PM.dtMovimento <= convert(datetime,getdate(),120)
group by
PM.cdProcesso
having right(max(convert(char, PM.dtMovimento) + substring(replicate('0',5),1,5-len(nuSeqDistrib))+convert(char,PM.nuSeqProcessoMv) + PM.cdSituacaoProcesso), 1)='B'
),

todos as (
select
PM.cdProcesso,
max (PM.nuSeqDistrib) as nuSeqDistrib
from
saj.efpgProcessoMv PM
join saj.efpgProcesso P on P.cdProcesso = PM.cdProcesso and P.cdSituacaoProcesso <> 'C'
where
PM.dtExclusao is null and
PM.dtMovimento <= convert(datetime,getDate() ,120)
group by
PM.cdProcesso
),

todos2 as (
select
TM.cdProcesso,
substring(TM.cdProcesso, 1, 9) + '0000' as cdProcessoP,
case when BX.cdProcesso is null then TM.nuSeqDistrib else BX.nuSeqDistrib end as nuSeqDistrib,
case when BX.cdProcesso is null then convert(datetime, getDate(),120) else BX.dtContabilizacao end as dtContabilizacao

from
todos TM
left outer join baixados BX on
BX.cdProcesso = TM.cdProcesso
),
fim as (
select
D.cdForo,
D.cdVara,
P2.flArea,
P2.cdClasseProcesso,
coalesce(P2.cdJuiz,-1) as cdMagistrado,
coalesce(A.cdAssunto,-1) as cdAssunto,
coalesce(O.cdClasseOrigem, -1) as cdClasseOrigem,
P2.cdSituacaoProcesso,
(case when C.flCarga = 'R' then C.cdTipoLocalDestino else coalesce(C.cdTipoLocalOrigem, -1) end) as cdTipoLocal,
(case when C.flCarga = 'R' then C.cdLocalDestino else coalesce(C.cdLocalOrigem, -1) end) as cdLocal,
min(D2.dtDistribuicao) as dtProcMaisAntigo,
AVG(ABS(datediff(day, coalesce(P.dtRecebimento, D1.dtDistribuicao) , todos2.dtContabilizacao))) as Media

from
todos2
left outer join saj.efpgDistProcesso D on
D.cdProcesso = todos2.cdProcessoP and
D.nuSeqDistrib = todos2.nuSeqDistrib

left outer join saj.efpgDistProcesso D1 on
D1.cdProcesso = D.cdProcesso and
D1.nuSeqDistrib = 1

left outer join saj.efpgProcesso P on
P.cdProcesso = todos2.cdProcesso

join saj.efpgProcesso P2 on
P2.cdProcesso = todos2.cdProcesso and P2.cdSituacaoProcesso in ('T','J','D','U')

join saj.efpgDistProcesso D2 on
D2.cdProcesso = P2.cdProcesso and
D2.nuSeqDistrib = 1

left outer join saj.esajProcAssunto A on
A.cdProcesso = P.cdProcesso and
A.flPrincipal = 'S'

left outer join saj.efsgOrigem1Grau O on
O.cdProcesso = P.cdProcesso and
O.cdTipoOrigem = 1

left outer join saj.ecgoUltimaCarga C on
C.cdObjeto = P.cdObjeto

where
todos2.nuSeqDistrib is not null
group by
D.cdForo, D.cdVara, P2.flArea, P2.cdClasseProcesso, P2.cdJuiz, A.cdAssunto, O.cdClasseOrigem,
P2.cdSituacaoProcesso, C.flCarga, c.cdTipoLocalDestino, c.cdTipoLocalOrigem, c.cdLocalDestino, c.cdLocalOrigem
)

select
-- ?,
-- ?,
cdForo,
cdVara,
flArea,
CASE WHEN flArea = '1' THEN 'Civil' ELSE CASE WHEN flArea = '2' THEN 'Criminal' ELSE 'Ambas' END END AS deArea,
cdClasseProcesso,
cdMagistrado,
cdAssunto,
cdClasseOrigem as cdAcao,
cdSituacaoProcesso,
cdTipoLocal,
cdLocal,
min(dtProcMaisAntigo)as dtProcMaisAntigo,
(sum(Media)/count(*)) as Media
from fim
group by
cdForo,
cdVara,
flArea,
cdClasseProcesso,
cdSituacaoProcesso,
cdMagistrado,
cdAssunto,
cdClasseOrigem,
cdTipoLocal,
cdLocal "

The next step it´s a Text File output, and this step is isn´t recognizing the fileds from the table input. It´s because the SELECT...FROM must be the first command? What can i do to resolve this? Thank´s

[JeffBeard]

Here are some things to try:

1. Right-click on the Table Input step and select "Output" to make sure that there are fields visible. If not then the SQL query isn't working.
2. Open the Text file step then look on the "Fields" tab to make sure that all the fields you saw in the "Output" in the first step are there.

--Jeff

[leomuler]

Jeff

Thanks by the help , but that didn´t worked. The SQL is OK. I execute it with preview and i retrive data ok. I made another table input step and i put the SELECT clause first and it´s works fine. EX:

SELECT a,b,c from Table 1 where etc etc.

This SQL works OK. But when i put that sql:
With name1 (SQL Statement) ,
With name2 (SQL Statement) ,
With name3 (SQL Statement) ,
SELECT
field1,
field2/name2
etc etc...

The kettle isn´t recognizing the columns (field1,field2). I execute this SQL with preview (inside table input) and it works. But the next step (ANY step) isn´t recognizing the fileds from the table input. I did this test:
1. Text File Input -> 2. Table Input -> 3.Text file Output.
In the text file input i put two columns. These columns from text file input were recognized by the step 2.Table input and by the step 3.Text File output too. But the columns (field1, field2, etc) from the table input are not recognized.
Any another idea ? I need an urgent solution!

[]´s

[JeffBeard]

Hi,

Well, if you get see the fields when previewing the Table input step then I'm not sure why it doesn't add the results to the output. If you right-click on the the Table input step and choose "Show output fields" and then right-click on the next step (make sure that there's a valid Hop between) then choose "Show input fields" and fields aren't the same, that might be a bug. However, in my admittedly limited experience I have not seen this happen.

--Jeff