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

    SQL import with multiple columns

    786797
      Hi,

      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?

      regards
      Ben
        • 1. Re: SQL import with multiple columns
          beyerch2
          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
            786797
            Sir,

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

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

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

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

                  Ledger File
                  -----------
                  Account,Prod1,Prod2,Prod3
                  1000,12,234,56

                  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
                  -----------
                  Account,Prod1,Prod2,Prod3
                  1000,12,234,56

                  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
                  -------------
                  1000,12,234,56

                  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
                  
                     '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
                    786797
                    beyerch2,

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

                    thanks
                    Benoit
                    • 7. Re: SQL import with multiple columns
                      SH_INT
                      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
                        786797
                        SH,

                        funny that you mentioned this, as i was trying to do that as we speak

                        SELECT Account, Product, Amount
                        FROM
                        (SELECT Account, Prod1, Prod2, Prod3
                        FROM Table_1) p
                        UNPIVOT
                        (Amount FOR Product IN
                        ( Prod1, Prod2, Prod3)
                        )AS unpvt;
                        GO

                        would that look correct to you?
                        • 9. Re: SQL import with multiple columns
                          786797
                          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
                            SH_INT
                            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
                              786797
                              SH,

                              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
                                SH_INT
                                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
                                  beyerch2
                                  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
                                    786797
                                    Guys,

                                    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.

                                    Ben