How do I populate a table in SQL if I want to use a table key lookup in a category?

Maria_Cherkaoui_(mariacherkaoui_-_(deleted))

I have an excel spreadsheet with data that I would like to use to have cascading attributes in a category. I was advised to use the Attribute Extension Module, which allows for Table Key Lookup attributes in a category. The desired end result is for users to be able to select several metadata fields that would be cascading as they drill down to the final field, exactly like the example shown in the document "Attribute Extensions 4.0.3 Installation and User Guide " Section 3.1.2 Attribute Definition?Cascading. My question is- how do I create and populate a table similar to the example? I am using DTS to import an excel spreadsheet right now and while it retrieves the correct distinct values, when I select the first value I receive this:Livelink Error: [Invalid select statement!] I think that this is because my table was not created/populated correctly. How has everyone else created their Tables in SQL to use the Table Key Lookup feature for Categories/attributes? Please keep in mind that I am not using forms or workflows.Thanks for any help you can provide,Maria

Find more posts tagged with

Comments

Appu_Nair

You are right the table is structured or created by you following the logic of the cascading experience you want to give to the pickerFor eg first show all countries (distinct country name) Then states of countries etc.The clue is the attribute that you create can be referred to the successive one by means of angle brackets or replacement variables so it serves as your "where" clause for the next one.Table and data has to be maintained by you,if it is a livelink form end users can add/edit data very easily.I can't seem to find a simple one right now but believe me it is not as difficult as it sounds maybe the table design may.

Pierre_Nocera

Message from Pierre Nocera <pnocera@opentext.com> via eLink

eLink

Hi Maria, could you show the sql statement ?

Pierre

De: eLink Discussion: Development Discussion [mailto:development@elinkkc.opentext.com]
Date: jeu. 26/02/2009 20:35
À: eLink Recipient
Objet : 2nd Screenshot of Error

2nd Screenshot of Error		Posted by mcherkaoui (Cherkaoui, Maria) on 2009/02/26 14:31
In reply to: How do I populate a table in SQL if I want to use a table key lookup in a category?		Posted by mcherkaoui (Cherkaoui, Maria) on 2009/02/26 14:28

Attachment Links:

stationid-error.JPG (26 KB)

[To reply to this thread, use your normal E-mail reply function.]
Topic:	How do I populate a table in SQL if I want to use a table key lookup in a category?
Discussion:	Development Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.development@elinkkc.opentext.com.

Pierre_Nocera

Message from Pierre Nocera <pnocera@opentext.com> via eLink

eLink

Ok, you didn't set a SQL statement in your attribute definition but used the build in feature of attribute lookup, sorry. Perhaps you may try to setup a specific SQL statement in the attribute definition, this would allow you to have better control on the lookup...

Pierre

De: eLink Discussion: Development Discussion [mailto:development@elinkkc.opentext.com]
Date: jeu. 26/02/2009 21:37
À: eLink Recipient
Objet : Which SQL statement?

Which SQL statement?		Posted by mcherkaoui (Cherkaoui, Maria) on 2009/02/26 15:35
In reply to: REÂ 2nd Screenshot of Error		Posted by pnocera (Nocera, Pierre) on 2009/02/26 15:01

[To reply to this thread, use your normal E-mail reply function.]
Topic:	How do I populate a table in SQL if I want to use a table key lookup in a category?
Discussion:	Development Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.development@elinkkc.opentext.com.

Michael Ennemoser

Dear Maria,did you got a hint about the problem solution? I face the same problem. I have a custom table in the Oracle DB and use this table for Table Lookup Fields in different categories. In some categories the Lookup field is working properly, in some I got your 2nd error message.

gimmal001

I encountered this issue and opened a support case. It is related to bug LLAE-252. If you are using attribute extensions on a category that has another attribute that uses a text popup with only a single value in the list, you will get this error. It is tricky because it isn't the attribute extensions attribute that can't have a a single value. If you add a second value to the attribute on the same category that uses a text popup, you will not see the issue.Summary:Livelink Error: [Invalid select statement!] using Attribute Extensions Module 4.2.0Resolution:LLAE-252 "Invalid select Statement" error when using category with TKL and Text Popup attribute with only one value.Asked customer to add another value to his pop-up attribute.

John Underhill

You also get "Invalid Select Statement" if you have a custom query and it contains any of:dropdeleteinsertupdatealtertruncatecreate <- caught me out as I needed it in the WHERE clauseexecxp_sp_--;If you MUST have one of the words above use a string concat like:WHERE COLUMN='Cre'||'ate'

David_Henshaw

Hi,

I've encountered similar issue where I had a series of cascading attributes (i.e. select 1st, 2nd, then auto-populate third).

We found that if a source of your data is an integer and only one row is returned, the SQL fails.

Try explicitly setting the SQL on the attribute as follows

Select convert(varchar, X) from Y where.....

Replace X and Y as appropriate.

It seems that if the field isn't explicitly selected by the user (which requires more than one row be present for the values of the attribute), then the SQL fails to convert integer/date (non text) database columns.

Explicitly converting to varchar seemed to fix our issue.

Regards,

David Henshaw

Fastman Consulting & Solutions