• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

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';
0
AXISHK
Asked:
AXISHK
2 Solutions
 
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
 
PortletPaulCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
PortletPaulCommented:
NO...

the pathways to [OPERATIONGROUP]

are through:
b.TOOPRID = c.OPRID
b.FROMOPRID = d.OPRID
0
 
PortletPaulCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now