i have 1-N Link in tables
How to call it in query def select condition
please help?
Solved! Go to Solution.
Views
Replies
Total Likes
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 <> 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
Hello @Shrutii ,
you can do it two ways:
Exist such as
Filtering dimension
First option has lower performance is recommended to use filtering dimension for linked tables
Marcel
Views
Replies
Total Likes
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
Views
Replies
Total Likes
ah ok i did not see that you want xml query def
Views
Replies
Total Likes
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 <> 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: 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 <> 0"/>
</condition>
</where>
<orderBy>
<node expr="@DCNS_ID" sort="1"/>
</orderBy>
</queryDef>
);
Views
Replies
Total Likes
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
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 <> 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
04/28/2023 7:43:44 PM SCR-160012 Javascript: 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
Views
Replies
Total Likes
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 <> 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
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
Views
Replies
Total Likes
@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.
Views
Likes
Replies