Expand my Community achievements bar.

Accelerate your Campaign learning with the Adobe Campaign Mentorship Program 2023!
SOLVED

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

Avatar

Level 3

 

 

i have 1-N Link in tables

 

How to call it in query def select condition

 

please help?

 

1 Accepted Solution

Avatar

Correct answer by
Level 5

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

View solution in original post

10 Replies

Avatar

Community Advisor

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 

Avatar

Level 3

@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

Avatar

Community Advisor

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

Avatar

Level 3

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>
);

Avatar

Level 5

Hi @Shrutii 

 

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

AkshayAnand_0-1682677288362.png

 

Also pasting the link to documentation.

 

Hope it helps.

 

Regards

A

 

Avatar

Level 5

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

Avatar

Level 3

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

Avatar

Correct answer by
Level 5

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

Avatar

Level 3

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

Avatar

Employee Advisor

@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.