July 7, 2014 at 4:14 am
Hi,
In the matrix report I am working on I need to display rows based on a condition even if no data is returned which is
COUNTRY_DSC in ('Germany', 'Austria', 'Netherlands', 'Belgium', 'France', 'Ireland', 'Sweden', 'Finland', 'Denmark', 'Norway')
In other words, I need to display rows for all the countries above even if they return null data.
The query is below:
---------------------------------------
select COUNTRY_DSC, POL_LOCATION_CD, TEU, mcc.TYPE_CD
from MG_BOOKING_DATASET mgd
inner join MG_OFFICE mo
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
right join MG_COUNTRY mc
on mo.COUNTRY_CD = mc.COUNTRY_CD
inner join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
inner join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
where Booking_status_cd in ('H','F','I','P')
---------------------------------------------
Could someone please help in this regard.
Thanks.
July 7, 2014 at 4:42 am
SELECT alias.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM MG_COUNTRY mc
LEFT join MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
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
July 7, 2014 at 4:52 am
ChrisM@Work (7/7/2014)
SELECT alias.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CDFROM MG_COUNTRY mc
LEFT join MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
Thanks a lot for your reply, Chris !
However, could you please advice how to include the rows for the following countries even if NULL data is returned for them. Actually I need to display the below list of countries in the report regardless of the TEU values.
COUNTRY_DSC in ('Germany', 'Austria', 'Netherlands', 'Belgium', 'France', 'Ireland', 'Sweden', 'Finland', 'Denmark', 'Norway')
Thanks again.
July 7, 2014 at 4:59 am
SELECT d.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
LEFT JOIN MG_COUNTRY mc
ON mc.COUNTRY_DSC = d.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
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
July 7, 2014 at 5:18 am
ChrisM@Work (7/7/2014)
SELECT d.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CDFROM (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
LEFT JOIN MG_COUNTRY mc
ON mc.COUNTRY_DSC = d.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
Thanks Chris,
I think I am not able to explain the requirement clearly.
I need to display data for all the countries for which the data exists. In addition to that I need to display the list of countries below as well on the report irrespective of whether the data exists for them or not.
The countries are:
'Germany', 'Austria', 'Czech Rep', 'Netherlands', 'Belgium', 'France', 'Ireland', 'Sweden', 'Finland', 'Denmark', 'Norway', 'Switzerland', 'Portugal', 'Spain'
Please find the screenshot for the sample output data attached. I hope I have not frustrated you.
Thanks.
July 7, 2014 at 5:42 am
SELECT mc.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM MG_COUNTRY mc
LEFT JOIN (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
ON d.COUNTRY_DSC = mc.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
WHERE mo.COUNTRY_CD IS NOT NULL OR d.COUNTRY_DSC IS NOT NULL
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
July 7, 2014 at 6:02 am
ChrisM@Work (7/7/2014)
SELECT mc.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CDFROM MG_COUNTRY mc
LEFT JOIN (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
ON d.COUNTRY_DSC = mc.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE mcc
on ber.KL_EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
WHERE mo.COUNTRY_CD IS NOT NULL OR d.COUNTRY_DSC IS NOT NULL
Thanks very much, Chris !!!
It worked now, sorry for the repeat requests.
July 7, 2014 at 6:13 am
You're welcome, no worries 🙂
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply