GetQueryDefinition

Initial Build

 2007.01

xWEB

 Web Method

Response Type

XML Node

This web method is used to document the List Table setup for a specific Facade Object to use the GetQuery web method most effectively. This method first appears in 2007.01.

In order to use GetQuery, you will need to know which tables and columns are available, and in the case where a single Table is represented more than once in a List Table setup (which is a common occurance), you will need to know the table alias to use in your GetQuery calls to avoid "ambiguous column name" errors.

This web method returns all that information in a XmlNode.

You will need to pass the Facade Object name as a parameter (for example, Individual or Organization).

Note: This web method is not intended to be used in production, but only as a reference or for documentation.

Only Tables and Columns the xWeb User is authorized to select will be returned, based on the underlying netFORUM group table/column security model.

Please see Xweb Laboratory for an interactive web site developed by Avectra that provides a visual, interactive result of GetQueryDefinition.

Parameters

szObjectName (string). The Object name. See XWeb:GetFacadeObjectList#Object_Name for more direction on this parameter.

Response

XML Node

Sample Request

Pass the Object name in szObjectName. 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>77497299-ab40-43f3-9cc0-d38b23e82d18</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:GetQueryDefinition>
<ns:szObjectName>Individual</ns:szObjectName>
</ns:GetQueryDefinition>
</soapenv:Body>
</soapenv:Envelope>

Sample Response

Example of GetQueryDefinition response in the Xweb Laboratory. The outermost node ("EventsRegistrant") is the overall List Table. The next set of nodes are its From Tables. The single expanded From Table ("ev_registrant_type") shows the columns in that table that are available in GetQuery.

Shortened for brevity (as explained in comments).

Here is a condensed response structure with elements omitted: 

<Object>
<ListTable>
<ListFromTables>
<ListFromTable>
<Columns>
<Column/>
<Column/>
</Columns>
<ListFromTableColumns>
<ListFromTableColumn />
<ListFromTableColumn />
</ListFromTableColumns>
</ListFromTable>
<ListFromTable>
<Columns>
<Column />
<Column />
</Columns>
</ListFromTable>
</ListFromTables>
</ListTable>
</Object>

The single <Object> root node contains the description of the object, based on the szObjectName parameter passed in.

Within the <Object> node is a single <ListTable> node contains the name of the main Table associated with the Object, as well as a <ListFromTables> node.

The <ListFromTables> node will contain a collection of one or more <ListFromTable> nodes.

Each <ListFromTable> node contain the name of the table in <lsf_from_table> as well as the alias, if any (<lsf_from_alias>). You will need to use the alias when referencing this table in GetQuery. Additionally, the join type and join expression are included for additional detail, as well as the table description (<mdt_description>).

Also within <ListFromTable> are two collections, <Columns> and <ListFromTableColumns>.

The <Columns> node will contain one or more <Column> nodes, one for each column in that table that is available in GetQuery. Each <Column> node contains the column name and additional information.

The <ListFromTableColumns> node, which may be empty, contains any default columns that are returned by the default list result if szColumnList is passed without a parameter. These are returned in one or more <ListFromTableColumn> nodes.

Here is a more complete response, with some nodes clipped for brevity (as explained in the code). 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>1a05b44f-42a3-472b-94b6-12ccc54005f2</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<GetQueryDefinitionResponse xmlns="http://www.avectra.com/2005/">
<GetQueryDefinitionResult>
<Object xmlns="">
<obj_key>F41B6E06-299B-4022-BE6F-0641BA87DE59</obj_key>
<obj_name>Individual</obj_name>
<obj_description>Individual</obj_description>
<ListTable>
<lst_mdt_name>co_individual</lst_mdt_name>
<lst_select_distinct>1</lst_select_distinct>
<mdt_description>Individual</mdt_description>
<ListFromTables>
<ListFromTable>
<lsf_from_table>co_individual</lsf_from_table>
<lsf_from_alias xsi:nil="true"/>
<lsf_from_join_type xsi:nil="true"/>
<lsf_from_join xsi:nil="true"/>
<mdt_description>Individual</mdt_description>
<Columns>
<Column>
<mdc_name>ind_cst_key</mdc_name>
<mdc_description>Primary Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_individual</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<Column>
<mdc_name>ind_prf_code</mdc_name>
<mdc_description>Prefix</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>1</mdc_nullable>
<mdc_table_name>co_individual</mdc_table_name>
<mdc_width_max>20</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
<ListFromTableColumns>
<ListFromTableColumn>
<lsc_mdc_name>ind_first_name</lsc_mdc_name>
<lsc_name_alias>First</lsc_name_alias>
<lsc_order>1</lsc_order>
</ListFromTableColumn>
<ListFromTableColumn>
<lsc_mdc_name>ind_last_name</lsc_mdc_name>
<lsc_name_alias>Last</lsc_name_alias>
<lsc_order>5</lsc_order>
</ListFromTableColumn>
</ListFromTableColumns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>co_customer</lsf_from_table>
<lsf_from_alias xsi:nil="true"/>
<lsf_from_join_type>Join</lsf_from_join_type>
<lsf_from_join>
                  cst_key=ind_cst_key and ind_delete_flag=0
                  and ind_deceased_flag=0
