@MYGz the table's name is the value from the ordertable?

Yes

Surey it's possible. You need dynamic SQL.

I also have the OrderTableField to specify the field on which to join.

I have to do this for various table in customer table..

Must read: The Curse and Blessings of Dynamic SQL

Any tutorial to get started in this direction?

k thanks. Reading Now...

Morning @ypercubeᵀᴹ

morning

@ypercubeᵀᴹ Do you know if is it possible to use RAISE NOTICE (inside a block of code) on fiddle.uk?

dbfiddle.uk/…

@McNets No, no idea. I don't think I have tried that.

Perhaps try to create a function that does that.

@McNets dbfiddle.uk/…

@ypercubeᵀᴹ It's the same I posted

dbfiddle.uk/…

@McNets I added language plpgsql.But see the last one, with a function

@ypercubeᵀᴹ yes, thanks

@MYGz a very much shorter introduction: sqlservercentral.com/articles/Dynamic+SQL/167392

But really, you have to read that Erland Sommarskog tutorial and try to work with this examples.

It's longer and takes time to get everything in, but it deserves it.

@ypercubeᵀᴹ Yep. I quite got it for 1 table. But Unable to figure out for multiple different tables in Customer.ordertable.

One of our projects is using something remotely similar. The table to join is encoded with a number rather than its actual name. And it's more like the entity than the table that is encoded, although in this case you could say it's all the same. And we have a reference table to describe each entity code. However, even when a table references different entities, we usually need to join it to just one of them, and the entity code is hard-coded in the query.

Some thing like ... FROM SomeTable AS t INNER JOIN SomeEntity AS e ON t.EntityCode = 5 AND t.EntityRefID = e.EntityRefID ...

Horrible design choice, but it's been in use for a very long time, and if I'm honest, it's not very easy to even come up with an option to replace it, let alone implement it

To clarify a little my example above, the EntityCode of 5 is a reference to the SomeEntity table. If it was a different value, it would be a different entity table to join. As I said, the entity codes are always hard-coded in the queries (and so are the entity table names, of course).

This is out of the box Microsoft design in Financial Dimensions framework. And Microsoft doesn't expects us to touch tables from SSMS. Table's should be queried from X++ programming language where the business logic is also available.

When facing the need to write a dynamic query, I usually start with an idea of how the final static query would look like in a specific scenario. Then think of a different scenario and the query for it, and then compare the two queries and figure out the variable parts and what would need to be the input to produce them.

This has worked well for me, but then I've only ever generated dynamic queries in T-SQL itself and in Delphi (the development environment we use).

I'm unable to think if there are too many tables (values) in Ordertable column, how would the final query look like. Tons of joins and 'on' conditions?

If a single query needs to pull from different tables at once? Possibly

And those would probably be outer joins. On top of the query's being unwieldy, it would likely be extremely slow

Yes. I think looping over and going 1 by 1 would be the right approach. Single query would produce massive duplication as there are unequal number of rows in each table.