FlowHeater - The Data Import / Export Specialist
See also the existing examples of using the SqlServer Adapter Adapter, which may answer your question: CSV text file import , flat file export , Import IDENTITY (Auto Increment) fields , CSV export divided into several files

Read side SQL: Extracting only the latest row

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Administrator
  • Administrator
More
12 years 4 months ago #2024 by FlowHeater-Team
Read side SQL: Extracting only the latest row was created by FlowHeater-Team
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:
Code:
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.

Please Log in or Create an account to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Administrator
  • Administrator
More
12 years 4 months ago #2025 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Read side SQL: Extracting only the latest row
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.

Please Log in or Create an account to join the conversation.

Time to create page: 0.301 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

Copyright © 2009-2023 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook

YouTube

 de en