March 28, 2007 at 7:49 am
Does anyone see a way that I might Improve/Simplify the following query?
Declare
@JOnum as char(10)
Set @JOnum = 'I2B98-0000'
SELECT
jodbom.fjobno
,[fbompart],[fbomrev],[fbomdesc],[fparent],[fparentrev],[factqty],[fbommeas],[fqty_iss]
,dt.fclot,dt.fccompany
FROM
jodbom
Left JOIN
(
/*Add the Company name for the lot numbers used in the JO*/
Select qalotc.fclot, qalotc.fcpartno, ct.fccompany
from qalotc
Join
(
/*Select all the receivers that match the doc number in the lot number table*/
SELECT fccompany, qalotc.fclot, qalotc.fcpartno
FROM rcitem
Join qalotc on RcItem.fReceiver + RcItem.fItemNo = fcdoc
WHERE (fctype = 'R')
) as ct
/*Retrive only the Company ,Lot and part no for the lots used in JO*/
On ct.fclot = qalotc.fclot
Where qalotc.fcuseindoc = @JOnum
) as dt
On
dt.fcpartno = fbompart
Where fjobno = @JOnum and fqty_iss > 0
March 28, 2007 at 8:26 am
It seems the second inner join isn't needed. Collapse this
/*Add the Company name for the lot numbers used in the JO*/
Select qalotc.fclot, qalotc.fcpartno, ct.fccompany
from qalotc
Join
(
/*Select all the receivers that match the doc number in the lot number table*/
SELECT fccompany, qalotc.fclot, qalotc.fcpartno
FROM rcitem
Join qalotc on RcItem.fReceiver + RcItem.fItemNo = fcdoc
WHERE (fctype = 'R')
) as ct
/*Retrive only the Company ,Lot and part no for the lots used in JO*/
On ct.fclot = qalotc.fclot
to
SELECT fccompany, qalotc.fclot, qalotc.fcpartno
FROM rcitem
inner Join qalotc on RcItem.fReceiver + RcItem.fItemNo = fcdoc
WHERE (fctype = 'R')
and qalotc.fcuseindoc = @JOnum
Doesn't seem to need to be wrapped. It's like a query that's then wrapped in a select *
March 28, 2007 at 8:44 am
Yes I thought that at first but, not all of them have lot numbers. So I have to put out the ones that do and get the company for them only.
Here is the results with the second join:
I2B98-0000 SA250-4 000 .250(+/-.020)27.27WX26.95LX16.5L 6311263-2 D 1.00000 EA 1.00000 S8021 SPARTECH POLYCAST
I2B98-0000 SA375-1 000 .375(+/-.030)27.27WX26.95LX16.5L 6311263-2 D 1.00000 EA 1.00000 S8051 SPARTECH POLYCAST
I2B98-0000 890B1/2 000 SEALANT, POLYSULFIDE #693 SEM KIT 6311263-2 D 3.00000 EA 3.00000 NULL NULL
I2B98-0000 RTV103 000 RTV (BLACK) 6311263-2 D 6.00000 OZ 6.00000 NULL NULL
I2B98-0000 6311263-20 D COCKPIT SIDE WINDOW SEAL RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311265-8 B BUSHING,SILICONE .234"X.362"X.650" 6311263-2 D 31.00000 EA 31.00000 NULL NULL
I2B98-0000 6311263-26 D SIDE COCKPIT INNER FROSTPANE RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311263-18 D COCKPIT SIDE SEAL RETAINER RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 DB31 000 SAFLEX, POLYVINYL BUTYRAL 60" 6311263-2 D 12.00000 SQF 12.00000 NULL NULL
I2B98-0000 6311263-24 D SIDE COCKPIT OUTER FROSTPANE RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311265-11 B SPACER, STEEL (.213"X.250"X .67") 6311263-2 D 31.00000 EA 31.00000 NULL NULL
I2B98-0000 6311263-16 D COCKPIT SIDE WINDOW RETAINER RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
Here is the results without:
I2B98-0000 890B1/2 000 SEALANT, POLYSULFIDE #693 SEM KIT 6311263-2 D 3.00000 EA 3.00000 NULL NULL
I2B98-0000 SA250-4 000 .250(+/-.020)27.27WX26.95LX16.5L 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 RTV103 000 RTV (BLACK) 6311263-2 D 6.00000 OZ 6.00000 NULL NULL
I2B98-0000 6311263-20 D COCKPIT SIDE WINDOW SEAL RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311265-8 B BUSHING,SILICONE .234"X.362"X.650" 6311263-2 D 31.00000 EA 31.00000 NULL NULL
I2B98-0000 6311263-26 D SIDE COCKPIT INNER FROSTPANE RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311263-18 D COCKPIT SIDE SEAL RETAINER RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 DB31 000 SAFLEX, POLYVINYL BUTYRAL 60" 6311263-2 D 12.00000 SQF 12.00000 NULL NULL
I2B98-0000 6311263-24 D SIDE COCKPIT OUTER FROSTPANE RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 6311265-11 B SPACER, STEEL (.213"X.250"X .67") 6311263-2 D 31.00000 EA 31.00000 NULL NULL
I2B98-0000 6311263-16 D COCKPIT SIDE WINDOW RETAINER RH 6311263-2 D 1.00000 EA 1.00000 NULL NULL
I2B98-0000 SA375-1 000 .375(+/-.030)27.27WX26.95LX16.5L 6311263-2 D 1.00000 EA 1.00000 NULL NULL
March 28, 2007 at 8:56 am
Wasn't 100% sure which columns went with which tables but try this
Declare @JOnum as char(10)
Set @JOnum = 'I2B98-0000'
SELECT
jodbom.fjobno
,jodbom.fbompart
,jodbom.fbomrev
,jodbom.fbomdesc
,jodbom.fparent
,jodbom.fparentrev
,jodbom.factqty
,jodbom.fbommeas
,jodbom.fqty_iss
,qalotc.fclot
,rcitem.fccompany
FROM
dbo.jodbom jodbom
LEFT JOIN
dbo.qalotc qalotc
JOIN
dbo.rcitem rcitem
ON
rcitem.fReceiver + rcitem.fItemNo = qalotc.fcdoc AND
rcitem.fctype = 'R' AND
qalotc.fcuseindoc = @JOnum
ON
qalotc.fcpartno = jodbom.fbompart
WHERE
jodbom.fjobno = @JOnum and
jodbom.fqty_iss > 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply