return 1 row from multiple case statements

Question

In the following Query below in the port with the case statements I'm trying to figure out how to not return a null value when the SegmentType doesn't match.

QRSubscriberTag,MLKSubscriberTag, QTMSubscriber Tag, are actually a table of Tags uniquely identified by their SegID and a segmentType associated to the SegID. I use an outer apply with a subquery that calls FOR XML PATH('') to concatenate the Tags together related to the specific tag.

The flaw with this method is that when the Tag isn't for example QRSubscriberTag, then a null record in the QRSubscriberTag column is created.

QRSubscriberTag|QR01|MLKSubscriberTag|MLK01|QTMSubscriberTag|QTM01|QTM02
NULL           |NULL|NULL            |{Data}|null           |FH   |45
TKP ;OCP ;OCR ;|R   |NULL            |null |null            |null |null

What I would like to see is all the Data in 1 line

QRSubscriberTag|QR01|MLKSubscriberTag|MLK01|QTMSubscriberTag|QTM01|QTM02
TKP ;OCP ;OCR ;|R   |NULL            |{Data}|null            |FH  |45

Below is my code

SET NOCOUNT ON

USE [Apps]

SELECT [BusRuleEnforceID], [FormatType], [Format],
        [tag], [TagValue], Operator,
       [QRSubscriberTag],[QR01],
       [MLKSubscriberTag],[MLK01], 
       [QTMSubscriberTag],[QTM01],[QTM02]
FROM
(
    SELECT 
        DISTINCT main.BusRuleEnforceID, 
        main.FormatType, 
        main.Format, 
        main.tag,
        main.TagValue,
        main.Operator,
        CASE
            WHEN seg.SegmentType = 'QR' THEN LEFT(SegsubscriberTags.list, LEN(SegsubscriberTags.list))
        END AS [QRSubscriberTag],
        CASE
            WHEN seg.SegmentType = 'MLK' THEN LEFT(SegsubscriberTags.list, LEN(SegsubscriberTags.list)-1)
        END AS [MLKSubscriberTag],
        CASE
            WHEN seg.SegmentType = 'QTM' THEN LEFT(SegsubscriberTags.list, LEN(SegsubscriberTags.list)-1)
        END AS [QTMSubscriberTag],
        c.ItemName as [SegmentId],
        Case
            WHEN c.ItemValue is not null and len(rtrim(c.ItemValue)) > 0 THEN c.ItemValue
            ELSE LEFT(TagOptions.list, LEN(TagOptions.list)-1)
        END as [Tags]
    FROM
        BMT_BusRulemaineria main 
    LEFT OUTER JOIN
        BMT_BusRuleServiceType st
    ON st.MappingmaineriaID = main.BusRuleEnforceID
    LEFT OUTER JOIN
        BMT_BusRuleSegment seg
    ON seg.BusRuleEnforceID = main.BusRuleEnforceID
    LEFT OUTER JOIN
        BMT_BusRuleItem c
    ON c.BusRuleEnforceSegID = seg.BusRuleEnforceSegID
    LEFT OUTER JOIN
        BMT_BusRuleItemOption co
    ON c.BusRuleItemId = co.BusRuleItemId
    OUTER APPLY 
    ( 
            SELECT 
            IsNull(co2.Tag, '{Unknown}') + ISNULL('=' + co2.TagValue, '') + ':' + IsNull(co2.ItemValue, '')+ ';' AS [text()] 
            FROM 
                BMT_BusRuleItemOption co2 
            WHERE 
                co.BusRuleItemId = co2.BusRuleItemId
            ORDER BY 
                co2.BusRuleItemId 
            FOR XML PATH('') 
        ) TagOptions (list) 
    OUTER APPLY 
    (
            SELECT 
            IsNull(porttag.Tag, '{Unknown}') + ' ' + ISNULL(porttag.Operator, '') + ' ' + IsNull(porttag.TagValue, '') + ';' AS [text()]
            FROM 
                BMT_BusRuleSegmentTag porttag 
            WHERE 
                seg.BusRuleEnforceSegID = porttag.BusRuleEnforceSegID
            ORDER BY 
                porttag.BusRuleEnforceSegID 
            FOR XML PATH('')
    ) SegsubscriberTags (list) 
        WHERE main.BusRuleEnforceID = 632563
    AND main.TagTypeId = 1
) AS QRSourceTable
PIVOT
(
    max([Tags])
    FOR [SegmentId] IN ([QR01], [MLK01], [QTM01], [QTM02])
) AS QRPivotTable
ORDER BY 
    [TagTypeId], [BusRuleEnforceID]

EDIT: Full Result Set Note: QRSubscriber, MLKSubscriber, and QTMSubscriber tags all have unique SegIDs.

