Hello dear helper,



I should convert an existing mssql stored procedure into firebird. Perhaps
anybody can help me. The database was not created by me.



The structure of the procedure is slightly different. Select. into . from. I
don't understand about the union with identical queries, this get all
records two times. The structure of the database is not for my taste, no
normal form of the data.



Is there anyone who can say, why one does something and what can be the
reason?



Thank your





CREATE procedure [dbo].[sp_XXX]

as

select distinct FZG_ID as ID,

LEFT(FZG_UUID, 40) as UUID,

LEFT(KFZ_ID, 12) as FgstNr,

LEFT(TYP, 6) as FzgTyp,

RADSTAND as Radstand,

DATUM as Datum,

LEFT(ZEIT,5) as Zeit,

Left(STATUS, 12) as STATUS,

Left(PRUEFSTAND, 8) as Pruefstand,

Left(NAME,32) as Name,

Left(EMAIL,32) as Email,

LEFT(TELEFON,32) as Telefon

into #atus4temp

from openquery (ATUS4, 'Select * from ATUS4.SVS_FAHRZEUGE$ where FZG_ID <
2000000000')

if @@Error <> 0

begin

return 0

end



select ID, UUID, FgstNr, FzgTyp, Radstand, Datum, Zeit, Status, Pruefstand,
Name, Email, Telefon

into #atus4

from (select ID, max(UUID) as UUID, max(FgstNr) as FgstNr, max(FzgTyp) as
FzgTyp, max(Radstand) as Radstand, min(Datum) as Datum, min(Zeit) as Zeit,

max(Status) as Status, max(Pruefstand) as Pruefstand,
min(Name) as Name, min(Email) as Email, min(Telefon) as Telefon

from #atus4temp

INNER JOIN Ver_Status

ON #atus4temp.Status = Ver_Status.V_Status

INNER JOIN Ver_Pruefstand

ON #atus4temp.Pruefstand = Ver_Pruefstand.V_Pruefstand

where ID > 0

and Datum > dateadd (day, -1, getdate ())

group by ID

union all

select ID, max(UUID) as UUID, max(FgstNr) as FgstNr, max(FzgTyp) as
FzgTyp, max(Radstand) as Radstand, max(Datum) as Datum, max(Zeit) as Zeit,

max(Status) as Status, max(Pruefstand) as Pruefstand,
min(Name) as Name, min(Email) as Email, min(Telefon) as Telefon

from #atus4temp

INNER JOIN Ver_Status

ON #atus4temp.Status = Ver_Status.V_Status

INNER JOIN Ver_Pruefstand

ON #atus4temp.Pruefstand = Ver_Pruefstand.V_Pruefstand

where ID > 0

and Datum > dateadd (day, -1, getdate ())

group by ID ) as atus4

if @@Error <> 0

begin

return 0

end



drop table #atus4temp



delete from ATUS4Fahrzeuge

where ID not in (select distinct ID from #atus4)

if @@Error <> 0

begin

return 0

end

update ATUS4Fahrzeuge

set ATUS4Fahrzeuge.FgstNr = rtrim (atus4.FgstNr),

ATUS4Fahrzeuge.UUID = rtrim (atus4.UUID),

ATUS4Fahrzeuge.FzgTyp = rtrim (atus4.FzgTyp),

ATUS4Fahrzeuge.Radstand = atus4.Radstand,

ATUS4Fahrzeuge.Datum = atus4.Datum,

ATUS4Fahrzeuge.Zeit = atus4.Zeit,

ATUS4Fahrzeuge.Status = rtrim (atus4.Status),

ATUS4Fahrzeuge.Pruefstand = rtrim (atus4.Pruefstand),

ATUS4Fahrzeuge.Name = rtrim (atus4.Name),

ATUS4Fahrzeuge.Email = rtrim (atus4.Email),

ATUS4Fahrzeuge.Telefon = rtrim (atus4.Telefon)

from ATUS4Fahrzeuge fzg

join #atus4 atus4 on fzg.ID = atus4.id



insert into ATUS4Fahrzeuge (UUID, ID, FgstNr, FzgTyp, Radstand, Datum, Zeit,
Status, Pruefstand, Name, Email, Telefon)

select rtrim (UUID), ID, rtrim (FgstNr), rtrim (FzgTyp), Radstand, Datum,
Zeit, rtrim (Status), rtrim (Pruefstand), rtrim (Name), rtrim (Email), rtrim
(Telefon)

from #atus4

where ID not in (select ID from ATUS4Fahrzeuge)

if @@Error <> 0

begin

return 0

end



drop table #atus4

GO



[Non-text portions of this message have been removed]