Using Extension Schema

[Former Member]

Hi Experts,

I tried to retrive 2 columns from an ordinary schema and 2 columns from an extended schema using a query but I could only see the records for ordinary schema being displayed and the extended schema columns were blank without any values. What might be the cause of this problem.

Please help. Thanks in Advance.

Sharmele.

[vikram_shanmugasundaram]

Hi Sharmele,

May be the table joins are not correct. It's hard to tell what the issue might be without seeing the actual query. Can you post the query?

Regards,

Vikram

[Former Member]

Hi Vikram,

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T1, <%EXT_TABLE(contracts.Contract)%> T2 WHERE T1.CONTEXTID = <%CONTEXT(contracts.Contract)%> AND T1.OBJECTID = T2.PARENT_OBJECT_ID <%ORDERBY%>

This is the query. Thanks.

Sharmele.

[Former Member]

Sharmele,

Your query seems to be fine. Assuming that you are referencing the extension fields correctly, the only possibility that remains is that these extension fields might not be populated in any of the Master Agreements.

Please check that. Also, check whether you are using the correct names to reference them.

Another question : Do these extension belong to an extension collection?

Thanks

Devesh

[Former Member]

Hi Devash,

Extension does not belong to any Collection Extension. Added to that, I don't thing there will be an mistake in name reference since I copy pasted the names from the table. Thanks.

Sharmele.

Edited by: Sharmele on Jul 1, 2011 12:11 PM

[Former Member]

Hi,

Is there any way by which we can check whether the tables are populated or not.

Thanks

Sharmele.

[Former Member]

Hi,

For the query below, I got a message saying "SQL command not properly ended ". What might be the cause of the error.

Please help.

(SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T1, <%EXT_TABLE(contracts.Contract)%> T2 WHERE T1.CONTEXTID = <%CONTEXT(contracts.Contract)%> AND T1.OBJECTID = T2.PARENT_OBJECT_ID ) LEFT OUTER JOIN (SELECT DISPLAY_NAME, DOCUMENT_DESCRIPTION FROM <%SCHEMA%>.FCI_CONGEN_CONTRACT_DOC WHERE CONTEXTID = <%CONTEXT(doccommon.ContractDocument)%>)<%ORDERBY%>

Thanks,

Sharmele.

[vikram_shanmugasundaram]

Hi Sharmele,

If you can explain what data your trying to query, I may be able to recommend something. At the least, the syntax for left outer join in your SQL needs to be fixed. You might want to google syntax for left outer join.

The join condition should be T1.OBJECTID=T3.PARENT_OBJECT_ID, where T1 is FCI_CONTRACT and T3 is FCI_CONGEN_CONTRACT_DOC.

You might also want to look at standard queries in the system as a reference. For example, FCI-MyMasterAgreementsAndAgreementsDoc is a complex query, but is a good example. It lists users Master Agreements and Agreements and the related contract data. One approach to consider is, take a look at a few standard queries (starts with FCI-) and pick the one that is closest to your requirement and use that query as a starting point.

Hope this helps.

Vikram

[Former Member]

Hi Vikram,

Thanks for the reply. I wanted to add an extra field into the Result field of the "Search Master Aggreements and Agreements " query. This extra Filed is from the Extension Definition. After adding the extra field into the result field of the query, I reframed the query as

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T1, <%EXT_TABLE(Contracts.contract)%> T2

<%ORDERBY%>

Is this Correct? When Trying to run this query, I got an Exception. Please help to rectify this.

Thanks & Regards,

Sharmele.

[Former Member]

Hi,

Please help me to extract values from the extension definition. Instead of table name if i give the name of extension definition, Can I get the value from the Extension Schema?

Thanks,

Sharmele.

[Former Member]

Sharmele,

If the Search Master Agreements and Agreements query is the one you were having difficulty with in your subsequent reply, then yes this is a double query. You chose one of the more difficult ones to modify, and I highly recommend looking at the SQL preview with someone who really understands complex querying. Based on your comments, it looks like you need some SQL expertise to help you out. I would really recommend that you work with someone on your team that has SQL experience or take the SAP advanced query and reporting training course (if they still offer it).

In addition, your EXT_TABLE clause is misspelled. You need to spell the class name EXACTLY as it appears in the resource guide. For master agreements, this is contracts.Contract (with a capital C on the second word).

-Howie