Visokio website     Downloads     Video tutorials     KnowledgeBase  
Import: Parametric SQL 'WHERE' clause with Excel? - Visokio Forums
Import: Parametric SQL 'WHERE' clause with Excel?
  •     DominicChan August 19, 2014 6:47AM
    Hi....Maybe it is already available?
    I would like to read an Excel file, say containing a field with 300 IDs, and use these 300 IDs as the SQL's 'Where' clause in conjunction with {{Parameter}} (or other ways?)
    Is this possible?
    Thanks
    Dom
  • 5 Comments
  •     tjbate August 19, 2014 7:30AM
    Dom - DataManager Database import blocks DO support fixed parameters which can be used as filters or arguments in conjunction with parametric SQL Statements. However, if you have 300 such fixed values, and you are importing from a spreadsheet via a Data File source block rather than an SQL Database source block, this is probably not practical.

    A better approach would be to use a separate IOK file to import all the data from the spreadsheet, then use a merge block comparing all records to a reference file listing only the 300 IDs of interest, keeping only the matching records. This IOK file matching only the 300 IDs of interest would then be the source file for your subsequent analysis.
  •     DominicChan August 19, 2014 8:44AM
    Yes.
    I am however exploring if I can limit the records returned/query time by an SQL statement, if the database has >1m records and I am really only interested in say 300 of them.
    I suppose there is no way of doing that.
    Dom
  •        daniel August 19, 2014 9:47AM
    If you have the ability to edit the main database or get your database team to do it, you could create a separate table of those 300 IDs in your database and use a SQL join query to filter it at source.


    You could also use more refined SQL queries to subset your data so that you won't have to download all 1m+ of them, ie. using date filters if you have time stamps in your data or if you ID field are integer numbers, take the min & max values and use them as:

    SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2;

  •     tjbate August 19, 2014 1:48PM
    Dom - Daniel is right. You could ask the DBA to write a stored procedure flagging all the IDs you are interested in with a value like "For Dom", then set the Omniscope parameter used in the Database Source block custom SQL WHERE clause to be the parameter text value "For Dom"

    ....but sometimes you have no influence over the database or its stored procedures.

    In summary, Omniscope DOES support parametric SQL queries, but not yet parameters comprised of lists where you could specify a parameter that is in fact a list of 300+ text values.

    You CAN pass parameter lists from one Omniscope to another, but not yet embed the text value lists in custom SQL Queries in Database Source Block connectors.
  •     acohen October 23, 2015 1:03PM
    Parametric queries as specified above would be really useful for us when querying across different databases. This would enable us to reduce our query time significantly. We would look forward to any updates in this regard.

    Thanks

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership