Hi I am not sure how to utilise case statement in a join... I have read about using select but not sure how to apply it.
I have 2 queries [Alloc] and [BKG] I want to join using below case statement, if I take away case then I end up with VVD duplicating in all weeks...
case when
[Alloc].[VVD] is null then
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code]
else
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
end
What happens if you add paren unparen like ...
(([Alloc].[Service Lane] = [BKG]. [Service Lane]) and ([Alloc]. [Port] = [BKG]. [First POL Location Code]))
... and similar for your
else
thank you but still same result, as per attached only vsl 2 should show in that week as all the others fall in other weeks, I cannot seem to get them under the right weeks?
Quote from: therese1 on 10 Dec 2018 05:09:29 PM
Hi I am not sure how to utilise case statement in a join... I have read about using select but not sure how to apply it.
I have 2 queries [Alloc] and [BKG] I want to join using below case statement, if I take away case then I end up with VVD duplicating in all weeks...
case when
[Alloc].[VVD] is null then
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code]
else
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
end
I don't see where week enters into your join logic. You could try using a boolean expression instead such as below, but perhaps also consider whether your join includes all the elements required.
(
[Alloc].[VVD] is null then
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code]
)
or
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
)
Quote from: Lynn on 11 Dec 2018 02:19:46 AM
I don't see where week enters into your join logic. You could try using a boolean expression instead such as below, but perhaps also consider whether your join includes all the elements required.
(
[Alloc].[VVD] is null and
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code]
)
or
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
)
I just tweaked Lynn's snippet to replace a naughty 'then' with an 'and' :)
Quote from: MFGF on 11 Dec 2018 03:17:15 AM
I just tweaked Lynn's snippet to replace a naughty 'then' with an 'and' :)
Ooops...thanks :)
many thanks guys... can I do something like adding For SLS_WK? this week is only in BKG, I have already done group span SLS_WK
(
[Alloc].[VVD] is null and
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code]
)
or
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
)
I have it now to the right vessels in the right weeks, but issue is it is triplicating lines for all vessels per port.
So below is the allocation table to match data against and get variance all ports per VVD are giving 3 lines per port, so all 3 different allocations if it equal vvd or not?
Service Lane VVD DT FRM DT TO Port allocation
AAA YTTT 01/09/2018 AUMEL 92
AAA YTTT 01/09/2018 AUSYD 83
AAA YTTT 01/09/2018 AUBNE 140
AAA 01/09/2018 AUMEL 152
AAA 01/09/2018 AUSYD 80
AAA 01/09/2018 AUBNE 99
AAA MBRT 01/09/2018 AUMEL 110
AAA MBRT 01/09/2018 AUSYD 76
AAA MBRT 01/09/2018 AUBNE 50
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] <>[BKG].[First VVD Vessel Code]
)
or
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
)
Quote from: therese1 on 12 Dec 2018 06:58:02 PM
I have it now to the right vessels in the right weeks, but issue is it is triplicating lines for all vessels per port.
So below is the allocation table to match data against and get variance all ports per VVD are giving 3 lines per port, so all 3 different allocations if it equal vvd or not?
Service Lane VVD DT FRM DT TO Port allocation
AAA YTTT 01/09/2018 AUMEL 92
AAA YTTT 01/09/2018 AUSYD 83
AAA YTTT 01/09/2018 AUBNE 140
AAA 01/09/2018 AUMEL 152
AAA 01/09/2018 AUSYD 80
AAA 01/09/2018 AUBNE 99
AAA MBRT 01/09/2018 AUMEL 110
AAA MBRT 01/09/2018 AUSYD 76
AAA MBRT 01/09/2018 AUBNE 50
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] <>[BKG].[First VVD Vessel Code]
)
or
(
[Alloc].[Service Lane] = [BKG].[Service Lane] and
[Alloc].[Port] = [BKG].[First POL Location Code] and
[Alloc].[VVD] = [BKG].[First VVD Vessel Code]
)
If I'm reading your results correctly, you have three different VVD values for each port, and you're seeing the results corresponding to these. If you want to see a single row for each port, remove the VVD item from your list and your query.
Cheers!
MF.