PS Query & SQL

I think they are talking about how you select your query records as you are querying. When you build queries, you often have two methods of choosing your second and subsequent records. I do most of my query from HCM, but you can do the same I'm about to mention below, from any db with any records you are familiar with.

If you use Query Manager from the web:

Hierarchy Join:  Start a new query and from the Records tab, look up and select POSITION_DATA for your first record. Now, look down the list of fields in the Position Data record, and you'll see links next to some of them for various tables. Use the link to ACTION_TABLE and select the Standard Join. Now, go over to your criteria tab and all you will see are two rows of criteria, each specifying the effective date for records A and B.

Non-hierarchy join:  Start a new query and from the Records tab, look up and select "POSITION_DATA for your first record. Go back to the Records tab, look up "ACTION_TABLE", select "Join Record" and from the next pop-up allow a Standard Join, and then near the bottom you need to select "A – POSITION_DATA" as the record you will be joining your second table to. This will create another pop-up where it is suggesting join criteria and asking if this is what you want. Go ahead and add that criteria. Now, go to your Criteria tab. You should see three rows of criteria. The first is the effective date for your record A. The second shows how the Action field is the same from table A to table B (this is how you know it is NOT a hierarchy join). Third criteria is effective date for B.

If you use App Designer it is very similar:

The difference in selecting hierarchy join vs non-hierarchy has to do with using the little + symbol next to a field from your first table to select the second table. If you use the + symbol you will get a hierarchy join with the same results on the criteria tab as you would get from the web. If you create a new query and instead look up the second table for your query and apply criteria from the pop-up, then you get more criteria showing in the criteria tab.

I don't use hierarchy joins, anymore. We've had misc, seemingly random problems with hierarchy joins over the years. The real kicker, though, was about 15 years ago, we did a major upgrade, and at go-live, I found myself re-writing hundreds of queries that suddenly malfunctioned. It was weird in that the hierarchy hadn't changed on the records, and I could re-write a hierarchy join exactly the same as the original, but the original would not run. We had some very upset end-users, and I put in a lot of midnight oil fixing these. Our non-hierarchy queries have never had issues that I can recall.

I think they are talking about how you select your query records as you are querying. When you build queries, you often have two methods of choosing your second and subsequent records. I do most of my query from HCM, but you can do the same I'm about to mention below, from any db with any records you are familiar with.

If you use Query Manager from the web:

Hierarchy Join:  Start a new query and from the Records tab, look up and select POSITION_DATA for your first record. Now, look down the list of fields in the Position Data record, and you'll see links next to some of them for various tables. Use the link to ACTION_TABLE and select the Standard Join. Now, go over to your criteria tab and all you will see are two rows of criteria, each specifying the effective date for records A and B.

Non-hierarchy join:  Start a new query and from the Records tab, look up and select "POSITION_DATA for your first record. Go back to the Records tab, look up "ACTION_TABLE", select "Join Record" and from the next pop-up allow a Standard Join, and then near the bottom you need to select "A – POSITION_DATA" as the record you will be joining your second table to. This will create another pop-up where it is suggesting join criteria and asking if this is what you want. Go ahead and add that criteria. Now, go to your Criteria tab. You should see three rows of criteria. The first is the effective date for your record A. The second shows how the Action field is the same from table A to table B (this is how you know it is NOT a hierarchy join). Third criteria is effective date for B.

If you use App Designer it is very similar:

The difference in selecting hierarchy join vs non-hierarchy has to do with using the little + symbol next to a field from your first table to select the second table. If you use the + symbol you will get a hierarchy join with the same results on the criteria tab as you would get from the web. If you create a new query and instead look up the second table for your query and apply criteria from the pop-up, then you get more criteria showing in the criteria tab.

I don't use hierarchy joins, anymore. We've had misc, seemingly random problems with hierarchy joins over the years. The real kicker, though, was about 15 years ago, we did a major upgrade, and at go-live, I found myself re-writing hundreds of queries that suddenly malfunctioned. It was weird in that the hierarchy hadn't changed on the records, and I could re-write a hierarchy join exactly the same as the original, but the original would not run. We had some very upset end-users, and I put in a lot of midnight oil fixing these. Our non-hierarchy queries have never had issues that I can recall.