i have 1-N Link.How to call it in query def select condition

[Shrutii]

i have 1-N Link in tables

How to call it in query def select condition

please help?

[Marcel_Szimonisz]

Hello @Shrutii ,
you can do it two ways:
Exist such as

• In query condition select (advanced fields, ... on bottom page, if the links are not shown)
• find your link and double click on it
• it will create condition exits such as
• create condition

Filtering dimension

• In query activity you can select targeting dimension and filtering dimension
• if the link is configured correctly you should see the linked table in the filtering dimension select
• create condition to filter you target data

First option has lower performance is recommended to use filtering dimension for linked tables

Marcel 

[Shrutii]

@Marcel_Szimonisz 

an you PLEASE SEDN SAMPLE CODE PLEASE

lets consider t

able1 has a,b,c columns--

and table 2 has d,e,f columns

and both table1 have 1 to N link with table2

please help

[Marcel_Szimonisz]

ah ok i did not see that you want xml query def

[Shrutii]

i m lookin for query def condition like tis

var queryOptinBDerivedConsumer = xtk.queryDef.create(
<queryDef distinct="true" schema="elc:BDERIVEDCONSUMER" operation="select">
<select>
<node expr="@DCNS_ID"/>
<node expr="[BRND_ID/@BRND_CODE]" alias="@BRND_CODE"/>

<node expr="[consumer/HSLD_ID/@HSLD_HKEY]" a.lias="@HSLD_HKEY"/>
<node expr="[sourceConsumerLink/@SRCC_CONSUMERID]"  alias="@EMDA_SOURCETIMESTAMP"/>
</select>

<where>
<condition boolOperator="AND" expr="@DCNS_BESTRECORD_TOCH_ID &lt;&gt; 0"/>
<condition boolOperator="AND" expr="[neoOptin-DCNS_ID]" internalId="1351616217" setOperator="EXISTS">
======================

now this table BEMEDIA-DCNS_ID has n link with above menrioned BDERIVED TABLE

I NEED TO ADD one field from BEMEDIA-DCNS_ID in query condition select where condition:

so i write this but receivin below error:
<condition boolOperator="AND" expr="[BEMEDIA-DCNS_ID/@EMDT_CODE]='emlprs'"/>

how to resolve this error:

04/28/2023 5:15:35 PM SCR-160012 Javascript&colon; error while evaluating script 'WKF12218/jsx2'.
04/28/2023 5:15:35 PM SOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.
04/28/2023 5:15:35 PM XTK-170036 Unable to parse expression '([BEMEDIA-DCNS_ID/@EMDT_CODE] = 'emlprs') AND (@forwarded = 0) AND (@origin = 0) AND (@id <> 0)'.
04/28/2023 5:15:35 PM Element 'BEMEDIA-DCNS_ID' unknown (see definition of schema 'NeoOptin (elc:neoOptin)').
<condition boolOperator="AND" expr="@forwarded = 0"/>
<condition boolOperator="AND" expr="@origin = 0"/>
<condition expr="@id &lt;&gt; 0"/>
</condition>
</where>
<orderBy>
<node expr="@DCNS_ID" sort="1"/>
</orderBy>
</queryDef>
);

[AkshayAnand]

Hi @Shrutii 

As @Marcel_Szimonisz  suggested you can use "EXISTS" in your querdef condition as below :-

Also pasting the link to documentation.

Hope it helps.

Regards

A

[AkshayAnand]

Could you please try once the below code and let me know :-

var queryOptinBDerivedConsumer = xtk.queryDef.create(
<queryDef distinct="true" schema="elc:BDERIVEDCONSUMER" operation="select">
<select>
<node expr="@DCNS_ID"/>
<node expr="[BRND_ID/@BRND_CODE]" alias="@BRND_CODE"/>

<node expr="[consumer/HSLD_ID/@HSLD_HKEY]" a.lias="@HSLD_HKEY"/>
<node expr="[sourceConsumerLink/@SRCC_CONSUMERID]"  alias="@EMDA_SOURCETIMESTAMP"/>
</select>

<where>
<condition boolOperator="AND" expr="@DCNS_BESTRECORD_TOCH_ID &lt;&gt; 0"/>
<condition boolOperator="AND" expr="[neoOptin-DCNS_ID]" internalId="1351616217" setOperator="EXISTS">
<condition boolOperator="AND" expr="BEMEDIA-DCNS_ID" setOperator = "EXISTS">
	<condition expr="@EMDT_CODE = 'emlprs'"/>
</condition>	
</where>

Regards

A

[Shrutii]

04/28/2023 7:43:44 PM SCR-160012 Javascript&colon; error while evaluating script 'WKF12218/jsx2'.
04/28/2023 7:43:44 PM SOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.
04/28/2023 7:43:44 PM XTK-170036 Unable to parse expression 'BEMEDIA-DCNS_ID'.
04/28/2023 7:43:44 PM Element 'BEMEDIA' unknown (see definition of schema 'Consumer / Touch point profile (elc:BDERIVEDCONSUMER)').

========

executed what u have pasted but

Gettin this error please help

[AkshayAnand · Accepted solution]

Hi @Shrutii 

Apologies for the typo.

var queryOptinBDerivedConsumer = xtk.queryDef.create(
<queryDef distinct="true" schema="elc:BDERIVEDCONSUMER" operation="select">
<select>
<node expr="@DCNS_ID"/>
<node expr="[BRND_ID/@BRND_CODE]" alias="@BRND_CODE"/>

<node expr="[consumer/HSLD_ID/@HSLD_HKEY]" a.lias="@HSLD_HKEY"/>
<node expr="[sourceConsumerLink/@SRCC_CONSUMERID]"  alias="@EMDA_SOURCETIMESTAMP"/>
</select>

<where>
<condition boolOperator="AND" expr="@DCNS_BESTRECORD_TOCH_ID &lt;&gt; 0"/>
<condition boolOperator="AND" expr="[neoOptin-DCNS_ID]" internalId="1351616217" setOperator="EXISTS">
<condition boolOperator="AND" expr="BEMEDIA" setOperator = "EXISTS">
	<condition expr="@EMDT_CODE = 'emlprs'"/>
</condition>	
</where>

Regards

A

[Shrutii]

can you PLEASE SEDN SAMPLE CODE PLEASE

lets consider t

able1 has a,b,c columns--

and table 2 has d,e,f columns

and both table1 have 1 to N link with table2

please help

[akshaaga]

@Shrutii ,

You can call it in a SQL query using a JOIN statement. Here's an example:

Suppose you have two tables: customers and orders.

The customers table has columns customer_id, first_name, and last_name. The orders table has columns order_id, customer_id, order_date, and total_amount.

To select all orders for a specific customer, you can use the following SQL query:

SELECT orders.order_id, orders.order_date, orders.total_amount

FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.first_name = 'Shruti' AND customers.last_name = 'Abc';

This query selects the order_id, order_date, and total_amount columns from the orders table and joins the customers table on the customer_id column.

The WHERE clause filters the results to only include orders for a customer with the first name ‘Shruti’ and the last name "Abc". You can modify the WHERE clause to search for a different customer or use other conditions to filter the results based on your specific needs.