March 5, 2019 at 6:16 am
I am some what of a self taught developer and was looking for some guidance if the query I have written is the best way to pull the information i am looking for or if another option would of been better suited.
The table wohead has a 1 to many relationship on both wosegdetl and woseg. What i am looking for is any work order that had an oeptype of 5 and kmfg not in ('111','112',102) and at the segment level the oedept was opened as 'SCH'
in essence a segment can have numerous labor entries which are oeptype 5 but if the segment has any ('111','112','102') labor at all i don't want it to show in the result set regardless of other kmfg entries as long as its scheduled. Below is what I came up with and any guidance is appreciated.
select kbranch as [Branch]
,kworkorder as [Work Order]
,cast(oedate as date) as [Date Closed]
,kcustnum as [Customer #]
,(select custname from custmast where kcustnum = wohead.kcustnum and custsnum = wohead.custsnum) as [Customer]
from wohead
where cast(oedate as date) >= '2018-06-01' and oedate is not null
and exists (select kbranch , kworkorder from wosegdetl where oeptype = '5' and kmfg not in ('111','112','102') and kbranch = wohead.kbranch and kworkorder = wohead.kworkorder and kswoseg <> '800'
and not exists (select kbranch , kworkorder from wosegdetl as wsd where oeptype = '5' and kmfg in ('111','112','102') and kbranch = wosegdetl.kbranch and kworkorder = wosegdetl.kworkorder and kswoseg = wosegdetl.kswoseg)
and exists (select kbranch , kworkorder from woseg where oedept= 'SCH' and kbranch = wosegdetl.kbranch and kworkorder = wosegdetl.kworkorder and kswoseg = wosegdetl.kswoseg))
March 5, 2019 at 6:42 am
Your query looks ok, exists and not exists are fast methods of filtering.
A few of points:
I would give every table an alias and use it on every column in the query. This makes the query more maintainable.
I would use the return key a bit more and put some new lines in the query to make it more readable.
I don't see why you have a bracket from the first exists to the last exists. More standard to write each and exists in its own bracket.
I've reformatted your query to how I'd format it. In my opinion it's easier to read and maintain (other people might disagree):select w.kbranch as [Branch],
w.kworkorder as [Work Order],
cast(w.oedate as date) as [Date Closed],
w.kcustnum as [Customer #],
c.custname as [Customer]
from wohead w
inner join custmast c
on c.kcustnum = w.kcustnum
and c.custsnum = w.custsnum
where cast(w.oedate as date) >= '2018-06-01'
/* and oedate is not null */ -- Not needed as previous line stops null
and exists (select *
from wosegdetl w1
where w1.oeptype = '5'
and w1.kmfg not in ('111','112','102')
and w1.kbranch = w.kbranch
and w1.kworkorder = w.kworkorder
and w1.kswoseg <> '800')
and not exists (select *
from wosegdetl as wsd
where wsd.oeptype = '5'
and wsd.kmfg in ('111','112','102')
and wsd.kbranch = w.kbranch
and wsd.kworkorder = w.kworkorder
and wsd.kswoseg = w.kswoseg)
and exists (select *
from woseg w2
where w2.oedept= 'SCH'
and w2.kbranch = w.kbranch
and w2.kworkorder = w.kworkorder
and w2.kswoseg = w.kswoseg)
March 5, 2019 at 7:07 am
thank you for the reply i have a couple of return question.
1. is it more beneficial to use a join rather than a sub select in the case of custmast in the main select.
2. in terms of your question about the outer brackets between the first and last exists i assumed I had to nest them since the segment is not in wohead but is in the first exists i need a way to compare the first exists with the second not exists. Like if you look at what you provided
and not exists (select *
from wosegdetl as wsd
where wsd.oeptype = '5'
and wsd.kmfg in ('111','112','102')
and wsd.kbranch = w.kbranch
and wsd.kworkorder = w.kworkorder
and wsd.kswoseg = w.kswoseg)
the and wsd.kswoseg=w.kswoseg will not work since w.kswoseg isint a valid column. and this is the trouble i was having if i had done it correctly.
March 5, 2019 at 7:37 am
lucaskhall - Tuesday, March 5, 2019 7:07 AMthank you for the reply i have a couple of return question.1. is it more beneficial to use a join rather than a sub select in the case of custmast in the main select.
2. in terms of your question about the outer brackets between the first and last exists i assumed I had to nest them since the segment is not in wohead but is in the first exists i need a way to compare the first exists with the second not exists. Like if you look at what you provided
and not exists (select *
from wosegdetl as wsd
where wsd.oeptype = '5'
and wsd.kmfg in ('111','112','102')
and wsd.kbranch = w.kbranch
and wsd.kworkorder = w.kworkorder
and wsd.kswoseg = w.kswoseg)the and wsd.kswoseg=w.kswoseg will not work since w.kswoseg isint a valid column. and this is the trouble i was having if i had done it correctly.
1. I always use a join instead of a subselect if possible. In fact if I can't get rid of the subselect with an inner join I put it in a cross apply to get it out of the way of the list of values.
2. I see what you mean. This is why it's important to alias tables and include that alias in every reference to a column. Also, why it's important to indent your code so people (including yourself) can see your intentions:
I would rewrite the code like this:select w.kbranch as [Branch],
w.kworkorder as [Work Order],
cast(w.oedate as date) as [Date Closed],
w.kcustnum as [Customer #],
c.custname as [Customer]
from wohead w
inner join custmast c
on c.kcustnum = w.kcustnum
and c.custsnum = w.custsnum
where cast(w.oedate as date) >= '2018-06-01'
and exists (select *
from wosegdetl w1
where w1.oeptype = '5'
and w1.kmfg not in ('111','112','102')
and w1.kbranch = w.kbranch
and w1.kworkorder = w.kworkorder
and w1.kswoseg <> '800'
and not exists (select *
from wosegdetl as wsd
where wsd.oeptype = '5'
and wsd.kmfg in ('111','112','102')
and wsd.kbranch = w1.kbranch
and wsd.kworkorder = w1.kworkorder
and wsd.kswoseg = w1.kswoseg)
and exists (select *
from woseg w2
where w2.oedept= 'SCH'
and w2.kbranch = w1.kbranch
and w2.kworkorder = w1.kworkorder
and w2.kswoseg = w1.kswoseg))
March 5, 2019 at 8:17 am
great thank you for all your help and advice i will take it into account in the future.
Thank you again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply