Using Case statement in Join

[therese1]

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...

Code Select Expand


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


[Cognos_Jan2017]

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

[therese1]

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?

[Lynn]

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...

Code Select Expand


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.

Code Select Expand


(
[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]
)


[MFGF]

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.

Code Select Expand


(
[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' :)

[Lynn]

I just tweaked Lynn's snippet to replace a naughty 'then' with an 'and' :)

Ooops...thanks  :)

[therese1]

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

Code Select Expand


(
[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]
)


[therese1]

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

Code Select Expand

(
[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]
)

[MFGF]

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

Code Select Expand

(
[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.