ICatalogManager3::SetJoinTable Method [C++]

Article
11/12/2009

Use this method to join an external table to a table in the Product Catalog System.

For a list of the methods that support the JoinTable feature, see Code to Join an External Table to Product Recordsets.

Definition

[C++]

HRESULT ICatalogManager3::SetJoinTable(BSTRstrTargetTableName,
  BSTRstrSourceJoinKey,
  BSTRstrTargetJoinKey,
  CatalogJoinTypeEnumeJoinType);

[Visual Basic]

Sub SetJoinTable(strTargetTableName As String,
  strSourceJoinKey As String,
  strTargetJoinKey As String,
  eJoinType As CatalogJoinTypeEnum)

Parameters

strTargetTableName[C++]
[in] A BSTR that contains the name of the external table or view with which to join.
strTargetTableName[Visual Basic]
A String that contains the name of the external table or view with which to join.
strSourceJoinKey[C++]
[in] A BSTR that contains the name of the product property to join on.
strSourceJoinKey[Visual Basic]
A String that contains the name of the product property to join on.
strTargetJoinKey[C++]
[in] A BSTR that contains the name of the column in the external table or view to join against.
strTargetJoinKey[Visual Basic]
A String that contains the name of the column in the external table or view to join against.
eJoinType[C++]
[in] A CatalogJoinTypeEnum enumeration that specifies the join type. See the Remarks section for valid values.
eJoinType[Visual Basic]
A CatalogJoinTypeEnum enumeration that specifies the join type. See the Remarks section for valid values.

Return Values

[C++] This method returns an HRESULT indicating whether it completed successfully. See the Error Values section for more details.

[Visual Basic] None.

Error Values

[C++] This method returns S_OK (0x00000000) to indicate success and standard COM HRESULT error values to indicate failure. For more information about standard COM errors, see Standard COM Errors. Additional information may be available using the global Err object, which can be accessed using the API function GetErrorInfo. In particular, the GetDescription method of the IErrorInfo interface may return a text description of the error.

[Visual Basic] This method sets the Number property of the global Err object to S_OK (&H00000000) to indicate success and to standard COM error values to indicate failure. For more information about standard COM errors, see Standard COM Errors. Additional information may be available using the global Err object. In particular, the Description property may contain a text description of the error.

The following table describes the custom COM errors this method can return.

Name	Value	Description
E_CAT_CATMGR_NOT_INITIALIZED	[C++] 0x889800B [Visual Basic] &H8898005	The CatalogManager object has not been initialized. The CatalogManager object should be initialized before calling this method.
E_CAT_INVALID_PARAMETER	[C++] 0x889800B [Visual Basic] &H8898006	The input parameter <parameter number> is invalid.
E_CAT_INVALID_JOIN_TYPE	[C++] 0x889800C [Visual Basic] &H8898008	The specified join type is either invalid or not supported.
E_CAT_INVALID_TARGET_TABLE	[C++] 0x889800C [Visual Basic] &H8898009	The target (join) table that was specified could not be found in the database.
E_CAT_INVALID_SOURCE_JOIN_KEY	[C++] 0x889800C [Visual Basic] &H889800A	The specified source join-key is invalid.
E_CAT_INVALID_TARGET_JOIN_KEY	[C++] 0x889800C [Visual Basic] &H889800B	The specified target join-key is invalid.

Remarks

You cannot set multiple join keys for a given source field. For example, you cannot use "ProductCode, VariantCode" as a valid string for the strTargetJoinKey parameter.

After you have called the SetJoinTable method, any method that returns product data will join the normal output of the method with the external table. That is, the join will occur after any sorting, filtering, or paging specified in the method call is completed. This means that you cannot sort or filter on columns in the external table, or do paging on data from the external table. For methods that accept the PropertiesToReturn parameter, you can specify the properties to return from the external table. If none are specified in these methods, all columns will be returned.

Join table information does not persist beyond the life of the CatalogManager object.

The following table shows the values in the CatalogJoinTypeEnum enumeration, their associated names, and describes their use.

Name	Value	Description
CMS_INNER_JOIN	0	Returns only rows where the keys are equal.
CMS_OUTER_JOIN_LEFT	1	Returns all rows in the Product Catalog System and matching rows in the target table.
CMS_OUTER_JOIN_RIGHT	2	Returns matching rows in the Product Catalog System and all rows in the target table.
CMS_OUTER_JOIN_FULL	3	Returns all rows in both tables.
CMS_NO_JOIN	-1	Turns off the join with the external table.

When you use the ProductID, VariantID, or other indexed column in the CatalogProducts table as a join key, it is recommended that you index the join key column in the external joined table for better performance.

If you pass a parameter containing spaces to the SetJoinTable method, that parameter must be enclosed by square brackets([]).

For more information about the methods to which the JoinTable feature applies, see Code to Join an External Table to Product Recordsets.

For more information about memory ownership issues related to COM property and method parameters, see Managing COM Parameter Memory.