Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: Read side SQL: Extracting only the latest row

Read side SQL: Extracting only the latest row 6 years 2 months ago #2024

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
Translated submission in the German forum. You can read the original entry here.

Hi,

I have an MS-SQL database where the data is merged from two tables. This works fine using the following SQL script on the READ side:
SELECT F_TP.T_SU, F_TP.T_ME, F_RP.R_ME, F_RP.R_DATE, F_RP.TP_ID
FROM LoTr.dbo.F_RP F_RP, LoTr.dbo.F_TP F_TP
WHERE F_RP.CAT_ID = F_TP.CAT_ID AND F_RP.F_ID = F_TP.F_ID AND F_RP.TP_ID = F_TP.TP_ID AND ((F_TP.T_STATUS=1) AND (F_RP.R_ME Not Like '%WV%' And F_RP.R_ME Not Like '%II%' And F_RP.R_ME Not Like '%VA%'))
ORDER BY F_TP.T_SU

Depending on the number of entries, F_RP.TP_ID can appear any number of times. However, I always only need the last entry, which I can determine from F_RP.R_DATE.

How can I get FlowHeater to just load the last entry by itself? Do I have to do that in SQL or can it be easily achieved using a Heater?

Thanks and greetings,
Nick
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.

Re:Read side SQL: Extracting only the latest row 6 years 2 months ago #2025

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
Hi Nick,

You can do this with the GroupBy Heater. Drag and drop the GroupBy Heater onto the Designer window and connect it to your field F_RP.TP_ID. To enable it to function properly you also have to modify the ORDER BY clause in the SQL SELECT statement. In your case, for example:

order by F_TP.T_SU, F_RP.TP_ID, F_RP.R_DATE

It is important that all "F_RP.TP_ID" fields that are to be grouped are delivered to FlowHeater in sequence. The GroupBy Heater then combines all identical values to a single row on the WRITE side. Note that if instead of the last entry, you wish to determine the first value, it is simply necessary to modify the Order by clause, by adding DESC (meaning descending) after the date field. For example:

order by F_TP.T_SU, F_RP.TP_ID, F_RP.R_DATE DESC
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.069 seconds