Skip to Content
-6

This select is good ?

Dec 15, 2016 at 12:33 PM

103

avatar image
Former Member
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.
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Raymond Giuseppi Dec 15, 2016 at 12:53 PM
2

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

Share
10 |10000 characters needed characters left characters exceeded
Raghu Govindarajan Dec 16, 2016 at 04:53 PM
2

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.


Show 2 Share
10 |10000 characters needed characters left characters exceeded

I agree. The SCN members also can't possibly know if this query is valid for the specific requirement. "Good" is too fuzzy.

1
Former Member

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

0
Jelena Perfiljeva
Dec 15, 2016 at 11:01 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 16, 2016 at 05:15 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you for the information. Do you have any information on the performance impacts versus a sub-query?

0
Former Member
Raghu Govindarajan

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

0