Best way to SELECT with one Case Expression

  • The CategoryId is in the table FIELDS_ACTIVATIONS where the FieldName is the column name of the data in the three tables...so common ground between the data and the categoryId is the FieldName in FIELDS_ACTIVATIONS

    I know..I know...

  • If I'm reading that right, it's applying the Category 1 rules to everything in the table EXTR_MILIMETRA, is that correct? So the categorisation doesn't happen on a row-by-row basis, but rather is something applicable to the entire table? Do other categories apply to other tables?

    I get the relationship between the Field Activations table and the column names, I'm just struggling to understand how categorisation is supposed to affect the overall result set.

  • Category 1 applies to EXTR_MILIMETRA columns. Category 2 may have 2 tables, same for three. Forget about four they haven't told me what to do with 4.
    "I'm just struggling to understand how categorisation is supposed to affect the overall result set."

    Me too. When I ask my senior she simply says "ColumnX is in category Y as per the FIELDS_ACTIVATIONS Table" so she expects me to display the overall result of each column, empty or with a value based on if the category is Active or not in the fields activations table.

    Personally I don't get it, I was hoping this was due to my inexperience.

  • OK, so it's categorised based on the source of data then. In which case you're basically already there. Each of your queries is going to end up with 30 columns (even if they're "empty" where they don't apply for that category) so you can literally just union all the results into one result set, i.e.

    With CategoryOne As
    (
    SELECT
    --Category 1
     CASE WHEN EXISTS (SELECT * FROM FIELD_ACTIVATIONS WHERE FieldName =  COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),1) And Category = 1)
     THEN REPLICATE('0', 10-LEN(PolicyNumber)) + PolicyNumber
     ELSE '' END AS PolicyNumber,
     CASE WHEN EXISTS (SELECT * FROM FIELD_ACTIVATIONS WHERE FieldName =  COL_NAME(object_id('Company.FileExtraction.EXTR_MILIMETRA'),2) And Category = 1)
     THEN REPLICATE('0', 7-LEN(BOCBranch)) + BOCBranch
     ELSE '' END AS BOCBranch,
    …
     From EXTR_MILIMETRA
     Group BY ....
    ),
    CategoryTwo As
    (
     CASE WHEN EXISTS (SELECT * FROM FIELD_ACTIVATIONS WHERE FieldName =  COL_NAME(object_id('Company.FileExtraction.TABLE2'),1) And Category = 2)
     THEN REPLICATE('0', 10-LEN(PolicyNumber)) + PolicyNumber
     ELSE '' END AS PolicyNumber,
     CASE WHEN EXISTS (SELECT * FROM FIELD_ACTIVATIONS WHERE FieldName =  COL_NAME(object_id('Company.FileExtraction.TABLE2'),2) And Category = 2)
     THEN REPLICATE('0', 7-LEN(BOCBranch)) + BOCBranch
     ELSE '' END AS BOCBranch,
    …
     From TABLE2
     Join TABLE3 On ....
     Group By ....
    )
    Select
     PolicyNumber,
     BOCBranch,
     ...
    From CategoryOne
    Union All
    Select
     PolicyNumber,
     BOCBranch,
     ...

  • Category 1 has 14, Category 2 has 56 and Category 3 has 15.

    Not sure about Category 4 yet.

    Will this still work?

  • You've lost me. In that field activation table you posted, there are the same columns per category but with different activation states - which would work, but then you talk about there being different numbers of columns in each category and it's not at all clear how they should be displayed.

  • Sorry mate.

    I've uploaded a txt file have a look at that. The previous one was a photo and it didn't show all the columns. Sorry.
    Ignore Category 4 though.

  • In the cases where a column doesn't apply to a category at all, just return a dummy value containing the empty string - that way all four queries will have the same columns and the union will be fine.

  • Andy leave it for now mate I'll talk to management because it's too messy I think. Maintaining this would be a nightmare too. I'll get back with clear info.

    Thanks for your time.

  • Here's the FieldActivations table create so that folks can more easily create something testable....
    CREATE TABLE #FIELD_ACTIVATIONS (
        FieldName tinyint NOT NULL,
        CategoryID tinyint NOT NULL,
        IsActive bit NOT NULL,
        UNIQUE CLUSTERED
            (
            FieldName ASC,
            CategoryID ASC
            )
    );
    INSERT INTO #FIELD_ACTIVATIONS (FieldName, CategoryID, IsActive)
        VALUES    ('PolicyNumber',                        1, 1),
                ('PolicyNumber',                        2, 1),
                ('PolicyNumber',                        3, 1),
                ('PolicyNumber',                        4, 1),
                ('BOCBranch',                            1, 1),
                ('BOCBranch',                            2, 0),
                ('BOCBranch',                            3, 0),
                ('BOCBranch',                            4, 0),
                ('CIFNumber',                            1, 1),
                ('CIFNumber',                            2, 0),
                ('CIFNumber',                            3, 0),
                ('CIFNumber',                            4, 0),
                ('EmployeeNumber',                        1, 1),
                ('EmployeeNumber',                        2, 0),
                ('EmployeeNumber',                        3, 0),
                ('EmployeeNumber',                        4, 0),
                ('PremiumSign',                            1, 1),
                ('PremiumSign',                            2, 0),
                ('PremiumSign',                            3, 0),
                ('PremiumSign',                            4, 0),
                ('Premium',                                1, 1),
                ('Premium',                                2, 0),
                ('Premium',                                3, 0),
                ('Premium',                                4, 0),
                ('RegistrationDate',                    1, 1),
                ('RegistrationDate',                    2, 0),
                ('RegistrationDate',                    3, 0),
                ('RegistrationDate',                    4, 0),
                ('ActivityCode',                        1, 1),
                ('ActivityCode',                        2, 0),
                ('ActivityCode',                        3, 0),
                ('ActivityCode',                        4, 0),
                ('ActivityDescription',                    1, 1),
                ('ActivityDescription',                    2, 0),
                ('ActivityDescription',                    3, 0),
                ('ActivityDescription',                    4, 0),
                ('PolicyTypeCode',                        1, 1),
                ('PolicyTypeCode',                        2, 0),
                ('PolicyTypeCode',                        3, 0),
                ('PolicyTypeCode',                        4, 0),
                ('PolicyTypeDescription',                1, 1),
                ('PolicyTypeDescription',                2, 0),
                ('PolicyTypeDescription',                3, 0),
                ('PolicyTypeDescription',                4, 0),
                ('ContributionCode',                    1, 1),
                ('ContributionCode',                    2, 0),
                ('ContributionCode',                    3, 0),
                ('ContributionCode',                    4, 0),
                ('ContributionDescription',                1, 1),
                ('ContributionDescription',                2, 0),
                ('ContributionDescription',                3, 0),
                ('ContributionDescription',                4, 0),
                ('ActivityMilimetra',                    1, 1),
                ('ActivityMilimetra',                    2, 0),
                ('ActivityMilimetra',                    3, 0),
                ('ActivityMilimetra',                    4, 0),
                ('SourceCode',                            1, 0),
                ('SourceCode',                            2, 0),
                ('SourceCode',                            3, 1),
                ('SourceCode',                            4, 0),
                ('EntityItemValue',                        2, 1),
                ('EntityItemValue',                        1, 0),
                ('EntityItemValue',                        3, 0),
                ('EntityItemValue',                        4, 0),
                ('AssigneeBank',                        2, 1),
                ('AssigneeBank',                        1, 0),
                ('AssigneeBank',                        3, 0),
                ('AssigneeBank',                        4, 0),
                ('InsuranceCompany',                    2, 1),
                ('InsuranceCompany',                    1, 0),
                ('InsuranceCompany',                    3, 0),
                ('InsuranceCompany',                    4, 0),
                ('InsuranceCompanyName',                2, 1),
                ('InsuranceCompanyName',                1, 0),
                ('InsuranceCompanyName',                3, 0),
                ('InsuranceCompanyName',                4, 0),
                ('CommencementDate',                    2, 1),
                ('CommencementDate',                    1, 0),
                ('CommencementDate',                    3, 0),
                ('CommencementDate',                    4, 0),
                ('ProductNumber',                        2, 1),
                ('ProductNumber',                        1, 0),
                ('ProductNumber',                        3, 0),
                ('ProductNumber',                        4, 0),
                ('ProductName',                            2, 1),
                ('ProductName',                            1, 0),
                ('ProductName',                            3, 0),
                ('ProductName',                            4, 0),
                ('ProductVersion',                        2, 1),
                ('ProductVersion',                        1, 0),
                ('ProductVersion',                        3, 0),
                ('ProductVersion',                        4, 0),
                ('AgentNumber',                            2, 1),
                ('AgentNumber',                            1, 0),
                ('AgentNumber',                            3, 0),
                ('AgentNumber',                            4, 0),
                ('MainAgentExternalReference',            2, 1),
                ('MainAgentExternalReference',            1, 0),
                ('MainAgentExternalReference',            3, 0),
                ('MainAgentExternalReference',            4, 0),
                ('ParticipatingAgentNumber',            2, 1),
                ('ParticipatingAgentNumber',            1, 0),
                ('ParticipatingAgentNumber',            3, 0),
                ('ParticipatingAgentNumber',            4, 0),
                ('ParticipatingAgentExternalReference',    2, 1),
                ('ParticipatingAgentExternalReference', 1, 0),
                ('ParticipatingAgentExternalReference', 3, 0),
                ('ParticipatingAgentExternalReference', 4, 0),
                ('PolicyDuration',                        2, 1),
                ('PolicyDuration',                        1, 0),
                ('PolicyDuration',                        3, 0),
                ('PolicyDuration',                        4, 0),
                ('PolicyStatus',                        2, 1),
                ('PolicyStatus',                        1, 0),
                ('PolicyStatus',                        3, 0),
                ('PolicyStatus',                        4, 0),
                ('PolicyStatusName',                    2, 1),
                ('PolicyStatusName',                    1, 0),
                ('PolicyStatusName',                    3, 0),
                ('PolicyStatusName',                    4, 0),
                ('PolicyStatusOpeningReference',        2, 1),
                ('PolicyStatusOpeningReference',        1, 0),
                ('PolicyStatusOpeningReference',        3, 0),
                ('PolicyStatusOpeningReference',        4, 0),
                ('PolicyStatusRegistrationDate',        2, 1),
                ('PolicyStatusRegistrationDate',        1, 0),
                ('PolicyStatusRegistrationDate',        3, 0),
                ('PolicyStatusRegistrationDate',        4, 0),
                ('PolicyPreviousStatus',                2, 1),
                ('PolicyPreviousStatus',                1, 0),
                ('PolicyPreviousStatus',                3, 0),
                ('PolicyPreviousStatus',                4, 0),
                ('PolicyPreviousStatusName',            2, 1),
                ('PolicyPreviousStatusName',            1, 0),
                ('PolicyPreviousStatusName',            3, 0),
                ('PolicyPreviousStatusName',            4, 0),
                ('MainInsuredNumber',                    2, 1),
                ('MainInsuredNumber',                    1, 0),
                ('MainInsuredNumber',                    3, 0),
                ('MainInsuredNumber',                    4, 0),
                ('MainInsuredExternalReference',        2, 1),
                ('MainInsuredExternalReference',        1, 0),
                ('MainInsuredExternalReference',        3, 0),
                ('MainInsuredExternalReference',        4, 0),
                ('OwnerNumber',                            2, 1),
                ('OwnerNumber',                            1, 0),
                ('OwnerNumber',                            3, 0),
                ('OwnerNumber',                            4, 0),
                ('OwnerExternalReference',                2, 1),
                ('OwnerExternalReference',                1, 0),
                ('OwnerExternalReference',                3, 0),
                ('OwnerExternalReference',                4, 0),
                ('PaymentArrangementId',                2, 1),
                ('PaymentArrangementId',                1, 0),
                ('PaymentArrangementId',                3, 0),
                ('PaymentArrangementId',                4, 0),
                ('PaymentFrequency',                    2, 1),
                ('PaymentFrequency',                    1, 0),
                ('PaymentFrequency',                    3, 0),
                ('PaymentFrequency',                    4, 0),
                ('PaymentFrequencyName',                2, 1),
                ('PaymentFrequencyName',                1, 0),
                ('PaymentFrequencyName',                3, 0),
                ('PaymentFrequencyName',                4, 0),
                ('PaymentMethod',                        2, 1),
                ('PaymentMethod',                        1, 0),
                ('PaymentMethod',                        3, 0),
                ('PaymentMethod',                        4, 0),
                ('PaymentMethodName',                    2, 1),
                ('PaymentMethodName',                    1, 0),
                ('PaymentMethodName',                    3, 0),
                ('PaymentMethodName',                    4, 0),
                ('AnnualPremium',                        2, 1),
                ('AnnualPremium',                        1, 0),
                ('AnnualPremium',                        3, 0),
                ('AnnualPremium',                        4, 0),
                ('RoleNumber',                            2, 1),
                ('RoleNumber',                            1, 0),
                ('RoleNumber',                            3, 0),
                ('RoleNumber',                            4, 0),
                ('ClientNumber',                        2, 1),
                ('ClientNumber',                        1, 0),
                ('ClientNumber',                        3, 0),
                ('ClientNumber',                        4, 0),
                ('Occupation',                            2, 1),
                ('Occupation',                            1, 0),
                ('Occupation',                            3, 0),
                ('Occupation',                            4, 0),
                ('SmokerFlag',                            2, 1),
                ('SmokerFlag',                            1, 0),
                ('SmokerFlag',                            3, 1),
                ('SmokerFlag',                            4, 0),
                ('RelationshipToInsured',                2, 1),
                ('RelationshipToInsured',                1, 0),
                ('RelationshipToInsured',                3, 0),
                ('RelationshipToInsured',                4, 0),
                ('AddressTypeOverride',                    2, 1),
                ('AddressTypeOverride',                    1, 0),
                ('AddressTypeOverride',                    3, 0),
                ('AddressTypeOverride',                    4, 0),
                ('OpeningRegistrationDate',                2, 1),
                ('OpeningRegistrationDate',                1, 0),
                ('OpeningRegistrationDate',                3, 0),
                ('OpeningRegistrationDate',                4, 0),
                ('ClosingRegistrationDate',                2, 1),
                ('ClosingRegistrationDate',                1, 0),
                ('ClosingRegistrationDate',                3, 0),
                ('ClosingRegistrationDate',                4, 0),
                ('OpeningEffectiveDate',                2, 1),
                ('OpeningEffectiveDate',                1, 0),
                ('OpeningEffectiveDate',                3, 0),
                ('OpeningEffectiveDate',                4, 0),
                ('ClosingEffectiveDate',                2, 1),
                ('ClosingEffectiveDate',                1, 0),
                ('ClosingEffectiveDate',                3, 0),
                ('ClosingEffectiveDate',                4, 0),
                ('OpeningStatus',                        2, 1),
                ('OpeningStatus',                        1, 0),
                ('OpeningStatus',                        3, 0),
                ('OpeningStatus',                        4, 0),
                ('ClosingStatus',                        2, 1),
                ('ClosingStatus',                        1, 0),
                ('ClosingStatus',                        3, 0),
                ('ClosingStatus',                        4, 0),
                ('OpeningReference',                    2, 1),
                ('OpeningReference',                    1, 0),
                ('OpeningReference',                    3, 0),
                ('OpeningReference',                    4, 0),
                ('ClosingReference',                    2, 1),
                ('ClosingReference',                    1, 0),
                ('ClosingReference',                    3, 0),
                ('ClosingReference',                    4, 0),
                ('UserId',                                2, 1),
                ('UserId',                                1, 0),
                ('UserId',                                3, 0),
                ('UserId',                                4, 0),
                ('PClientRolePk',                        2, 1),
                ('PClientRolePk',                        1, 0),
                ('PClientRolePk',                        3, 0),
                ('PClientRolePk',                        4, 0),
                ('LoadingTimestamp',                    2, 1),
                ('LoadingTimestamp',                    1, 0),
                ('LoadingTimestamp',                    3, 0),
                ('LoadingTimestamp',                    4, 0),
                ('EntityItemType',                        2, 1),
                ('EntityItemType',                        1, 0),
                ('EntityItemType',                        3, 0),
                ('EntityItemType',                        4, 0),
                ('EntityItemSubType',                    2, 1),
                ('EntityItemSubType',                    1, 0),
                ('EntityItemSubType',                    3, 0),
                ('EntityItemSubType',                    4, 0),
                ('EntityItemSecondSubType',                2, 1),
                ('EntityItemSecondSubType',                1, 0),
                ('EntityItemSecondSubType',                3, 0),
                ('EntityItemSecondSubType',                4, 0),
                ('EntityItemId',                        2, 1),
                ('EntityItemId',                        1, 0),
                ('EntityItemId',                        3, 0),
                ('EntityItemId',                        4, 0),
                ('EntityNumber',                        2, 1),
                ('EntityNumber',                        1, 0),
                ('EntityNumber',                        3, 0),
                ('EntityNumber',                        4, 0),
                ('EntitySubNumber',                        2, 1),
                ('EntitySubNumber',                        1, 0),
                ('EntitySubNumber',                        3, 0),
                ('EntitySubNumber',                        4, 0),
                ('EntityItemValue',                        2, 0),
                ('EntityItemRowNumber',                    2, 1),
                ('EntityItemRowNumber',                    1, 0),
                ('EntityItemRowNumber',                    3, 0),
                ('EntityItemRowNumber',                    4, 0),
                ('GEntityValuesPk',                        2, 1),
                ('GEntityValuesPk',                        1, 0),
                ('GEntityValuesPk',                        3, 0),
                ('GEntityValuesPk',                        4, 0),
                ('sourceCodeId',                        3, 1),
                ('sourceCodeId',                        2, 0),
                ('sourceCodeId',                        1, 0),
                ('sourceCodeId',                        4, 0),
                ('sourceCodeDesc',                        3, 1),
                ('sourceCodeDesc',                        2, 0),
                ('sourceCodeDesc',                        1, 0),
                ('sourceCodeDesc',                        4, 0),
                ('agentCaseWeight',                        3, 1),
                ('agentCaseWeight',                        2, 0),
                ('agentCaseWeight',                        1, 0),
                ('agentCaseWeight',                        4, 0),
                ('agentPremiumWeight',                    3, 1),
                ('agentPremiumWeight',                    2, 0),
                ('agentPremiumWeight',                    1, 0),
                ('agentPremiumWeight',                    4, 0),
                ('agentBusinessTypeSourceCd',            3, 1),
                ('agentBusinessTypeSourceCd',            2, 0),
                ('agentBusinessTypeSourceCd',            1, 0),
                ('agentBusinessTypeSourceCd',            4, 0),
                ('bankCaseWeight',                        3, 1),
                ('bankCaseWeight',                        2, 0),
                ('bankCaseWeight',                        1, 0),
                ('bankCaseWeight',                        4, 0),
                ('bankPremiumWeight',                    3, 1),
                ('bankPremiumWeight',                    2, 0),
                ('bankPremiumWeight',                    1, 0),
                ('bankPremiumWeight',                    4, 0),
                ('bankBusinessTypeSourceCd',            3, 1),
                ('bankBusinessTypeSourceCd',            2, 0),
                ('bankBusinessTypeSourceCd',            1, 0),
                ('bankBusinessTypeSourceCd',            4, 0),
                ('isActiveFlag',                        3, 1),
                ('isActiveFlag',                        2, 0),
                ('isActiveFlag',                        1, 0),
                ('isActiveFlag',                        4, 0),
                ('configurationStatusCd',                3, 1),
                ('configurationStatusCd',                2, 0),
                ('configurationStatusCd',                1, 0),
                ('configurationStatusCd',                4, 0),
                ('lastModifiedBy',                        3, 1),
                ('lastModifiedBy',                        2, 0),
                ('lastModifiedBy',                        1, 0),
                ('lastModifiedOn',                        4, 0);

    DROP TABLE #FIELD_ACTIVATIONS;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Guys thanks a lot for your help. I’ve spoken to management about this and it turns out the design is total crap and managers will be seeing to it tomorrow.

    Thanks again for all your help and sorry for any time I may have wasted.

  • No worries. It's not impossible to make a design along those lines work, but it sounds like it hadn't been entirely thought through.

  • Ended up changing the whole thing.

    Just thought I'd let you know, thanks again for your time Andy.

Viewing 13 posts - 16 through 27 (of 27 total)

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