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: Join SQL Tables

Join SQL Tables 5 years 6 months ago #2116

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
I have been looking for a way to pull data from multiple tables in one database and manipulate those into a series of text files..

For example I have 4 tables that contain information that together comprises the contents of a single invoice that I need to create a text file for, in a specific format, to import into a text bridge software..

Will flowheater allow me to take data from 4 unique table put it together based on a common value, invoice number, and then create a text file with that invoice number and then repeat the process to create all the invoices in those tables creating unique files for each invoice from those 4 tables?

Thanks,
Herb
Request via Email
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:Join SQL Tables 5 years 6 months ago #2117

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

You can do this by 2 ways.

Create a view in your database engine and select this view in FlowHeater on the READ side, or joins these tables together by a SQL query and use this SQL query. See the follow example
Select table1.invoicenr, table1.field1, table2.field1, table3.field1, table3.field2, table4.field1
from table1
inner join table2 on tabel1.invoicenr = table2.invoivenr
inner join table3 on table1.invoicenr = table3.invoicenr
inner join table4 on table1.invoicenr = table4.invoicenr
Put this SQL statement into the SQL textbox (READ side Configurator) and click the load schema button. Now you can export these field form these 4 tables into one text file.

If you want to export one text file per invoice, please have a look to the following forum post.
Export each row into a separate flat file
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.
Last Edit: 1 year 10 months ago by FlowHeater-Team.
The administrator has disabled public write access.

Re:Join SQL Tables 5 years 6 months ago #2118

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
Thanks Robert..

Just some background that might further develop this topic.. Tables 1,2,3 all have a matching number of records in common and all have invoice number in common.. However Table 4 has articles, or items but still has invoice number in common.. Since table 4 may have more rows specific to the single row, invoice number, being related by tables 1, 2, and 3.. Will that change this query?

Also.. With respect to the output.. I do have a particular output format that must be met.. My tables closely resemble that format, in terms of sequence or order, but to what extent can output into single files be manipulated? For example.. Can invoice number contained in the tables be removed before being placed into the single output file? Primarily because invoice number is only important to relate the data from the various tables and is not necessary in the final product text file.. Also.. Could each text file that is created be labeled, or named by the invoice number? I realize my sequence might be off here.. Surely you'd want to use the invoice number to name the file before removing it before writing the individual text file.. Point made?

Thanks for your time.

Herb
The administrator has disabled public write access.

Re:Join SQL Tables 5 years 6 months ago #2120

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

With SQL joins you can do all what you want with your data even 1:n relations and so on. This is not a FlowHeater functionality this is provided by the database engine. For more information please have a look to the following tutorial. SQL Join Tutorial

Your second question, this is the power of FlowHeater :)
Please have a look to the general examples and you will see that’s very easy. Your question about - set the export text file name to the invoice number I think it´s answered by this topic.

export each row into a separate flat file

If not I´ll could make a little example for you. Please post again if you need some example.
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.
Last Edit: 1 year 10 months ago by FlowHeater-Team.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.077 seconds