LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

hi,

i have the following case:
a.1 or more source files(csv, xls etc) less important, that are parsed with dedicated nodes and we are able to see the output.
b.one dbquery wich does not support input. In this query connector i want to build some custom sql in order to be able to treat the output of the xls file/or files as an input table in the sql statement(for eg a join with the output from xls, the statement is much complex and calculates different aggregation from the begining on large volumes of data that needs to be filtered from the begining).
I m aware about listing data and use join, and then filter, and then calculate aggregation....
Please if you met and solve this case until now and you can provide me your feedback....
I m using oracle but i want to avoid storing the input files in permanent tables, maybe if exists local temporary tables suported by the tool(tables that are automaticaly droped at end of the conector)
Imagine the next scenario excluding measures calculation:
you have 50 mil of customers and a black list(exclusion list provided in an xls file -every day the list is different)
Exclusion list could reduce the no of customers to 10 mil( exclusion list can be a type of the customer or the customer id...)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

What you'll need to do is use the DB Execute node. See the attached. I'm using a Static Data node to simulate the results you would get from CSV/XLS/etc. The key is using the sqlSelect function. You can either perform one query per record (this is the top example) or aggregate your filter values into one string and use the IN condition. For exclusions, you could do NOT IN as the expression, but my colleague warns me that there are limitations on how many values can go into a list following the IN condition.
Attachments:
DB_Execute.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

hi,
thx fot the detailed answer, but is just a partial result for what i need.
in the connector i want to write code that contain joins/inner/left...on different columns from the xls/i need the xls output to be available like a table in the custom sql.
select a.*,b.* from source1 a left outer join (xls_content) b on a.key=b.key

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

For the volumes of data you need to use in the query, you'll need the optimal method available to send the data to Oracle. In your question, I don't see what the size is of the initial set of data you are using. I've seen the answer ryeh gave you work fine for up to a few 100K records.

For the blacklisted customer data you mentioned, it seems like you are talking about maybe 40 mil records. To use that data for query you will have to send it to Oracle in some way. Sending them as a parameters in a query would be slow for that amount of data unless you have a very fast network connection to the database. The most efficient way to send data to Oracle is with the DB Store node, this means loading to a temp table. And since each LAE node uses its own independent database connection you will not be able to use temp tables that only survive the connection or transaction, they will need to be much more permanent.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gabygyy

HI,

i think you answer my question, regarding temp tables/ that they are available only during the node/ in the meantime i developed another custom solution-maybe this is usefull for others. I've created a procedure that is able to create/drop any table. The table name will be passed as a param and will contain in the name the batchid to be unique per run.
step1. launch procedure to create table
step2.insert data from xls to table
step3 use the table in the joins where nedeed
step4 use the same procedure to drop table.