This content has been marked as final. Show 14 replies
I've read your question multiple times and I'm still not sure what you are asking.
What exactly don't you understand about columns in a database? How to dynamically refer to them without knowing the names of the columns? How to refer to the values in a database table?
Is there a specific issue you are trying to work out? That might help me to understand the issue.
i know the table name and the columns named but what i do not know is how to manipulate columns to transpose them into lines.
based on my example above, the result should look like this
Account Products Amount
1000 1 12
1000 2 234
1000 3 56
2000 1 324
2000 2 34
2000 3 90
i know how to do this with a file but not with a DB table...
Essentially you need write a SQL Query that will get the data from the database. To use that inside of FDM, you will need to script code to pass the SQL Query to the database, get the results, and use them in your code.
Using your sample columns (Account, Products, Amounts) with a sample table name of Table the SQL Query would be :
Select Account, Products, Amount from Table
Executing that would return all 'rows' from the database table as shown in your example.
In the morning, I'll dig out some sample VBScript that you would use in FDM to actually execute the query and reference the data from script.
Thank you. I look forward to see what you will come up with.
i understand your suggestion and hope your code will clarify the way to achieve this.
Ok here are some scenarios....
Scenario #1 - Work with data from Load File (vbscript)
Scenario #2 - Work with data from Load File (fParseString)
Function GetAccount(strField, strRecord) Dim arrFields 'The split command takes a string and a delimitter character that you supply and creates an array where each element is a split value 'You can then reference the specific 'column' you want by ordinal. Column 1 is array element 0 unless you have OPTION BASE 1 at the beginning of your code... arrFields = Split(strRecord,",") GetAccount = arrFields(0) 'NOTE : Prod1 would be arrFields(1), Prod2 arrFields(2), etc, etc. 'NOTE 2 : Anytime you use split, you probably want to double check that you got the expected number of columns. You can check the number of elements by using the UBOUND ' If ubound(arrFields) <> 3 then error occurred, do something.... End Function
Scenario #3 - Work with data from any database table that is in the FDM Database
Function GetAccount(strField, strRecord) GetAccount = DW.utilities.fparsestring(strRecord,4,1,",") End Function
Database Table : Table1
FDM Script (Import, Event, Custom - Not sure what you are needing this for, but shouldn't matter)
Now, the API I'm using above is only really going to work for tables in the current database that FDM is operating with. (Notice that nowhere do I tell it how to connect to the database or even what database to connect to. That is due to the fact that the API call is doing the dirty work of establishing database connections, etc. (or re-using the existing database connections established by FDM)
Function GetDBData 'Declare Working Variables Dim strSQL Dim strAccountPlaceHolder 'Initialize Working Variables strSQL = "SELECT Account, Prod1, Prod2, Prod3 FROM Table1" 'NOTE: The above query is going to get every record in the sample Table1 table. If you look into SQL query language, you can do a lot more than this, but 'That can be a whole different conversation 'Create Recordset for all Exported Entities Set rs = DW.DataAccess.farsKeySet(strSQL) 'NOTE: This is an API call that takes your SQL Query and hits it against the database and returns the resulting data rows. Since our example only has one row, you'll only get one. 'IMPORTANT : ANYTIME you query a database, the first thing you should do with the result is confirm that it has data. (see next lines) If rs.EOF And rs.BOF Then 'EOF = End of File 'BOF = Beginning of File ' If you query a database and both of these flags are immediately set, that means there is no data. 'When you have data, BOF will be set, but EOF will not be set. As you iterate through the records, EOF will set after you have read the last row and attempt to read more. 'Here I would return sometype of message indicating no data and exit the function usually... end if Do 'Do something with the Account value strAccountPlaceHolder = rs("Account") rs.MoveNext 'system to read next record Loop Until rs.EOF 'As long as we don't hit EOF, keep going.....
If you want to talk to an entirely different database, you can do that, and the code will look very similar, except that you have to add your own vbscript to establish the database connectivity, etc. If you search vbscript and ADO on the net, you should find enough to help you; otherwise, ask.
Edited by: beyerch2 on Jun 5, 2012 9:13 AM
scenario 1 & 2 are clear and i know how to do this.
scenario 3, unless i misunderstood, is the beginning of a script reading from a DB, which i know how to use to.
Now it doesn't solve my pivoting issue i think...
If you are using SQL server 2005 or later simply use the UNPIVOT relational operator in your DB query to move the multiple value columns into seperate rows. Simples. This is also possible with Oracle11
funny that you mentioned this, as i was trying to do that as we speak
SELECT Account, Product, Amount
(SELECT Account, Prod1, Prod2, Prod3
FROM Table_1) p
(Amount FOR Product IN
( Prod1, Prod2, Prod3)
would that look correct to you?
this is working. now i just need to figure out how to make the table_2 always using this sql statement...but thanks guys for your help :-)
Looks fine to me, should do what you are looking for. Although you won't need the GO in your FDM sql query
now i know i am going to sound dull, but i would like to make sure i understand what you are saying.
i have table_1 with the content. fine
table_2 as destination
can fdm update that table_2 from table_1 everytime i am doing an import??
in other words, fdm would update table_2 then import from table_2???
i am not a program which is why i might sound stupid.. sorry
Table 2 is is a temporary work table that FDM creates every time you run an import regardless of the source of the data (file/DB). Data is stored here while all the import format parsing mapping etc is applied and then if no errors occur the transformed data will be moved to the approriate data table asscoiated with the FDM loacation you are importing data to.
I guess I misunderstood the original question. Ex. 3 is simply reading rows from a DB and using the account field. It sounds like the issue is you want to combine multiple data rows to one with multiple columns?
thanks for your help i got it to work!!
FDM can execute the sql statement from table_1 to table_2 then import from 2. great stuff.
now i have one more question. i noticed (i never thought about looking at this before..) that when i click on open source document, it shows the script...is there a way to generate a file in the back end to which FDM would drill back to? or is this the only behaviour when using imtegration scripts?
thank you anyways for all your help.