Allow joins to use constant values or reference codes

David Registro ●

Hi Stephen,

Firstly apologies for the delayed response! Appreciate the level of detail you have provided, makes our job a lot easier :)

I'm moving this to a question (from idea) as I believe what you're after is already achievable.

What you're going to want to do is to create a report, and use a filter there.

At the view level, just make all your joins, so the view essentially returns everything.

Use the report to actually filter down the data, and apply sub-queries

Hope this helps, and please let me know if it doesn't.

Regards,

Daivd

Reply URL

1

Stephen Johnson ●

Thank you to whoever deleted Yulia's comment. It was incredibly rude and failed to understand my point. Not ideal qualities for consultants supporting this product.

David, I appreciate your response, but I don't think we're on the same page. The two SQL statements below are not identical. They produce different result sets. The first is what I'm after, the second is what I think your filter approach would create.

Query 1:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
  ON TableA.col1 = TableB.col1
  AND TableA.col2 = 1

Query 2:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
  ON TableA.col1 = TableB.col1
WHERE TableA.col2 = 1

The advantage I see in the first query is that I can aggregate metrics from TableA (where I want all Table A records) and TableB (without double counting, since TableA to TableB is a many to one relationship).

If I can apply a filter at the subquery level rather than the report level, then I might be able to do this. At that point, the only issues are that the solution is more complex than simply having the feature I asked for (joining on a constant in the view) and I have to repeat it for every report I create, rather than doing it once at the view level.

I will test the subquery approach and see if it works.

Thank you to whoever deleted Yulia's comment. It was incredibly rude and failed to understand my point. Not ideal qualities for consultants supporting this product.

David, I appreciate your response, but I don't think we're on the same page. The two SQL statements below are not identical. They produce different result sets. The first is what I'm after, the second is what I think your filter approach would create.

Query 1:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
  ON TableA.col1 = TableB.col1
  AND TableA.col2 = 1

Query 2:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
  ON TableA.col1 = TableB.col1
WHERE TableA.col2 = 1

The advantage I see in the first query is that I can aggregate metrics from TableA (where I want all Table A records) and TableB (without double counting, since TableA to TableB is a many to one relationship).

If I can apply a filter at the subquery level rather than the report level, then I might be able to do this. At that point, the only issues are that the solution is more complex than simply having the feature I asked for (joining on a constant in the view) and I have to repeat it for every report I create, rather than doing it once at the view level.

I will test the subquery approach and see if it works.

Reply URL

1

David Registro ●

Yeah apologies for that previous comment, it's under discussion.

In specific cases like this I tend to rely on the consultants as they're the best report writers, so I'll pass back your additional info to another consultant to get some feedback.

Would also be interested to know if a sub-query is going to give you what you were after.

Based on all of this we will work out what idea to create. With ideas, they're essentially wish list items, so while we do read and take all on board, we always aim to provide alternative methods as we have no idea when such an idea will be done, if ever.

Apologies for the previous comments!

Thanks

David

URL

1

Yulia Astrelina ●

Hi Stephen,

sorry for being rude to you. I did not really mean that. There may be some cultural differences and I did not express myself in English correctly.

regarding to your business, both your queried produces the same results however if you are not happy with extra work that you do in subqueries with filters, there is another way of doing this in a view. Just start with tableB and left join tableA applying a condition to TableA.col2 = 1.

Sometimes, it is not possible to flip joins direction in a view without breaking the existing reports. In this case, your suggestion makes sense. However, I'll leave Yellowfin product team to decide on that.

There is another way to do your filter with left join. This will depend how big your TableB. You can do a virtual table where you can add a dummy column to tableB like SELECT col1, 1 as col2 FROM TableB. Then you can join it TableA in the way you described.

Regards,

Yulia

Reply URL

Allow joins to use constant values or reference codes

Replies (3)