Skip to Content
1

Business Partner Property Marked Query

May 03, 2017 at 07:07 PM

107

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt May 05, 2017 at 06:03 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you so much!

0
Johan Hakkesteegt May 04, 2017 at 07:17 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your help. Is there any way to do the same you did but for all the entire customers at the same time.

Thank you so much again.

0