14 Replies Latest reply on Jun 7, 2012 6:01 AM by 786797

    SQL import with multiple columns


      I know how to import multiple columns from a file using fparsestring and i know how to import values from a single Value column in SQL. But now i need to import multiple values from 3 columns from a sql DB at once.

      in example

      Col1     Col2     Col3     Col4

      Account     Prod1     Prod2     Prod3
      1000      12     234     56
      2000      324     34     90

      I guess i cannot use fparsestring here...anyone has an idea how to solve this? of what type of function i can use?

      With a file, i can identify the columns Prod1 = DW.Utilities.fParseString(strRecord, 4, 2, ","), but how to do this with a Table?

        • 1. Re: SQL import with multiple columns
          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.
          • 2. Re: SQL import with multiple columns

            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...
            • 3. Re: SQL import with multiple columns

              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.
              • 4. Re: SQL import with multiple columns
                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.

                • 5. Re: SQL import with multiple columns
                  Ok here are some scenarios....

                  Scenario #1 - Work with data from Load File (vbscript)

                  Ledger File

                  Import Script
                  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 #2 - Work with data from Load File (fParseString)

                  Ledger File

                  Import Script
                  Function GetAccount(strField, strRecord)
                     GetAccount = DW.utilities.fparsestring(strRecord,4,1,",") 
                  End Function
                  Scenario #3 - Work with data from any database table that is in the FDM Database

                  Database Table : Table1
                  Account [nvarchar(25)]
                  Prod1 [nvarchar(25)]
                  Prod2 [nvarchar(25)]
                  Prod3 [nvarchar(25)]

                  Data Rows

                  FDM Script (Import, Event, Custom - Not sure what you are needing this for, but shouldn't matter)
                  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 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.....
                  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)

                  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
                  • 6. Re: SQL import with multiple columns

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

                    • 7. Re: SQL import with multiple columns
                      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
                      • 8. Re: SQL import with multiple columns

                        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)
                        )AS unpvt;

                        would that look correct to you?
                        • 9. Re: SQL import with multiple columns
                          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 :-)
                          • 10. Re: SQL import with multiple columns
                            Looks fine to me, should do what you are looking for. Although you won't need the GO in your FDM sql query
                            • 11. Re: SQL import with multiple columns

                              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
                              • 12. Re: SQL import with multiple columns
                                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.
                                • 13. Re: SQL import with multiple columns
                                  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?
                                  • 14. Re: SQL import 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.