Question

Querying a table and copying values to another table

srayford
Badge

So I have an Oracle table that has a Username field, Crew_Leader field, and a Crew_Number field.

In my process I am reading from a table that has a username value in a field called Editor.

I've been referring to this article: https://knowledge.safe.com/articles/19634/using-the-sqlexecutor-to-do-a-join.html

and decided to try to use the sqlexecutor transformer to run a query to join the Crew_Leader and Crew_Number fields to the table. However this seems to be very slow process as it tries to read over a thousand records. Do you know any other way I can accomplish getting the 2 fields from another table and then copying the values into fields in another table?

Thanks.

4 replies

srayford
Badge

SQL Statement: select a.*, b."CREW_NUM", b."CREW_LEADER"

 

from gas.primary_gas_vlv_maint a, sde.cfg_agol_user_lookup b

 

where b."USERNAME" = '@Value(EDITOR)';
takashi
Rank
Userlevel 2
Badge +17

Since your SQL statement doesn't have any conditions for joining table a and b, the statement performs CROSS JOIN on (table a) and (table b where USERNAME = EDITOR). Is it your intended query?

Result, the number of resulting features will be (the number of all records in table a) x (the number of records in table b whose USERNAME matches an EDITOR), every time whenever a feature is input. Naturally it could take a long time if the number of incoming features was large.

M
Badge +2

@srayford The article Merging or Joining Spreadsheet or Database Data gives ideas on the appropriate transformer to use for a particular type of join. SQLExecutor But as @takashi mentions, the SQL statement used will determine the results and possibly performance of the query.

srayford
Badge

At first I was trying to use SQLExecutor transformer that consisted of a SQL statement joining the 2 tables following this article: https://knowledge.safe.com/articles/19634/using-the-sqlexecutor-to-do-a-join.html

That was incredibly slow, so slow that I never got to the end of the process because it was taking too long. The SAFE representative Dan recommended I look into the InlineQuerier transformer. I was able to use the tables as inputs and then a SQL statement joining the 2 tables as the output. It worked for me and was fast. So the InlineQuerier transformer was my solution.

SQL Statement used in the InlineQuerier: select B.*, A.CREW_NUM, A.CREW_LEADER from "SDE.CFG_AGOL_USER_LOOKUP" A, "PRIMARYGASVALVE" B where A.USERNAME = B.EDITOR

Reply


Community Stats

28,707
Posts
109,480
Replies
36,553
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.