Solved

Running Querys on record sets

  • 6 October 2017
  • 3 replies
  • 2 views

Badge

perhaps I am overthinking this, but here is my workflow

  • user provides a CSV file with two columns
    • table name
      • apple
      • orange
      • banana
    • query
      • query 1 = select count(*) from apple where colour = 'red'
      • query 2 = select count(*) from orange where colour = 'orange'
      • query 3 = select count(*) from banana where source_location <> (select top 1 source_location from orange)
  • read the csv
  • use feature reader to dynamically read that table from a GDB (table name attribute as input)
  • here is where I am overthinking.

here is my desired output table

table namequeryresult countappleselect count(*) from apple where colour = 'red'2orangeselect count(*) from orange where colour = 'orange'5bananaselect count(*) from banana where source_location <> (select top 1 source_location from orange)1

I want to be able to run each row from the CSV table, and return the count result of the query.

keep in mind, there are queries that rely on other tables as well. so my thinking is throw all of them into an inline querier. however, Inline querier only allows the input query to be a user parameter, and not decided by an attribute such as the query attribute from the CSV table.

Any thoughts?

cheers,

icon

Best answer by takashi 7 October 2017, 03:23

View original

3 replies

Userlevel 1
Badge +4

Hi! One reflection. As we are overthinking to begin with you could "cheat" a bit. I think the InlineQuerier uses SQLite to begin with. So you could "mimic" that behavior and first write the features (or selected columns) with a FeatureWriter to a SQLlite database. Then you could use the SQLExecutor on that tempporary database. But it is probably overthinking it :-)

Userlevel 2
Badge +16

I like the thought.

As @ulfme mentioned, I would try to store the contents of the CSV file in a database and execute the query against the database.

But not sure if that is an option.

Userlevel 2
Badge +17

If the source GDB format supports SQL querying, I think you can execute the SQL statement (i.e. the value of "query" attribute given by each initiator feature read from the CSV table) with the SQLExecutor transformer.

Reply