Skip to Content
1
May 03, 2017 at 07:07 PM

Business Partner Property Marked Query

331 Views

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.