When I use output set to select multiple columns from the same object (table or view) and I use a parameter filter on one of the columns, I notice that the rp_run_query creates self joins thus making my output a mess. I get something like this:
Select work.customer_no,
'DSO - Ticket Perf Number' = a.perf_code,
'Ticketing Performance Name' = b.perf_desc
From #work1 work JOIN t_customer customer ON work.customer_no = customer.customer_no
Left Outer Join lvs_tck_hist a ON work.customer_no = a.customer_no and a.perf_code in('091012CL','091112CL')
Left Outer Join lvs_tck_hist b ON work.customer_no = b.customer_no
So.....being the adventurous type that I am, I jumped on the TEST server and looked at the code in rp_run_query. I find the code block responsible for this behavior.
Does anyone else experience this issue with the query creation?
At first glance this looks like expected behavior. If you have the same parameter filter values on both columns from the same table (in this case perf_code and perf_desc), then you shouldn't get the multiple joins. In essence what you are saying here is "give me a perf_codes with this filter and perf_desc's with no filter".
Thanks Chuck! I guess since I am not a user, this query syntax seems a bit much for me but I completely agree that the code is functioning as designed based on your explanation. However, I find it a bit cumbersome to have to put the same query parameter on every field I need from the same table but that's life, so I can live with that. But I think we can agree that if any developer were to write this query themselves, they would not use self joins to get this result set. With the use of multiple self joins, query performance may suffer but that's not a big deal and since output set builder is a user-based tool, I get it.
In the rp_run_query proc, I am curious as to why the data_where variable is used to determine the table alias based on the code comment at the top of this block. See block below.
-- check to see if this table has been used for a join already and add it if it does not Select @table_alias = null Select @table_alias = table_alias from #work_tables where data_from = @data_from and IsNull(data_where, '') = IsNull(@data_where, '')
The comment leads me to believe that an existing table will not reoccur in the syntax but I see that it is dependent upon "both" the table name and the where filter(s) and that was what initially threw me for a loop. In my testing, I found that if the data_where filter is removed, the self joins do not occur but I do understand your design and I can live with that. Thanks again.