12-15-2016 12:33 PM
SELECT usr~usrnam FROM sosu INNERJOIN soud AS sub ON sosu~subno = sub~usrno AND sosu~subyr = sub~usryr AND sosu~subtp = sub~usrtp INNERJOIN soud AS usr ON sosu~usrno = usr~usrno AND sosu~usryr = usr~usryr AND sosu~usrtp = usr~usrtp WHERE sub~usrnam = is_frontend-front_userid AND sosu~proti = c_checked AND sosu~proto = c_checked AND(( sosu~sbdat <sy-datum AND sosu~sedat >sy-datum )OR( sosu~sbdat =sy-datum AND sosu~sbtim <sy-uzeit )OR( sosu~sedat =sy-datum AND sosu~setim >sy-uzeit )).IFsy-subrc EQ0.
12-15-2016 12:53 PM
I would try a more readily understandable code like
AND ( sosu~sbdat LT sy-datum OR ( sosu~sbdat EQ sy-datum AND sosu~sbtim LT sy-uzeit ) ) " from
AND ( sosu~sedat GT sy-datum OR ( sosu~sedat EQ sy-datum AND sosu~setim GT sy-uzeit ) ). " to
Also correct typos in your code...
Regards,
Raymond
12-15-2016 11:01 PM
Not sure what exactly is definition of "good" here but in addition to what Raymond said - if you're using aliases at least chose more descriptive names. USR and SUB are too cryptic.
And INNER is default JOIN, so simply JOIN is sufficient. But the short form is just my personal preference, others could be in favor of the full form.
The potential performance is another story. I don't know these tables by heart but hopefully you've already checked if you're using the key fields as appropriate.
12-16-2016 4:53 PM
I tried laying this code out I think as you intended in a text editor and came up with this...
It looks like what you were trying to ask specifically was the recursive joins with the same table; is that correct? I have never seen that before, what exactly are you trying to achieve here by doing that?
Other suggestions - 1) When you post code, even pseudocode, make sure that you format it so that people can read it without having to reconstruct it like I just did. 2) Rather than just asking - what's wrong with this, ask a specific question about it. This is not a puzzle solving forum or a mind reading forum.
12-16-2016 6:59 PM
I agree. The SCN members also can't possibly know if this query is valid for the specific requirement. "Good" is too fuzzy.
12-19-2016 9:50 AM
Ok sry for the format, i don't know why that's didn't work when i check code form.
It's not my code and i just wanted to know if its possible to have (2 innerjoin on the same table) and why its usefull 🙂 ?
Thank's for all the answer
12-16-2016 5:15 PM
Hi,
In some situations, you may find it necessary to join a table to itself, as though you were joining two separate tables. This is referred to as a self join. In the self join, the combined result consists of two rows from the same table.
see more: https://docs.thunderstone.com/site/texisman/joining_a_table_to_itself.html
Best Regards
Filipe Sardinha
12-19-2016 7:57 PM
Thank you for the information. Do you have any information on the performance impacts versus a sub-query?
12-20-2016 12:06 PM
Hi,
For example, you have 2 tables both with 20 rows..
on join the operation performed is 20 * 2.
in sub-query is 20 * 20
join works by concentrating the operation on the result of the first two tables, any subsequent joins would concentrate joining on the in-memory(or cached to disk) result of the first joined tables, and so on. less read-write needle movement.
Best Regards
Filipe Sardinha