Would it be bad practice to run a named query in a transform?
I have a sql query that returns some data and a separate table that I cant inner join. So I want to loop the rows and run a named query to pull in the other aggregate data to be displayed in a table.
I'm torn on the named query in the transform and I wanted to get some other point of views or suggestions.
I'm not sure I understand what you're trying to do here.
Why don't you create a copy of the named query you're using and add to it the inner join you need?
Why can't you inner join them ? Is the other table on a different database ?
You should probably try to fix whatever prevents you from using a simple join, instead of working around it in transforms.
its different data that's not relational but I want it in the same table/rows.
Maybe i could try to link it somehow but I would have to rethink the data entry
How do you merge the data if there's no relation between them ? What's the rule ?
Even if a join isn't possible for whatever reason you were going to follow some logic in your transform which could just be a subquery at worst inside your query. The more you let your database handle data manipulation the better off you will be performance wise.
Can you share your database schema and a little sample data?
I'll get back with some reply's after the 4th, I appreciate the help!