SQL Query as Attribute value

[Former Member]

Hi,

I have a little problem with an newly created custom attribute. This attribute has presentation SingleSelect and the attribute values should be selected via an SQL query. So far so good.

This attribute is displayed in a WET. When I open these WET I get an error messages with the following text: Could not expand values for attribute <ATTRIBUTE NAME>.

The used SQL query

SELECT distinct REP_NAME 
   FROM MC_REPOSITORY
   WHERE REP_NAME is not null and (LENGTH(REP_NAME) = 6)

When we use the following SQL Query everything works fine.

SELECT distinct avalue 
   FROM MXIV_ENTRIES 
   WHERE attrname='ACCOUNTADS'

My problem now is that I don't understand why my query from MC_REPOSITORY doesn't work but the other works fine.

Best regards

Lars

[jared_kobe · Accepted solution]

Lars,

It has been my experience that when the UI throws that error on a SQL Query lookup when it doesn't have the appropriate rights to the table. I checked our installation, and the mxmc_prov user doesn't have the select permission on our MC_REPOSITORY SQL table. If this is the case, you'd just have to add the permissions to the table.

Hope that helps!

[Former Member · Accepted solution]

Hi Jared,

Sometimes the answer is so easy that you don't think in that direction. I don't thought of missing permissions. I am now using the view (MCV_REPOSITORY) Christian mentioned for my SQL Query.

Thanks to you two, you both made my day.

Best regards

Lars

[Former Member]

Hi Lars

Jared is right. The user *_prov user doesn't have right to this table.

But there is a view which works.

If you don't find it, tell me and I have a look again tomorrow