<table><tbody><tr><th>FormatType</th><th>Format</th><th>Version</th><th>AmtCode</th><th>Date</th><th>LocationIdentifier</th><th>TagType</th><th>tag</th><th>TagValue</th><th>Operator</th><th>InfoCode</th><th>QRSubscriberTag</th><th>QR01</th><th>QR02</th><th>QR03</th><th>QR04</th><th>QR05</th><th>QR06</th><th>QR07</th><th>MLKSubscriberTag</th><th>MLK01</th><th>QTMSubscriberTag</th><th>QTM01</th><th>QTM02</th></tr><tr><td>DN</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>TIR</td><td>RPSAMN</td><td>NULL</td><td>exists</td><td>78</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>{Data}</td><td>NULL</td><td>TRH</td><td>675</td></tr><tr><td>DN</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>TIR</td><td>RPSAMN</td><td>NULL</td><td>exists</td><td>78</td><td>TKRARAP not exists TKRALAP not exists TKRADAP not exists </td><td>J</td><td>SKU</td><td>34</td><td>NULL</td><td>NULL</td><td>SILMULTU:2;SILMHLTU:67</td><td>TOPALD:{Data};QORITK{Data}</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td></tr></tbody></table>

um don't use NULL in your case statement? you can use a blank string like '' or any other sentinel value you choose. — Jeremy, Jun 15 '16 at 17:55
Forgot to remove Them. The else nulls do not do anythign in this case. if I did for example: `CASE WHEN port.SegmentType = 'QR' THEN LEFT(SegDependantTags.list, LEN(SegDependantTags.list)) END AS [QRSubscriberTag]` the results would be the same. The Case statement is creating nulls in cases where the Segment is not QR. — GnisPL, Jun 15 '16 at 17:58
Make sure you post the code you are actually using. This would not pass a syntax check. You are referencing a table or alias called 'port' that does not exist anywhere in your from clause. — Brian Pressler, Jun 15 '16 at 22:22
Now the output that you added doesn't match the same fields that your code would generate. — Brian Pressler, Jun 16 '16 at 16:51

score 1 · Answer 1 · answered Jun 15 '16 at 18:02

1

Use the ELSE clause to assign a default value when the comparison does not match, like so:

CASE WHEN port.SegmentType = 'QR' THEN 
  LEFT(SegDependantTags.list, LEN(SegDependantTags.list))
ELSE 'Your Value Rather Than NULL Goes Here'
END AS [QRSubscriberTag],

answered Jun 15 '16 at 18:02

Tahbaza

9,486
2
26
39

This method would create multiple columns. The idea is compact it into one column. – GnisPL Jun 15 '16 at 18:26
hmmm, I don't see that anywhere in your question. – Tahbaza Jun 15 '16 at 18:28
where I said What I would like to see is all the Data in 1 line – GnisPL Jun 15 '16 at 19:18

score 0 · Answer 2 · answered Jun 15 '16 at 22:43

It seems that instead of using Distinct in your QRSourceTable sub-query you want to use a Group By with a MAX aggregate function on your subscriber tag fields:

SELECT 
        main.BusRuleEnforceID, 
        main.FormatType, 
        main.Format, 
        main.tag,
        main.TagValue,
        main.Operator,
        max(CASE
            WHEN port.SegmentType = 'QR' THEN LEFT(SegDependantTags.list, LEN(SegDependantTags.list))
        END) AS [QRSubscriberTag],
        max(CASE
            WHEN port.SegmentType = 'MLK' THEN LEFT(SegDependantTags.list, LEN(SegDependantTags.list)-1)
        END) AS [MLKSubscriberTag],
        max(CASE
            WHEN port.SegmentType = 'QTM' THEN LEFT(SegDependantTags.list, LEN(SegDependantTags.list)-1)
        END) AS [QTMSubscriberTag],
        c.ItemName as [SegmentId],
        Case
            WHEN c.ItemValue is not null and len(rtrim(c.ItemValue)) > 0 THEN c.ItemValue
            ELSE LEFT(TagOptions.list, LEN(TagOptions.list)-1)
        END as [Tags]
from
     ...
WHERE main.BusRuleEnforceID = 632563
    AND main.TagTypeId = 1
GROUP BY main.BusRuleEnforceID, 
        main.FormatType, 
        main.Format, 
        main.tag,
        main.TagValue,
        main.Operator

With this method I see same results. I recently obtained the results I wanted recently by using sub queries for each SegmentType instead of using the SegDependantTags.List, however due to the overuse of subqueries the execution time took 30 minutes instead of 15 seconds to find 38k records. — GnisPL, Jun 16 '16 at 14:44
If you are still getting an additional record with this method, that means that one of the fields in your select from the 'main' table alias must be different than the other record too. You didn't show all the fields in your result table in the question, so I was making guess. What are the fields in the output of your query that must be unique? Start with that question and work from there. — Brian Pressler, Jun 16 '16 at 14:54

return 1 row from multiple case statements

2 Answers2