topic Re: This select is good ? in Application Development and Automation Discussions

This select is good ?

Former Member — Thu, 15 Dec 2016 12:33:06 GMT

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.

Re: This select is good ?

RaymondGiuseppi — Thu, 15 Dec 2016 12:53:01 GMT

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

Re: This select is good ?

Jelena_Perfiljeva — Thu, 15 Dec 2016 23:01:34 GMT

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.

Re: This select is good ?

raghug — Fri, 16 Dec 2016 16:53:42 GMT

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.

Re: This select is good ?

Former Member — Fri, 16 Dec 2016 17:15:45 GMT

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

Re: This select is good ?

Jelena_Perfiljeva — Fri, 16 Dec 2016 18:59:04 GMT

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

Re: This select is good ?

Former Member — Mon, 19 Dec 2016 09:50:46 GMT

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

Re: This select is good ?

raghug — Mon, 19 Dec 2016 19:57:51 GMT

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

Re: This select is good ?

Former Member — Tue, 20 Dec 2016 12:06:47 GMT

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