7 Replies Latest reply: Feb 9, 2016 8:59 AM by Grant Perkins RSS

    Monarch input filter for BASIS ODBC data source

    Mark Shields

      Hi,

       

      I am using Monarch to connect to a BASIS database using their BBj ODBC driver, When selecting the data, all fields are presented a Character types so Monarch has to do the type conversions for me (which is fine).

       

      The problem is when applying an input filter, I have to use a string for a date in format YYYYMMDD. If I connect Excel to the datasource and use INVOICE_DATE >= '20160101' then it returns the correct dataset, Monarch on the other hand tells me the string is in not in the correct format. I believe this is because Monarch is unable to convert it's input filter into the correct syntax for the BBj driver to pass to the database.

       

      It would be great if there was a way of getting Monarch to allow me to specify the SQL statement or define input filters that it cannot verify itself but I know are correct.

       

      Any advice anyone?

       

      Thanks

        • Re: Monarch input filter for BASIS ODBC data source
          Chris Porthouse

          What version of Monarch are you using?  If you are applying a filter based on a date field, try surrounding the date in curly braces: Date>={2010-04-18}

            • Re: Monarch input filter for BASIS ODBC data source
              Mark Shields

              I am using the latest release of Monarch as We had it installed last week.

               

              I cannot use that syntax as the field is a string that represents a date in the YYYYMMDD format (e.g. 20160209 for today)

               

              if I put the filter expression as INVOICE_DATE >= "20160101", Monarch will go off to the datasource and come back with an error saying it cannot open external database with the details saying "Input string was not in a correct format." If I use the same connection string with no input filter, then I get the entire dataset back. This is not desirable and I cannot make this implementation live as the whole dataset is too large to work with in a timely way.

               

              Thanks,

            • Re: Monarch input filter for BASIS ODBC data source
              Joseph Territo

              Mark,

              So essentially you would want the Import Filter section to have the Ctod() function available in this case so it could convert the string date value into an actual date value, and thus allowing you to filter properly.... I believe this would be a good enhancement request to Datawatch. I would recommend you submit that to support@datawatch.com and see if it could be implemented on a future release.

                • Re: Monarch input filter for BASIS ODBC data source
                  Mark Shields

                  I think the best solution would be for Monarch to support entering of the SQL statement directly as it does in Data Prep Studio because I can get the filtered data into there but I cannot do what I need to be able to do with it from there.

                  I need to use the power of Monarch and the features of the "Standard Process" in Automator (Insert new and update existing records in a OleDb export) to get my data into a data warehouse to run a Visualiser server from.

                • Re: Monarch input filter for BASIS ODBC data source
                  Grant Perkins

                  Hi Mark,

                   

                  Are you specifying the filter as an expression?

                   

                  I am not familiar with BASIS but I can't see how a filter on an incoming field presented as a text string would not filter it the filter is also a text string. That, in effect, seems to be what your Excel example is doing.

                   

                  Have you defined (and saved) a model for the incoming data or are you letting Monarch interpret the fields on the fly when you open the source?

                   

                   

                  Grant

                   

                  ETA: Looks like Joseph has pre-empted my question somewhat ... still interested in your reply though.