Skip to Content

Business Partner Property Marked Query

Hi Experts.

The following query shows the business partner with the property that is marked in the properties tab of the business partner master data.

SELECT T0.[CardCode], T0.[CardName],


(case
when T0.[QryGroup1] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 1)
when T0.[QryGroup2] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 2)
when T0.[QryGroup3] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 3)
when T0.[QryGroup4] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 4)
when T0.[QryGroup5] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 5)
when T0.[QryGroup6] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 6)
when T0.[QryGroup7] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 7)
when T0.[QryGroup8] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 8)
when T0.[QryGroup9] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 9)
when T0.[QryGroup10] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 10)
when T0.[QryGroup11] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 11)
when T0.[QryGroup12] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 12)
when T0.[QryGroup13] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 13)
when T0.[QryGroup14] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 14)
when T0.[QryGroup15] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 15)
when T0.[QryGroup16] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 16)
when T0.[QryGroup17] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 17)
when T0.[QryGroup18] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 18)
when T0.[QryGroup19] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 19)
when T0.[QryGroup20] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 20)
when T0.[QryGroup21] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 21)
when T0.[QryGroup22] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 22)
when T0.[QryGroup23] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 23)
when T0.[QryGroup24] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 24)
when T0.[QryGroup25] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 25)
when T0.[QryGroup26] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 26)
when T0.[QryGroup27] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 27)
when T0.[QryGroup28] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 28)
when T0.[QryGroup29] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 29)
when T0.[QryGroup30] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 30)
when T0.[QryGroup31] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 31)
when T0.[QryGroup32] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 32)
when T0.[QryGroup33] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 33)
when T0.[QryGroup34] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 34)
when T0.[QryGroup35] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 35)
when T0.[QryGroup36] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 36)
when T0.[QryGroup37] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 37)
when T0.[QryGroup38] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 38)
when T0.[QryGroup39] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 39)
when T0.[QryGroup40] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 40)
when T0.[QryGroup41] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 41)
when T0.[QryGroup42] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 42)
when T0.[QryGroup43] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 43)
when T0.[QryGroup44] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 44)
when T0.[QryGroup45] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 45)
when T0.[QryGroup46] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 46)
when T0.[QryGroup47] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 47)
when T0.[QryGroup48] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 48)
when T0.[QryGroup49] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 49)
when T0.[QryGroup50] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 50)
when T0.[QryGroup51] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 51)
when T0.[QryGroup52] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 52)
when T0.[QryGroup53] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 53)
when T0.[QryGroup54] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 54)
when T0.[QryGroup55] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 55)
when T0.[QryGroup56] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 56)
when T0.[QryGroup57] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 57)
when T0.[QryGroup58] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 58)
when T0.[QryGroup59] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 59)
when T0.[QryGroup60] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 60)
when T0.[QryGroup61] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 61)
when T0.[QryGroup62] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 62)
when T0.[QryGroup63] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 63)
when T0.[QryGroup64] = 'y' then (SELECT T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 64)
end) as 'Property'


FROM OCRD T0

But it only brings one value.

How do you bring all the values that are marked?

Thank you so much.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 05, 2017 at 06:03 AM

    Hi Ivonne,

    With the following version of the query, you can have single BP, you can have all BPs (at the prompt enter a percentage character %), or even a certain range, for example by entering 100% or A1%, or %1% (assuming you would have BP numbers for example like 10001, 10002, etc and/or A0001, B0001, A0002, etc).

    Please note that the number of rows returned = BPs x checked properties, and thus the query can conceivably return thousands of rows:

    /*select * from OCRD c */
    DECLARE @CardCode AS NVARCHAR(15)
    SET @CardCode = /* c.CardCode */ '[%0]'
    SELECT *
    FROM
    (select CardCode, CardName, case when T0.[QryGroup1] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 1) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup2] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 2) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup3] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 3) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup4] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 4) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup5] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 5) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup6] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 6) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup7] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 7) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup8] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 8) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup9] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 9) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup10] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 10) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup11] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 11) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup12] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 12) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup13] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 13) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup14] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 14) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup15] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 15) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup16] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 16) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup17] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 17) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup18] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 18) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup19] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 19) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup20] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 20) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup21] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 21) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup22] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 22) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup23] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 23) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup24] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 24) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup25] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 25) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup26] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 26) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup27] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 27) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup28] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 28) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup29] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 29) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup30] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 30) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup31] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 31) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup32] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 32) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup33] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 33) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup34] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 34) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup35] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 35) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup36] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 36) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup37] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 37) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup38] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 38) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup39] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 39) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup40] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 40) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup41] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 41) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup42] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 42) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup43] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 43) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup44] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 44) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup45] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 45) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup46] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 46) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup47] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 47) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup48] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 48) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup49] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 49) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup50] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 50) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup51] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 51) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup52] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 52) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup53] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 53) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup54] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 54) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup55] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 55) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup56] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 56) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup57] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 57) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup58] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 58) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup59] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 59) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup60] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 60) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup61] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 61) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup62] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 62) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup63] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 63) else null end as prop from OCRD T0 where T0.CardCode like @CardCode
    union all select CardCode, CardName, case when T0.[QryGroup64] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 64) else null end as prop from OCRD T0 where T0.CardCode like @CardCode) X
    WHERE NOT X.prop IS NULL
    ORDER BY X.CardName, X.prop

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • May 04, 2017 at 07:17 AM

    Hi Ivonne,

    The CASE statement unfortunately always only returns the first option that is true. That is why you only ever get the first property that has been set for a customer.

    You can get all properties for a single BP number:

    /*select * from OCRD c */
    DECLARE @CardCode AS NVARCHAR(15)
    SET @CardCode = /* c.CardCode */ '[%0]'
    SELECT *
    FROM
    (select case when T0.[QryGroup1] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 1) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup2] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 2) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup3] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 3) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup4] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 4) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup5] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 5) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup6] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 6) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup7] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 7) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup8] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 8) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup9] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 9) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup10] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 10) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup11] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 11) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup12] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 12) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup13] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 13) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup14] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 14) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup15] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 15) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup16] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 16) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup17] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 17) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup18] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 18) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup19] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 19) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup20] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 20) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup21] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 21) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup22] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 22) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup23] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 23) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup24] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 24) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup25] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 25) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup26] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 26) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup27] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 27) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup28] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 28) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup29] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 29) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup30] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 30) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup31] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 31) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup32] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 32) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup33] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 33) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup34] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 34) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup35] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 35) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup36] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 36) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup37] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 37) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup38] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 38) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup39] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 39) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup40] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 40) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup41] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 41) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup42] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 42) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup43] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 43) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup44] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 44) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup45] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 45) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup46] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 46) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup47] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 47) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup48] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 48) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup49] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 49) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup50] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 50) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup51] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 51) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup52] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 52) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup53] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 53) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup54] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 54) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup55] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 55) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup56] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 56) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup57] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 57) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup58] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 58) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup59] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 59) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup60] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 60) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup61] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 61) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup62] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 62) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup63] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 63) else null end as prop from OCRD T0 where T0.CardCode = @CardCode
    union all select case when T0.[QryGroup64] = 'y' then (select T0.[GroupName] FROM OCQG T0 WHERE T0.[GroupCode] = 64) else null end as prop from OCRD T0 where T0.CardCode = @CardCode) X
    WHERE NOT X.prop IS NULL

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded