October 16, 2012 at 6:17 am
Hi,
I have a requirement to create a CASE statement for the following condition:
SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Could someone please help me ?
Thanks,
Paul
October 16, 2012 at 6:22 am
pwalter83 (10/16/2012)
I have a requirement to create a CASE statement for the following condition:SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Paul, without more information, specifically the tables DDL, it's going to be hard to guess what you want.
October 17, 2012 at 8:40 am
robert.gerald.taylor (10/16/2012)
pwalter83 (10/16/2012)
I have a requirement to create a CASE statement for the following condition:SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Paul, without more information, specifically the tables DDL, it's going to be hard to guess what you want.
Actually, this just needs to be interpreted or converted to a SQL query, I am not sure why you need DDL in this case ?
this should be something like:
COUNT(case when (NCV_BL.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV_BL.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) THEN TEU end) AS XYZ
It's just the part where it says ''for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL'' that I don't know how to convert.
Could someone have any idea about it ?
Thanks,
Paul
October 17, 2012 at 12:59 pm
The request for the DDL was so we could see the tables and have SOME idea of what you're talking about.
Sorry, I'm unable to be of help.
Rob
October 17, 2012 at 1:34 pm
pwalter83 (10/17/2012)
robert.gerald.taylor (10/16/2012)
pwalter83 (10/16/2012)
I have a requirement to create a CASE statement for the following condition:SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Paul, without more information, specifically the tables DDL, it's going to be hard to guess what you want.
Actually, this just needs to be interpreted or converted to a SQL query, I am not sure why you need DDL in this case ?
this should be something like:
COUNT(case when (NCV_BL.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV_BL.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) THEN TEU end) AS XYZ
It's just the part where it says ''for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL'' that I don't know how to convert.
Could someone have any idea about it ?
Thanks,
Paul
Keep in mind that we can't see what you see and we have no knowledge of your project or your tables. The column names mean nothing to me so I couldn't begin to offer much help. It sounds like maybe you are part way to a solution with the query you posted.
It's just the part where it says ''for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL'' that I don't know how to convert.
That is all just muttering to me because none of that makes any sense out of context.
With ddl of the tables involved it MIGHT make some sense, but without it there is nothing anybody can do to help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2012 at 7:17 pm
To add my two cents, the English language is sometimes at best ambiguous, so problem descriptions in narrative are often unclear.
Most of the senior posters here are code-talkers - they let the code do the talking. Seeing the input and expected results often results in instant clarity.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 18, 2012 at 5:43 am
Sean Lange (10/17/2012)
pwalter83 (10/17/2012)
robert.gerald.taylor (10/16/2012)
pwalter83 (10/16/2012)
I have a requirement to create a CASE statement for the following condition:SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Paul, without more information, specifically the tables DDL, it's going to be hard to guess what you want.
Actually, this just needs to be interpreted or converted to a SQL query, I am not sure why you need DDL in this case ?
this should be something like:
COUNT(case when (NCV_BL.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV_BL.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) THEN TEU end) AS XYZ
It's just the part where it says ''for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL'' that I don't know how to convert.
Could someone have any idea about it ?
Thanks,
Paul
Keep in mind that we can't see what you see and we have no knowledge of your project or your tables. The column names mean nothing to me so I couldn't begin to offer much help. It sounds like maybe you are part way to a solution with the query you posted.
It's just the part where it says ''for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL'' that I don't know how to convert.
That is all just muttering to me because none of that makes any sense out of context.
With ddl of the tables involved it MIGHT make some sense, but without it there is nothing anybody can do to help you.
ok i bow to all your demands...please find the DDLs and sample data below:
--------------------------------
CREATE TABLE [dbo].[NCV_BL](
[BL_ID] [decimal](10, 0) NOT NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](10) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](12) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[TEU] [decimal] (10,3) null,
[PLACE_OF_RECEIPT] [nvarchar](5) NULL,
[PLACE_OF_DELIVERY] [nvarchar](5) NULL,
[SAISAN_MONTH] [nvarchar](6) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_PORT_CONTROL](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,
[PORT_CD] [varchar](5) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_BILL_OF_LADING](
[BL_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NULL
) ON [PRIMARY]
INSERT INTO [NCV_BL]
VALUES ('16789928','CNYTN','EGPSD','HJPA','0112W','W', '1.000','CNYTN','EGPSW','200802')
INSERT INTO [NCV_BL]
VALUES ('16823117','USORF','INNSA','ALSAB','0076E','E','1.000',,'USCHI', 'INTKD','200908')
INSERT INTO [NCV_BL]
VALUES ('16467030','CNSHA','NLRTM','COSHEL','037W','W', '2.000','CNSHA','NLRTM','201009')
INSERT INTO [NCV_BL]
VALUES ('12381896','DEHAM','CNSHA','YMUNT','0030E','E','2.000','DEHAM','CNSHA','201101')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('669246', '1230118')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('761970', '1255321')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696131', '1261443')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696183', '1261585')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261585','GBFXT')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261443','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1230118','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1255321','INNSA')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696183','YMUNT')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696131','COSHEL')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('669246','HJPA')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('761970','ALSAB')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('12381896','YMUNT')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16467030','COSHEL')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16789928','HJPA')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16823117','ALSAB')
--------------------------------
below is the query that would join all the tables above (including the incomplete code for the requirement).
-------------------------------
select COUNT(case when (NCV_BL.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV_BL.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) THEN TEU end) AS XYZ
from ncv_bl nb
join MG_BILL_OF_LADING bol
on bol.BL_ID = nb.BL_ID
join MG_VSLVOY_HEADER vh
on bol.VESSEL_CD = vh.vessel_cd
join MG_VSLVOY_PORT_CONTROL vpc
on vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
join MG_VSLVOY_SCHEDULE vs
on vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID
-------------------------------
The requirement I have is - Create a CASE statement for the following condition:
SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.
Thanks,
Paul
October 18, 2012 at 7:26 am
Thank you for the ddl and sample data. However your requirement mentions a column TEU. This is not present on any of the tables you provided.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 7:40 am
Sean Lange (10/18/2012)
Thank you for the ddl and sample data. However your requirement mentions a column TEU. This is not present on any of the tables you provided.
Sorry Sean, I realized that and have already added it to the ncv_bl table.
Thanks again.
October 18, 2012 at 8:15 am
pwalter83 (10/18/2012)
Sean Lange (10/18/2012)
Thank you for the ddl and sample data. However your requirement mentions a column TEU. This is not present on any of the tables you provided.Sorry Sean, I realized that and have already added it to the ncv_bl table.
Thanks again.
You actual requirements are still pretty much a mystery but based on your sample query I tossed this together.
select Sched.*, nb.POL_LOCATION_CD, SUM(case when sched.PORT_CD IS NULL then TEU else 0 end) as TEU
from ncv_bl nb
join MG_BILL_OF_LADING bol on bol.BL_ID = nb.BL_ID
join MG_VSLVOY_HEADER vh on bol.VESSEL_CD = vh.vessel_cd
join MG_VSLVOY_PORT_CONTROL vpc on vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
join MG_VSLVOY_SCHEDULE vs on vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID
left join (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) sched on sched.PORT_CD = nb.POL_LOCATION_CD or sched.PORT_CD = nb.POD_LOCATION_CD
group by sched.PORT_CD, POL_LOCATION_CD
The problem you were going to run into once you got your case statement situated is that you can't perform aggregate functions when the is a subquery.
I hope this gets you pointed in the right direction. I would help more but I am totally swamped at work today.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 9:45 am
Sean Lange (10/18/2012)
pwalter83 (10/18/2012)
Sean Lange (10/18/2012)
Thank you for the ddl and sample data. However your requirement mentions a column TEU. This is not present on any of the tables you provided.Sorry Sean, I realized that and have already added it to the ncv_bl table.
Thanks again.
You actual requirements are still pretty much a mystery but based on your sample query I tossed this together.
select Sched.*, nb.POL_LOCATION_CD, SUM(case when sched.PORT_CD IS NULL then TEU else 0 end) as TEU
from ncv_bl nb
join MG_BILL_OF_LADING bol on bol.BL_ID = nb.BL_ID
join MG_VSLVOY_HEADER vh on bol.VESSEL_CD = vh.vessel_cd
join MG_VSLVOY_PORT_CONTROL vpc on vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
join MG_VSLVOY_SCHEDULE vs on vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID
left join (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) sched on sched.PORT_CD = nb.POL_LOCATION_CD or sched.PORT_CD = nb.POD_LOCATION_CD
group by sched.PORT_CD, POL_LOCATION_CD
The problem you were going to run into once you got your case statement situated is that you can't perform aggregate functions when the is a subquery.
I hope this gets you pointed in the right direction. I would help more but I am totally swamped at work today.
October 18, 2012 at 9:50 am
Ough man! You need to split this monster into parts...
That will make it much more readable and most likely much more efficient...
October 18, 2012 at 9:56 am
Which tables all of these PLACE_OF_DELIVERY, PLACE_OF_RECEIPT and saisan_month columns come from?
They are not in your DDL and you didn't use table aliases in your query...
October 19, 2012 at 2:43 am
Eugene Elutin (10/18/2012)
Which tables all of these PLACE_OF_DELIVERY, PLACE_OF_RECEIPT and saisan_month columns come from?They are not in your DDL and you didn't use table aliases in your query...
Thanks Eugene,
The PLACE_OF_DELIVERY, PLACE_OF_RECEIPT and saisan_month columns come from NCV_BL and as they are unique to this table, I didnt use tables aliases for them. I have updated the DDLs and sample data and they can found below:
--------------------------------
CREATE TABLE [dbo].[NCV_BL](
[BL_ID] [decimal](10, 0) NOT NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](10) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](12) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[TEU] [decimal] (10,3) null,
[PLACE_OF_RECEIPT] [nvarchar](5) NULL,
[PLACE_OF_DELIVERY] [nvarchar](5) NULL,
[SAISAN_MONTH] [nvarchar](6) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_PORT_CONTROL](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,
[PORT_CD] [varchar](5) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MG_BILL_OF_LADING](
[BL_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NULL
) ON [PRIMARY]
INSERT INTO [NCV_BL]
VALUES ('16789928','CNYTN','EGPSD','HJPA','0112W','W', '1.000','CNYTN','EGPSW','200802')
INSERT INTO [NCV_BL]
VALUES ('16823117','USORF','INNSA','ALSAB','0076E','E','1.000',,'USCHI', 'INTKD','200908')
INSERT INTO [NCV_BL]
VALUES ('16467030','CNSHA','NLRTM','COSHEL','037W','W', '2.000','CNSHA','NLRTM','201009')
INSERT INTO [NCV_BL]
VALUES ('12381896','DEHAM','CNSHA','YMUNT','0030E','E','2.000','DEHAM','CNSHA','201101')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('669246', '1230118')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('761970', '1255321')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696131', '1261443')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696183', '1261585')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261585','GBFXT')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261443','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1230118','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1255321','INNSA')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696183','YMUNT')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696131','COSHEL')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('669246','HJPA')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('761970','ALSAB')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('12381896','YMUNT')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16467030','COSHEL')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16789928','HJPA')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16823117','ALSAB')
--------------------------------
Thanks,
Paul
October 24, 2012 at 7:32 am
Eugene Elutin (10/18/2012)
Which tables all of these PLACE_OF_DELIVERY, PLACE_OF_RECEIPT and saisan_month columns come from?They are not in your DDL and you didn't use table aliases in your query...
Hi Eugene,
I have amended my query and its more easy to understand now and I have also developed some code (still incomplete and in bold) for the issue I have:
select
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2008' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO08,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2009' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO09,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2010' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO10,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2011' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO11
FROM NCV_BL NB
INNER JOIN MiniGapp..MG_BILL_OF_LADING BOL
ON NB.BL_ID = BOL.BL_ID
LEFT JOIN
(
SELECT distinct
MAX(NCV.BL_ID) AS BL_ID,
SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'GB%' THEN TEU END) AS GB_TS08,
SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'DE%' THEN TEU END) AS DE_TS08
FROM NCV_BL NCV
INNER JOIN MG_BILL_OF_LADING AS bol ON bol.BL_ID = NCV.BL_ID
INNER JOIN MG_VSLVOY_HEADER AS vh ON bol.VESSEL_CD = vh.VESSEL_CD
INNER JOIN MG_VSLVOY_PORT_CONTROL AS vpc ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
INNER JOIN MG_VSLVOY_SCHEDULE AS vs ON vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID
WHERE NCV.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE)
) NA
ON NB.BL_ID = NA.BL_ID
Thanks,
Paul
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply