CODEWITHSUNDEEP
sqldb2
Kayser
Kayser

Reputation: 6694

Lost in SQL Query

I try to generate title in all levels of the officers but I get only the more detailed title. How can i correct the statement?

Currently, I get the more detailed title like 99ABC but i want to get the all combinations.

99---
99--C
99-B-
99A--
99-BC
99A-C
99AB-
99ABC


  select distinct TITLE 
   from (SELECT         
        (case when OFF3.OFFICER_ID IS NOT NULL then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || 
        coalesce( OFF2.INDICATOR, '-') || coalesce( OFF3.INDICATOR, '-') 
        else case when OFF2.OFFICER_ID IS NOT NULL then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || coalesce( OFF2.INDICATOR, '-') || '-' 
        else case when OFF1.OFFICER_ID IS NOT NULL then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || '--' 
        else DEP.INDICATOR || TEA.INDICATOR || '---' end end end ) as TITLE 

        FROM DEPARTMENT DEP, 
        TEAM TEA 
        LEFT OUTER JOIN OFFICER OFF1 ON OFF1.OFFICER1_TEAM_ID = TEA.TEAM_ID 
        LEFT OUTER JOIN OFFICER OFF2 ON OFF2.OFFICER2_TEAM_ID = TEA.TEAM_ID 
        LEFT OUTER JOIN OFFICER OFF3 ON OFF3.OFFICER3_TEAM_ID = TEA.TEAM_ID 

        WHERE TEA.TEAM_DEPA_ID = DEP.DEPARTMENT_ID 
            AND TEA.TEAM_MANAGER_ID IS NOT NULL 
            AND coalesce( TEA.TEAM_MANAGER_ID, OFF1.TEAM_OFFICER_ID, OFF2.TEAM_OFFICER_ID, OFF3.TEAM_OFFICER_ID ) is not null ) myselect

P.S. || is used for concetenate operation.

Upvotes: 0

Views: 102

Answers (1)

user359040
user359040

Reputation:

(EDITED, to remove CTE) Try:

select distinct TITLE from 
(SELECT case title_case
             when 0 then TITLE0
             when 1 then TITLE1
             when 2 then TITLE2
             when 3 then TITLE3
        end as TITLE
 FROM
    (SELECT 0 title_case FROM sysibm.sysdummy1 UNION ALL
     SELECT 1 title_case FROM sysibm.sysdummy1 UNION ALL
     SELECT 2 title_case FROM sysibm.sysdummy1 UNION ALL
     SELECT 3 title_case FROM sysibm.sysdummy1) tc
    CROSS JOIN 
    (SELECT case when OFF3.OFFICER_ID IS NOT NULL 
                      then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || coalesce( OFF2.INDICATOR, '-') || coalesce( OFF3.INDICATOR, '-') 
            end as TITLE3,
            case when OFF2.OFFICER_ID IS NOT NULL 
                      then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || coalesce( OFF2.INDICATOR, '-') || '-' 
            end as TITLE2,
            case when OFF1.OFFICER_ID IS NOT NULL 
                      then DEP.INDICATOR || TEA.INDICATOR || coalesce( OFF1.INDICATOR, '-') || '--' 
            end as TITLE1,
            DEP.INDICATOR || TEA.INDICATOR || '---' as TITLE0
     FROM DEPARTMENT DEP
     JOIN TEAM TEA ON TEA.TEAM_DEPA_ID = DEP.DEPARTMENT_ID 
     LEFT JOIN OFFICER OFF1 ON OFF1.OFFICER1_TEAM_ID = TEA.TEAM_ID 
     LEFT JOIN OFFICER OFF2 ON OFF2.OFFICER2_TEAM_ID = TEA.TEAM_ID 
     LEFT JOIN OFFICER OFF3 ON OFF3.OFFICER3_TEAM_ID = TEA.TEAM_ID 
     WHERE TEA.TEAM_MANAGER_ID IS NOT NULL 
    ) mytitles
) myselect

Upvotes: 1

Related Questions