SQL Query

Question

Post reply

SQL Query

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Hi Paul

Your sample data set doesn't have a matching row between MG_VSLVOY_HEADER and MG_VSLVOY_PORT_CONTROL:

SELECT ncv.*, vh.VSLVOY_HEADER_ID

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh

ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD

AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM

AND ncv.saisan_LEG_CD = vh.LEG_CD

--JOIN MG_VSLVOY_PORT_CONTROL vpc

--ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

--WHERE NOT EXISTS (

--SELECT 1

--FROM MG_VSLVOY_SCHEDULE vs

--WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID

--AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)

--)

--AND BL_ID = '17231410'

Here's your sample data script for those who don't like attachments:

-- sample data script

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]

INSERT INTO [NCV_BL]

VALUES ('17231410','DKAAR','IDJKT','HNJAFR','0002E','E', '4','DEYTN','EGPSW','200802')

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]

INSERT INTO [MG_VSLVOY_PORT_CONTROL]

VALUES ('1629990', '1342190'),('1629991', '1342191'),('1629992', '1342192'),

('1629993', '1342193'),('1629994', '1342194'),('1629995', '1342195'),

('1629996', '1342196'),('1629997', '1342197'),('1630001', '1342201'),

('1629998', '1379563'),('1629999', '1379564'),('1630000', '1379565')

CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](

[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,

[PORT_CD] [varchar](5) NOT NULL

) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_SCHEDULE]

VALUES ('1342190','DEHAM'),('1342191','NLRTM'),('1342192','FRLEH'),('1342193','ESALG'),('1342194','EGSUZ'),('1342195','SGSIN'),

('1342196','CNYTN'),('1342197','HKHKG'),('1342201','CNSHA'),('1379563','CNTXG'),('1379564','KRKAN'),('1379565','KRPUS')

CREATE TABLE [dbo].[MG_VSLVOY_HEADER](

[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,

[VESSEL_CD] [nvarchar](10) NOT NULL,

[VOYAGE_NUM] [nvarchar](12) NULL,

[LEG_CD] [nvarchar](1) NULL

) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_HEADER]

VALUES ('762476','HNJAFR','0002E','E'),('696131','COSHEL','0003W','W'),('669246','HJPA','0007R','R'),('761970','ALSAB','0008Q','Q')

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

ChrisM@Work (1/11/2013)
Hi Paul
I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!
Cheers
ChrisM

Voluntary job change? Or???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pwalter83 SSChampion Points: 14575 More actions · Answer 3

ChrisM@home (1/13/2013)
Hi Paul
Your sample data set doesn't have a matching row between MG_VSLVOY_HEADER and MG_VSLVOY_PORT_CONTROL:
SELECT ncv.*, vh.VSLVOY_HEADER_ID
FROM NCV_BL ncv
JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD
--JOIN MG_VSLVOY_PORT_CONTROL vpc
--ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
--WHERE NOT EXISTS (
--SELECT 1
--FROM MG_VSLVOY_SCHEDULE vs
--WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
--AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
--)
--AND BL_ID = '17231410'
Here's your sample data script for those who don't like attachments:
-- sample data script
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]
INSERT INTO [NCV_BL]
VALUES ('17231410','DKAAR','IDJKT','HNJAFR','0002E','E', '4','DEYTN','EGPSW','200802')
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]
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES ('1629990', '1342190'),('1629991', '1342191'),('1629992', '1342192'),
('1629993', '1342193'),('1629994', '1342194'),('1629995', '1342195'),
('1629996', '1342196'),('1629997', '1342197'),('1630001', '1342201'),
('1629998', '1379563'),('1629999', '1379564'),('1630000', '1379565')
CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,
[PORT_CD] [varchar](5) NOT NULL
) ON [PRIMARY]
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES ('1342190','DEHAM'),('1342191','NLRTM'),('1342192','FRLEH'),('1342193','ESALG'),('1342194','EGSUZ'),('1342195','SGSIN'),
('1342196','CNYTN'),('1342197','HKHKG'),('1342201','CNSHA'),('1379563','CNTXG'),('1379564','KRKAN'),('1379565','KRPUS')
CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [nvarchar](10) NOT NULL,
[VOYAGE_NUM] [nvarchar](12) NULL,
[LEG_CD] [nvarchar](1) NULL
) ON [PRIMARY]
INSERT INTO [MG_VSLVOY_HEADER]
VALUES ('762476','HNJAFR','0002E','E'),('696131','COSHEL','0003W','W'),('669246','HJPA','0007R','R'),('761970','ALSAB','0008Q','Q')

Hi Chris,

I am really sorry for this blunder and for wasting your precious time.

Please find the updated ddl and sample data attached.

pwalter83 SSChampion Points: 14575 More actions · Answer 4

ChrisM@Work (1/11/2013)
Hi Paul
I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!
Cheers
ChrisM

Hi Chris,

Best of luck for your next venture !!!

pwalter83 SSChampion Points: 14575 More actions · Answer 5

ChrisM@Work (1/11/2013)
Hi Paul
I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!
Cheers
ChrisM

Hi Chris,

I am still struggling with this query. Any possibility you could have a look whenever you get a chance ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

pwalter83 (1/15/2013)
ChrisM@Work (1/11/2013)
Hi Paul
I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!
Cheers
ChrisM
Hi Chris,
I am still struggling with this query. Any possibility you could have a look whenever you get a chance ?

Try this, Paul:

SELECT ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD, vh.VSLVOY_HEADER_ID, TEU

FROM NCV_BL ncv

INNER JOIN MG_VSLVOY_HEADER vh

ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD

AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM

AND ncv.saisan_LEG_CD = vh.LEG_CD

WHERE BL_ID = '17231410'

AND NOT EXISTS (

SELECT vs.PORT_CD, vpc.VSLVOY_HEADER_ID

FROM MG_VSLVOY_PORT_CONTROL vpc -- 12 rows per row in vh

INNER JOIN MG_VSLVOY_SCHEDULE vs

ON vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID

WHERE vpc.VSLVOY_HEADER_ID = vh.VSLVOY_HEADER_ID -- correlation

AND vs.PORT_CD IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD) -- correlation

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pwalter83 SSChampion Points: 14575 More actions · Answer 7

ChrisM@Work (1/15/2013)
pwalter83 (1/15/2013)
ChrisM@Work (1/11/2013)
Hi Paul
I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!
Cheers
ChrisM
Hi Chris,
I am still struggling with this query. Any possibility you could have a look whenever you get a chance ?
Try this, Paul:
SELECT ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD, vh.VSLVOY_HEADER_ID, TEU
FROM NCV_BL ncv
INNER JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD
WHERE BL_ID = '17231410'
AND NOT EXISTS (
SELECT vs.PORT_CD, vpc.VSLVOY_HEADER_ID
FROM MG_VSLVOY_PORT_CONTROL vpc -- 12 rows per row in vh
INNER JOIN MG_VSLVOY_SCHEDULE vs
ON vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
WHERE vpc.VSLVOY_HEADER_ID = vh.VSLVOY_HEADER_ID -- correlation
AND vs.PORT_CD IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD) -- correlation
)

Thanks a lot Chris, it worked perfectly well !!