SQL Statement

I'm reviewing a SQL statement written by the vendor, can I simplify the two statements into one or there is some reason behind to separate into two line ?  Tks


"inner join ROUTEOPRTABLE as c"
"inner join ROUTEOPRTABLE as d"



update BI_SALESDATA set FDATETIME = b.SUBMITDATE
from BI_SALESDATA as a
inner join ProdRouteInTransitTable as b
on a.PRODTABLE_PRODID = b.PRODID
inner join ROUTEOPRTABLE as c
on b.TOOPRID = c.OPRID
and c.OPERATIONGROUP ='P'
inner join ROUTEOPRTABLE as d
on b.FROMOPRID = d.OPRID
and d.OPERATIONGROUP ='F';
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
It looks like ROUTEOPRTABLE might be some kind of lookup table.

It first does this to get the row where the OPERATIONGROUP is "P"

inner join ROUTEOPRTABLE as c
on b.TOOPRID = c.OPRID
and c.OPERATIONGROUP ='P'

and then does this to get the row where the OPERATIONGROUP is "F"

nner join ROUTEOPRTABLE as d
on b.FROMOPRID = d.OPRID
and d.OPERATIONGROUP ='F';

Could this be done by combining the joins into one ?  Without knowing how your data relationships work, hard to say.

Is there some performance concern or you just don't like the way the code works.  Depending upon your vendor, mucking around with their code may have repercussions with any support / maintenance contracts you have in place, so tread carefully.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Joining to the same table more than once isn't unusual. The pathway for each use of that table is different, and it's clearly an operation (routing) that proceeds FROM and goes TO which are different (from 'F' to 'P').
UPDATE
      BI_SALESDATA
SET
      FDATETIME = b.SUBMITDATE
FROM BI_SALESDATA AS a
      INNER JOIN ProdRouteInTransitTable AS b
                  ON a.PRODTABLE_PRODID = b.PRODID
      INNER JOIN ROUTEOPRTABLE AS c
                  ON b.TOOPRID = c.OPRID
                        AND c.OPERATIONGROUP = 'P'
      INNER JOIN ROUTEOPRTABLE AS d
                  ON b.FROMOPRID = d.OPRID
                        AND d.OPERATIONGROUP = 'F';

Open in new window

To me this "looks OK"; but as said above without detailed knowledge of your application and the data it is not possible to be definitive.
0
AXISHKAuthor Commented:
Will the result be the same at first glance if,

inner join ROUTEOPRTABLE as c
 on b.TOOPRID = c.OPRID
 and c.OPERATIONGROUP in ('P', 'F')
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

PortletPaulfreelancerCommented:
NO...

the pathways to [OPERATIONGROUP]

are through:
b.TOOPRID = c.OPRID
b.FROMOPRID = d.OPRID
0
PortletPaulfreelancerCommented:
Actually I just deleted my own comment, I thought I may have found an equivalent using just one (more complex) join to that table, but it wasn't right.

Discuss that query with the vendor if you are that concerned about it.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wouldn't touch this query without knowing the logic behind it.
Why are you concerned about it? It's running slow?
0
awking00Commented:
It's somewhat speculative to say, without data and table structures, but it appears that the query is simply updating a date value in table A with the submission date from table B where a submission was made to a specific group ('P') on that date or from a specific group ('F') on that date.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.