Query Definition

[Former Member]

Hi,

I have created an external field in the Agreements section with data type as 'TEXT'.

Now i want to generate a report which captures the field. However when i define the field in the Results tab of the query definition, the preview shows Invalid Identifier.

I assume this is due to defining the column type as String / String Resource in the field 'Column Type'

Please let me know whether my assumption is correct. If yes, what should be the value in the Column Type so that i can capture the text field?

Thanks in anticipation of your help!

Regards,

Anshuman

[vikram_shanmugasundaram · Accepted solution]

Hi Anshuman,

Text extension fields are stored as BLOBs in the DB. It’s not possible to query the full content. However, you could use the “_PREVIEW” column which will display the first 1000 characters. Use column type String.

Hope this helps.

Regards,

Vikram

[Former Member · Accepted solution]

Hi Vikram,

I could not see "PREVIEW" as an option. Could you please provide me some more details. The error i am getting is

ORA-00904: "T2"."TST_ONE": invalid identifier

Thanks,

Anshuman

[vikram_shanmugasundaram · Accepted solution]

If your extension field id is TST_ONE, you should see in the FCI_DYN_xxxxxx table a column named TST_ONE_TEXT_PREVIEW. You can add this column to your query which will display the first 1000 characters.

Regards,

Vikram

[Former Member · Accepted solution]

Hi Vikram,

Thanks a lot!

Regards,

Anshuman

[Former Member · Accepted solution]

Hi Vikram,

Please let me know to resolve this issue.

I am facing similar issue when I am trying to fetch an Extension Field in Result Fields of Query Definition.

Do we need to add FCI_DYN_XXX table in FROM clause?

Thanks,

Saloni

[vikram_shanmugasundaram · Accepted solution]

Hi Saloni,

For querying extension fields you would have to use the <%EXT_TABLE(...)%> token in the FROM clause of your SQL statement. The help section contains details on the usage of this token.

Regards,

Vikram