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

Lookup heater

More
12 years 2 months ago #2202 by Tim Latter
Lookup heater was created by Tim Latter
I had the earlier version of flowheater (the first version) and now that I have upgraded to version 2, my Lookup functions stop working. They all look correct, as per version 1. Any suggestions?

thanks Tim

Please Log in to join the conversation.

More
12 years 2 months ago - 4 months 2 weeks ago #2203 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
Hi Tim

There are some changes about SQL Server user/owner tables and schemas. Do you use the SQL-Server Adapter with the database Lookup Heater?

Best practices) The Lookup Heater is very strength. Please try to use the new SQL Heater instead of the Lookup Heater. With the SQL Heater it is very simple to implement a Database Lookup.

Here you can find an example how you can use the SQL Heater. Dynamic SQL Filter

If you want to use the Lookup Heater furthermore please post you’re FlowHeater Definition (zip) for more analytic.

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 2 months ago #2204 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
Hi, thanks for the reply. I am using the sqlServerSdapter. As I said in the first post, this worked in version 1; the first time I opened the V1 .FHD file, a message came up and asked if I wanted to convert it for V2 which I answered yes to.
When I tried the SQL heater, I received a message that stated “There is already an open DataReader associated with this Command which must be closed first.”

Any suggestion would be greatly appreciated.
Thanks tim

Please Log in to join the conversation.

More
12 years 2 months ago #2205 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
Hi Tim

Ups! This is an error with the SQL-Server Adapter on READ in connection with the SQL Heater. Maybe this is also the reason why your Database Lookup doesn’t work.
I’ve fix it. You can download the Fix (Beta) here . In the ZIP archive there’s no setup, you have to start FlowHeater.exe by double-click!

With the next version this is official fixed!

Thanks for the notification.

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.

More
12 years 2 months ago #2206 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
hi, i loaded your beta version of flowheater 2 and the lookup works, but i am still having problems withe the sql heater.

i have added an sql statement that is "select [address1] from communication_old where commtype = $comm$ and
commlocation = $location$". i have attached a picture of the heater properties.
when i run this i get the error message "lookup error - select [] from []" the read side is using the communication_old table as per the image.


i would appreciate any help as i don't think i can use the lookup heater to perform the function i need.
Attachments:

Please Log in to join the conversation.

More
12 years 2 months ago #2207 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
hi tim,

are you sure that this error message occurs from the sql heater? this error occurs usually form the database lookup heater.

verify that no database lookup heater is on your definition and try it again please. if the error message occurs again please post your flowheater definition (zip).

in case of you want to select some information from the same table on read you have choose in the read adapter please try the following. go to the adapter properties on the read side and enter for the property "autocommitafter" the value -1.


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.
Attachments:

Please Log in to join the conversation.

More
12 years 1 month ago #2208 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
Hi, I tried to change that parameter (AutoCommitAfter) and the run did occur, however the value of "phoneNumber' which is a look up value dependant on 2 critera in the read table (heater) does not change value. It repeats the first value. I have attached the heater definition file. This functionality, if I can get it to work, would solve many problems in migrations and transfers that I am performing now.

Attachment oldCommtoComm.zip not found

Attachments:

Please Log in to join the conversation.

More
12 years 1 month ago #2209 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
the error message throws from an empty database lookup heater on your definition. see the screenshot below.

i don't know what your purpose? could you describe it please? i guess you want update the new table with some old content? in this case i think you can do it a bit simpler.


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.
Attachments:

Please Log in to join the conversation.

More
12 years 1 month ago #2210 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
hi, thank you for your last reply. what i am trying to do is to take information from a table called “communication” which holds address, phone, email data to a new table which will hold similar information except in different columns. the problem with the old table is that all the address, phone, email information is in one column. this column is than uses two other columns which are guids defining the "type" of information (address, phone, email) and a second column which defines the "location" of the information (work, home). in the new table i break out the information (address, email, phone) into separate columns to allow better reporting of the information.

so i have to take a column from the old table called “address1” which holds the address, phone, email information and use criteria to put that information into new columns in the new table. i tried to use a sql heater (i also tried a lookup heater). when i use the sql heater, i used the sql statement “select communication.address1 from communication_old left join communication on communication_old.contactguid = communication.contactguid where communication.commtype = $comm$ and communication.commlocation = $location$” as per the definition file i sent you. the problem now is it takes the first value it finds in the old table (like a phone number) and repeats the same information for all rows in the new table. i have attached part of the definition run. i hope this explanation helps.

thank you
tim
Attachments:

Please Log in to join the conversation.

More
12 years 1 month ago #2211 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
You have to specify what record you want to read. I guess the right field is the ContactGUID field? I've made an example based on your definition; please have a look to the attachment. Note: The definition isn’t tested!

First I set the parameter "ContactGUID" with the content of the ContactGUID field. This parameter we need to build the Select in the SQL Heater.

For example)
Email field: Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = '$commEmail$' and CommLocation = '$location$'

PhoneNumber field: Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = '$commPhoneNumber$' and CommLocation = '$location$'

The parameters "CommType" and "CommLocation" are defined as static parameters. It's also possible to use the GUID without parameters.

Note: In the example I've used the GroupBy Heater . With this only one record per "ContactGUID" are imported/inserted in the new SQL Table. For this you have to use the following SQL select statement on the READ Side

select * from communication_old order by ContactGUID

Attachment sql_heater_example.zip not found


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.
Attachments:

Please Log in to join the conversation.

More
12 years 1 month ago #2212 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
Hi, as per your last reply, I don't understand set the "contactguid" parameter. This is the id of the user and changes for each record. Each row represents a user record. The primary key is "Contactguid"

Please Log in to join the conversation.

More
12 years 1 month ago #2213 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
Correction on my last post. The Contactguid is not the primary key, but it represents a user that is stored in the "Contact" table. Contactguid is a foreign key.

Please Log in to join the conversation.

More
12 years 1 month ago #2214 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
Hi Tim,

I guess it’s only. I don’t know your database schema. In my opinion our database looks like this.

CommGuid -> UniqueID (PrimaryKey)
ContactGuid -> per each contact unique
CommType -> Type of communication e.g. Email, Phone, …
CommLocation -> Home, Office, ..
Address1 -> the value for Phone, Email, …

Here a example without guid’s I’m using ID’s and Names.

First Contact
CommGuid1, ContactGuid1, CommType_Email, CommLocation_Home, Address1_Email1
CommGuid2, ContactGuid1, Commtype_Phone, CommLocation_Home, Address1_Phone1

Second Contact
CommGuid3, ContactGuid2, CommType_Email, CommLocation_Home, Address1_Email1
CommGuid4, ContactGuid2, Commtype_Phone, CommLocation_Home, Address1_Phone1

To select the home phone number from the second contact we need a SQL select like this

Select Address1 from communication_old where ContactGUID = ' ContactGuid2' and CommType = 'CommType_Email ' and CommLocation = 'CommLocation_Home '

If I have misunderstood you please post a short description of your database schema and some example data.

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.

More
12 years 1 week ago #2219 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
Hi, sorry I didn't get back sooner, I have been away. Your schema documented in the last post is correct. Again, what I don't understand is how to set a (one) parameter for contactguid in regards to coping from the old table to the new table. Contactguid is the userID and can change for each record or a set of records (one user can have an email, phone number fax number etc - therefore 3 records in the old table.). The old table has 10000 records and therefore around 5000 users (5000 contactguids that refer to each individual user). How do I set "ONE" contactguid parameter to copy from the old table to the new table. I need every user and every record.

Please Log in to join the conversation.

More
12 years 1 week ago #2220 by Tim Latter
Replied by Tim Latter on topic Re:Lookup heater
A p.s. to the last post. Just to be clear, I need to transpose the information from the old table which look like

commGUID
ContactGUID
Address1 (all information such as email, address, phone)
Commtype
CommLocation

To a table that looks like this:

CommGUID
ContactGUID
Address (for address)
Email (for email)
Phone (for phone number)
CommType
Commlocation

Basically I need to break up the address1 field in the old table (that holds all the user information for address, phone, email etc.) and add it to individual fields in the new table.

I hope this helps.


Tim

Please Log in to join the conversation.

More
12 years 1 week ago #2223 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Lookup heater
Hi Tim,

I’m wondering why you want to import the field’s commType and commLocation to the WRITE side?

I have made a simple example including a little access database. Please have a look to the attachment.

Here I’ve replaced the commType and also commLocation GUID with the static words (Email, Phone, Home, Work). Also I’ve used numbers instead of GUID for the fields commGUID and contactGUID!

How does it work?
The GroupBy Heater groups all same contactGuids to one row on the WRITE. Then per each row the SetParameter Heater stores the actual contactGUID in the Parameter "$ContactGUID$". This parameter are used in the SQL Heater to build dynamic SQL Statements like this.

Select Address1 from communication_old where ContactGUID = '$ContactGUID$' and CommType = 'Email' and CommLocation = 'Work'

The parameter placeholder $ContactGUID$ is replaced with the current contactGUID of each row.

Attachment access_example.zip not found


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.
Attachments:

Please Log in to join the conversation.

Time to create page: 0.487 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.