- Posts: 9
Lookup heater
- Tim Latter
- Topic Author
- Offline
- User
Less
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
thanks Tim
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 2 months ago - 4 months 2 weeks ago #2203
by FlowHeater-Team
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.
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.
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.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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
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.
- FlowHeater-Team
- Offline
- Admin
12 years 2 months ago #2205
by FlowHeater-Team
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.
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.
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.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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.
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.
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 2 months ago #2207
by FlowHeater-Team
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.
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.
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.
Please Log in to join the conversation.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 1 month ago #2209
by FlowHeater-Team
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.
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.
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.
Please Log in to join the conversation.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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
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
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 1 month ago #2211
by FlowHeater-Team
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.
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
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.
Please Log in to join the conversation.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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.
- FlowHeater-Team
- Offline
- Admin
12 years 1 month ago #2214
by FlowHeater-Team
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.
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.
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.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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.
- Tim Latter
- Topic Author
- Offline
- User
Less
More
- Posts: 9
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
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.
- FlowHeater-Team
- Offline
- Admin
12 years 1 week ago #2223
by FlowHeater-Team
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.
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.
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.
Please Log in to join the conversation.
Time to create page: 0.487 seconds