Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

Join SQL Tables

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago #2116 by FlowHeater-Team
Join SQL Tables was created by FlowHeater-Team
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.

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago - 4 months 2 weeks ago #2117 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Join SQL Tables
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
Code:
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: 4 months 2 weeks ago by FlowHeater-Team.

Please Log in to join the conversation.

More
12 years 9 months ago #2118 by Herbert Berkley
Replied by Herbert Berkley on topic Re:Join SQL Tables
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

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago - 4 months 2 weeks ago #2120 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Join SQL Tables
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: 4 months 2 weeks ago by FlowHeater-Team.

Please Log in to join the conversation.

Time to create page: 0.312 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

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