Display ALL rows even if no data exists

  • Hi,

    I have a requirement to display all the list of countries in an SSRS report as below even if there is no data/values available in the database. As an example below there is no data for Austria and Sweden but they should still show up:

    ANRBRVRTMLEH

    Germany3510274

    Austria

    Czech Rep38

    Netherlands88122

    Belgium5143

    France10243

    Ireland2

    Sweden

    Finland16

    Denmark222

    Norway

    Switzerland1337

    Portugal14

    Spain4

    Baltics1

    Buffer30

    The SQL code that I created doesnt work as it only shows rows for which there is data available for the countries listed above.

    Please find the SQL code attached.

    Could someone please advise any changes to above code so that the requirement can be accomplished ?

    Thanks.

  • I would use the following approach:

    WITH cte_with_values AS

    (

    SELECT xxx

    FROM abc INNER JOIN cde ON abc.col1=cde.col1

    )

    SELECT your_columns

    FROM tbl_with_list_of_countries t

    LEFT OUTER JOIN cte ON t.country=cte.country

    The verbal description:

    Within the cte calculate the values you need for the countries that do have values.

    Then use a table (or a list) to left join the cte against to get the result you're looking for.

    You might need to change the data type for BRV, RTM, and LEH to VARCHAR(x) if you need blank vlaus for the countries without corresponding values. Alternateively, you could display NULL or 0.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • To add to what Lutz posted, you have things like the following in your code...

    WHEN mo.OFFICE_CD IN ('KBE ANR','KBE ZEE', 'KEU ANR') THEN 'BELGIUM'

    It would be MUCH better if you had a 2 column table of values that would hold the Office Codes similar to the following...

    OfficeCode Country

    KBE ANR Belgium

    KBE ZEE Belgium

    KEU ANR Belgium

    That way, you could use simple joins instead of massive case statements. The side benefit would be that if you opened a new office, you could simply add it to the table instead of having to find and change all of the code that is affected.

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

  • This should get you started with Jeff's suggestion:

    SELECT *

    INTO #Offices

    FROM (VALUES

    ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

    ('KDE VIE','AUSTRIA'),

    ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

    ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

    ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

    ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

    ('KDE PRG','CZECH REPUBLIC'),

    ('CHGLS BSL','SWITZERLAND'),

    ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

    ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

    ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

    ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

    ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

    ('KFI HEL','FINLAND'),

    ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

    ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

    ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

    ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

    ) d (OFFICE_CD, COUNTRY_DSC)

    SELECT

    booking_num,

    d.COUNTRY_DSC,

    CASE

    WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

    THEN 'NLRTM'

    WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

    THEN 'DEBRV'

    --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

    --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

    ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

    CASE

    WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

    WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

    ELSE 2 END AS TEU,

    mcc.TYPE_CD,

    mbi.VESSEL_CD,

    mbi.VOYAGE_CD

    FROM #Offices d

    LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    --left JOIN (VALUES

    --('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),

    --('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')

    --) d (COUNTRY_DSC)

    --ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    LEFT JOIN MG_OFFICE mo

    ON mc.COUNTRY_CD = mo.COUNTRY_CD

    LEFT join MG_BOOKING mgd

    ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

    AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

    left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

    ON mgd.BOOKING_ID = ber.BOOKING_ID

    left JOIN MG_BOOKING_ITINERARY mbi

    ON mgd.BOOKING_ID = mbi.BOOKING_ID

    AND mbi.VESSEL_CD IN ('VCRB')

    AND mbi.VOYAGE_CD IN ('5')

    left JOIN MG_CONTAINER_CODE mcc

    ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

    ORDER BY d.COUNTRY_DSC

    “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

  • LutzM (8/7/2014)


    I would use the following approach:

    WITH cte_with_values AS

    (

    SELECT xxx

    FROM abc INNER JOIN cde ON abc.col1=cde.col1

    )

    SELECT your_columns

    FROM tbl_with_list_of_countries t

    LEFT OUTER JOIN cte ON t.country=cte.country

    The verbal description:

    Within the cte calculate the values you need for the countries that do have values.

    Then use a table (or a list) to left join the cte against to get the result you're looking for.

    You might need to change the data type for BRV, RTM, and LEH to VARCHAR(x) if you need blank vlaus for the countries without corresponding values. Alternateively, you could display NULL or 0.

    Hi Lutz, thanks for your suggestion but I am unable to implement it as I am not comfortable with using CTE.

    I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.

    Is there any other way you can suggest ?

    Thanks.

  • ChrisM@Work (8/7/2014)


    This should get you started with Jeff's suggestion:

    SELECT *

    INTO #Offices

    FROM (VALUES

    ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

    ('KDE VIE','AUSTRIA'),

    ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

    ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

    ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

    ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

    ('KDE PRG','CZECH REPUBLIC'),

    ('CHGLS BSL','SWITZERLAND'),

    ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

    ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

    ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

    ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

    ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

    ('KFI HEL','FINLAND'),

    ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

    ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

    ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

    ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

    ) d (OFFICE_CD, COUNTRY_DSC)

    SELECT

    booking_num,

    d.COUNTRY_DSC,

    CASE

    WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

    THEN 'NLRTM'

    WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

    THEN 'DEBRV'

    --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

    --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

    ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

    CASE

    WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

    WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

    ELSE 2 END AS TEU,

    mcc.TYPE_CD,

    mbi.VESSEL_CD,

    mbi.VOYAGE_CD

    FROM #Offices d

    LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    --left JOIN (VALUES

    --('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),

    --('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')

    --) d (COUNTRY_DSC)

    --ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    LEFT JOIN MG_OFFICE mo

    ON mc.COUNTRY_CD = mo.COUNTRY_CD

    LEFT join MG_BOOKING mgd

    ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

    AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

    left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

    ON mgd.BOOKING_ID = ber.BOOKING_ID

    left JOIN MG_BOOKING_ITINERARY mbi

    ON mgd.BOOKING_ID = mbi.BOOKING_ID

    AND mbi.VESSEL_CD IN ('VCRB')

    AND mbi.VOYAGE_CD IN ('5')

    left JOIN MG_CONTAINER_CODE mcc

    ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

    ORDER BY d.COUNTRY_DSC

    Thanks.

    What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.

    My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?

    I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.

    Thanks.

  • Step 1:

    Write the query to produce the result set you're looking for (except the countries without values).

    Step 2: post that query.

    Step 3:

    Tell us the source tabel where we can find the column ANR.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • pwalter83 (8/7/2014)


    ChrisM@Work (8/7/2014)


    This should get you started with Jeff's suggestion:

    SELECT *

    INTO #Offices

    FROM (VALUES

    ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

    ('KDE VIE','AUSTRIA'),

    ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

    ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

    ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

    ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

    ('KDE PRG','CZECH REPUBLIC'),

    ('CHGLS BSL','SWITZERLAND'),

    ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

    ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

    ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

    ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

    ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

    ('KFI HEL','FINLAND'),

    ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

    ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

    ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

    ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

    ) d (OFFICE_CD, COUNTRY_DSC)

    SELECT

    booking_num,

    d.COUNTRY_DSC,

    CASE

    WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

    THEN 'NLRTM'

    WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

    THEN 'DEBRV'

    --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

    --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

    ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

    CASE

    WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

    WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

    ELSE 2 END AS TEU,

    mcc.TYPE_CD,

    mbi.VESSEL_CD,

    mbi.VOYAGE_CD

    FROM #Offices d

    LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    --left JOIN (VALUES

    --('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),

    --('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')

    --) d (COUNTRY_DSC)

    --ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    LEFT JOIN MG_OFFICE mo

    ON mc.COUNTRY_CD = mo.COUNTRY_CD

    LEFT join MG_BOOKING mgd

    ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

    AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

    left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

    ON mgd.BOOKING_ID = ber.BOOKING_ID

    left JOIN MG_BOOKING_ITINERARY mbi

    ON mgd.BOOKING_ID = mbi.BOOKING_ID

    AND mbi.VESSEL_CD IN ('VCRB')

    AND mbi.VOYAGE_CD IN ('5')

    left JOIN MG_CONTAINER_CODE mcc

    ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

    ORDER BY d.COUNTRY_DSC

    Thanks.

    What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.

    My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?

    I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.

    Thanks.

    Did you look at the code, Paul? Check the FROM-list (FROM clause).

    “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

  • ChrisM@Work (8/7/2014)


    pwalter83 (8/7/2014)


    ChrisM@Work (8/7/2014)


    This should get you started with Jeff's suggestion:

    SELECT *

    INTO #Offices

    FROM (VALUES

    ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

    ('KDE VIE','AUSTRIA'),

    ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

    ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

    ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

    ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

    ('KDE PRG','CZECH REPUBLIC'),

    ('CHGLS BSL','SWITZERLAND'),

    ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

    ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

    ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

    ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

    ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

    ('KFI HEL','FINLAND'),

    ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

    ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

    ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

    ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

    ) d (OFFICE_CD, COUNTRY_DSC)

    SELECT

    booking_num,

    d.COUNTRY_DSC,

    CASE

    WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

    THEN 'NLRTM'

    WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

    THEN 'DEBRV'

    --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

    --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

    ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

    CASE

    WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

    WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

    ELSE 2 END AS TEU,

    mcc.TYPE_CD,

    mbi.VESSEL_CD,

    mbi.VOYAGE_CD

    FROM #Offices d

    LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    --left JOIN (VALUES

    --('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),

    --('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')

    --) d (COUNTRY_DSC)

    --ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    LEFT JOIN MG_OFFICE mo

    ON mc.COUNTRY_CD = mo.COUNTRY_CD

    LEFT join MG_BOOKING mgd

    ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

    AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

    left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

    ON mgd.BOOKING_ID = ber.BOOKING_ID

    left JOIN MG_BOOKING_ITINERARY mbi

    ON mgd.BOOKING_ID = mbi.BOOKING_ID

    AND mbi.VESSEL_CD IN ('VCRB')

    AND mbi.VOYAGE_CD IN ('5')

    left JOIN MG_CONTAINER_CODE mcc

    ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

    ORDER BY d.COUNTRY_DSC

    Thanks.

    What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.

    My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?

    I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.

    Thanks.

    Did you look at the code, Paul? Check the FROM-list (FROM clause).

    Thanks Chris. I tried your code but it displays all the data irrespective of the values chosen from the dropdown list. For e.g. if I select 'VCRB' and '076W' as the values it would show me all the values not taking the parameters into consideration.

    The report should show all the countries irrespective of the data exists or not as shown in the attached - Resultset1.

    But what I am currently getting is as shown in Resultset2. Its only showing countries for which data exists.

    Thanks.

  • I don't see any parameters in your code, Paul.

    “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

  • ChrisM@Work (8/7/2014)


    I don't see any parameters in your code, Paul.

    Sorry Chris, the code should be as attached. I am making blunders after blunders. This is what happens when you have little knowledge and this then leads to frustration and thats what I am experiencing at the moment. I am in the wrong profession is all I can say and its too late to change now.

    You have helped immensely before and saved my job many times and I was hoping I could get your help this time too but it seems I am not even able to explain my problem clearly.

    Please let me know of any missing information I can provide.

    Thanks a lot.

  • Like I said before: Step 1 is to get the query you're looking for without the additional countries that aren't included in the result set.

    Please post that query.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (8/7/2014)


    Like I said before: Step 1 is to get the query you're looking for without the additional countries that aren't included in the result set.

    Please post that query.

    Sorry Lutz, I dont know what you mean by 'without the additional countries that aren't included in the result set'. Please find my query I created attached.

    Thanks.

  • ChrisM@Work (8/7/2014)


    I don't see any parameters in your code, Paul.

    Any ideas, Chris ??

  • Basically your problem is that you are using the country derived from MG_OFFICE instead of the country derived from your fixed list. You could try something like

    select booking no, d.country, .....

    from (VALUES

    ('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),

    ('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')

    ) d (COUNTRY_DSC)

    left join MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

    left join MG_OFFICE AS mo ON mo.COUNTRY_DSC = mc.COUNTRY_DSC AND d.COUNTRY_DSC = CASE

    WHEN mo.OFFICE_CD IN ('KBE ANR','KBE ZEE', 'KEU ANR') THEN 'BELGIUM'

    WHEN mo.OFFICE_CD IN ('KDE VIE') THEN 'AUSTRIA'

    WHEN mo.OFFICE_CD IN ('KDE BRE', 'KDE BRV', 'KDE DUS', 'KDE FRA', 'KDE HAM', 'KDE MUC', 'KDE STR') THEN 'GERMANY'

    WHEN mo.OFFICE_CD IN ('KFR BOD','KFR DKK','KFR FOS','KFR LEH','KFR LIO','KFR MRS','KFR PAR','EXAF PARIS','FRBAL BOD','KEU LEH')

    THEN 'FRANCE'

    WHEN mo.OFFICE_CD IN ('KEU RTM', 'KNL RTM') THEN 'NETHERLANDS'

    WHEN mo.OFFICE_CD IN ('KDE PRG') THEN 'CZECH REPUBLIC'

    WHEN mo.OFFICE_CD IN ('CHGLS BSL') THEN 'SWITZERLAND'

    WHEN mo.OFFICE_CD IN ('KPT LIS','KPT OPO') THEN 'PORTUGAL'

    WHEN mo.OFFICE_CD IN ('ESBLR BCN','ESBLR MAD','ESBLR SDR','ESBLR TAR','ESBLR VCL','ESBLR VGO','ESBLR VLC','KES BCN','KES BIO'

    ,'KES MAD','KES VGO','KES VLC') THEN 'SPAIN'

    WHEN mo.OFFICE_CD IN ('IEIRL DUB','IEIRL ORK') THEN 'IRELAND'

    WHEN mo.OFFICE_CD IN ('KSE HES','KSE-JV GOT','KSE-JV HES') THEN 'SWEDEN'

    WHEN mo.OFFICE_CD IN ('KFI HEL') THEN 'FINLAND'

    WHEN mo.OFFICE_CD IN ('KDK AAL','KDK AAR','KDK CPH','KDK EBJ','KDK ODE','KDK-JV AAL','KDK-JV AAR','KDK-JV CPH','KDK-JV EBJ','KDK-JV ODE')

    THEN 'DENMARK'

    WHEN mo.OFFICE_CD IN ('KNO OSL','KNO-JV OSL') THEN 'NORWAY'

    WHEN mo.OFFICE_CD IN ('KDE GDY', 'EESCR TLL', 'KRU LED', 'LTLIM KLJ', 'LVKLS RIX', 'RUMET LED', 'RUMET MOW', 'RUMSA LED')

    THEN 'BALTIC' ELSE 'BUFFER'

    END

    left join MG_BOOKING mgd on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

    ......

    (you need to add the rest of the select list and the remaining joins to this).

    Tom

Viewing 15 posts - 1 through 15 (of 56 total)

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