</lsf_from_join>
<mdt_description>Customer</mdt_description>
<Columns>
<Column>
<mdc_name>cst_key</mdc_name>
<mdc_description>Customer Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_customer</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<Column>
<mdc_name>cst_type</mdc_name>
<mdc_description>Customer Type</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_customer</mdc_table_name>
<mdc_width_max>20</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
<ListFromTableColumns>
<ListFromTableColumn>
<lsc_mdc_name>cst_org_name_dn</lsc_mdc_name>
<lsc_name_alias>Organization</lsc_name_alias>
<lsc_order>40</lsc_order>
</ListFromTableColumn>
<ListFromTableColumn>
<lsc_mdc_name>cst_eml_address_dn</lsc_mdc_name>
<lsc_name_alias>E-mail</lsc_name_alias>
<lsc_order>45</lsc_order>
</ListFromTableColumn>
<ListFromTableColumn>
<lsc_mdc_name>cst_no_email_flag</lsc_mdc_name>
<lsc_name_alias xsi:nil="true"/>
<lsc_order>200</lsc_order>
</ListFromTableColumn>
</ListFromTableColumns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>co_customer_x_address</lsf_from_table>
<lsf_from_alias xsi:nil="true"/>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>cst_cxa_key=cxa_key</lsf_from_join>
<mdt_description>Customer Address</mdt_description>
<Columns>
<Column>
<mdc_name>cxa_cst_key</mdc_name>
<mdc_description>Customer Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_customer_x_address</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<Column>
<mdc_name>cxa_adr_key</mdc_name>
<mdc_description>Address Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_customer_x_address</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<!-- Additional "ListFromTable" nodes removed for brevity -->
<ListFromTable>
<lsf_from_table>co_organization</lsf_from_table>
<lsf_from_alias xsi:nil="true"/>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>ixo_org_cst_key=org_cst_key</lsf_from_join>
<mdt_description>Organization</mdt_description>
<Columns>
<Column>
<mdc_name>org_cst_key</mdc_name>
<mdc_description>Organization Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_organization</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<Column>
<mdc_name>org_name</mdc_name>
<mdc_description>Organization Name</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>co_organization</mdc_table_name>
<mdc_width_max>150</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>mb_membership</lsf_from_table>
<lsf_from_alias>Membership</lsf_from_alias>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>ind_cst_key = Membership.mbr_cst_key and Membership.mbr_delete_flag=0 and Membership.mbr_terminate_date is null and Membership.mbr_chp_cst_key is null</lsf_from_join>
<mdt_description>Membership</mdt_description>
<Columns>
<Column>
<mdc_name>mbr_src_code</mdc_name>
<mdc_description>Source Code</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>1</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>50</mdc_width_max>
</Column>
<Column>
<mdc_name>mbr_asn_code</mdc_name>
<mdc_description>Association</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>10</mdc_width_max>
</Column>
<Column>
<mdc_name>mbr_join_date</mdc_name>
<mdc_description>Join Date</mdc_description>
<mdc_data_type>av_date_small</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>1</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>mb_member_type</lsf_from_table>
<lsf_from_alias xsi:nil="true"/>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>Membership.mbr_mbt_key=mbt_key</lsf_from_join>
<mdt_description>Member Type</mdt_description>
<Columns>
<Column>
<mdc_name>mbt_key</mdc_name>
<mdc_description>Unique Key</mdc_description>
<mdc_data_type>av_key</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>mb_member_type</mdc_table_name>
<mdc_width_max>16</mdc_width_max>
</Column>
<Column>
<mdc_name>mbt_code</mdc_name>
<mdc_description>Member Type</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>mb_member_type</mdc_table_name>
<mdc_width_max>50</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>mb_membership</lsf_from_table>
<lsf_from_alias>ChapterMembership</lsf_from_alias>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>ind_cst_key = ChapterMembership.mbr_cst_key and ChapterMembership.mbr_delete_flag=0 and ChapterMembership.mbr_terminate_date is null and ChapterMembership.mbr_chp_cst_key is not null</lsf_from_join>
<mdt_description>Membership</mdt_description>
<Columns>
<Column>
<mdc_name>mbr_src_code</mdc_name>
<mdc_description>Source Code</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>1</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>50</mdc_width_max>
</Column>
<Column>
<mdc_name>mbr_asn_code</mdc_name>
<mdc_description>Association</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>10</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<ListFromTable>
<lsf_from_table>mb_membership</lsf_from_table>
<lsf_from_alias>OrgMembership</lsf_from_alias>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>org_cst_key=OrgMembership.mbr_cst_key and OrgMembership.mbr_delete_flag=0 and OrgMembership.mbr_terminate_date is null and OrgMembership.mbr_chp_cst_key is null</lsf_from_join>
<mdt_description>Membership</mdt_description>
<Columns>
<Column>
<mdc_name>mbr_src_code</mdc_name>
<mdc_description>Source Code</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>1</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>50</mdc_width_max>
</Column>
<Column>
<mdc_name>mbr_asn_code</mdc_name>
<mdc_description>Association</mdc_description>
<mdc_data_type>nvarchar</mdc_data_type>
<mdc_ext>0</mdc_ext>
<mdc_nullable>0</mdc_nullable>
<mdc_table_name>mb_membership</mdc_table_name>
<mdc_width_max>10</mdc_width_max>
</Column>
<!-- additional "Column" nodes removed for brevity -->
</Columns>
</ListFromTable>
<!-- Additional "ListFromTable" nodes removed for brevity -->
</ListFromTables>
</ListTable>
</Object>
</GetQueryDefinitionResult>
</GetQueryDefinitionResponse>
</soap:Body>
</soap:Envelope>