DQL Help in DQL Read activity

Hi,

In Process Builder 6.7 User guide I see the following under "DQL Read" activity:
Step 7: Type the Query to Run in the text box.

To create a query that uses placeholder values, type a question mark (?) where you want the
value to be substituted. The question mark represents a parameter whose value the activity will
insert at runtime.

My DQL is as follows:

select user_address from dm_user where user_state=0 and user_name in (select i_all_users_names from dm_group where group_name like 'ecm_%')

Can any one please change my DQL with place holder(s)?

Also please give me some guidelines how I can pull the result (a collection of email addresses) into a variable (I think it must start with $$, like $$address).

Please share your knowledge and experiences for figuring this out.

Thanks.

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

CláudioGil

You use the ? in places were will want to complete the query with data you don't know before hand. So:

You want to select 'user_addresses', so that does not take the ?
You want to take that from dm_user objects, so that does not take the ? either.
You want to filter the dm_user objects using filters you do not know beforehand, so you can place ? in parts of the were clause.

Taking your initial query you could have something like:

select user_address from dm_user where user_state=? and user_name in (select i_all_users_names from dm_group where group_name like ?)

Note that the ? are placed only where values should be placed and, in this case, we say that the specific user_state and group_name pattern we will user are parameters that we map latter, when you press "Next". I user_state must be 0 then you do not use the ? but place 0 like you have in you initial example.

We you press "Next" you map the values to each ? (Input Message Mapping). When you press "Next" again you map the "user_address", from the query, to the process data (Output Message Mapping).

Note:

Doing "select ? ..." does not mean, "select values into a variable". It means "select a specific value, that I will specify when I press Next, for all matching rows".

Also, this question is very similar to the one you have in https://community.emc.com/message/630129

All comments

DCTM_Guru

Process does a strict replacement of the question mark. So if you want to parameterize group_name:

select user_address from dm_user where user_state=0 and user_name in (select i_all_users_names from dm_group where group_name like '?')

Julien.Fontaine

Athati, I don't understand what is your need ? What you want to do with your query ?

CláudioGil

Johnny Gee wrote:Process does a strict replacement of the question mark.  So if you want to parameterize group_name:select user_address from dm_user where user_state=0 and user_name in (select i_all_users_names from dm_group where group_name like '?')

Actually, currently a "smart" substitution is done so you must place the question mark without the quotes (?). You'll get an error if you try to validate a query with '?'.

athati

Thanks Johnny, Julien and Claudio for your advices, but I want to pull 'user_address'es. I think I need to place question mark in place of user_address like "select ? from dm_user....".

Julien...I need to get all email addresses of all users in the groups starting with "fotl_" into EmailMessage.To[0] field of my SMTP activity. Hope I am clear.

Thanks.

CláudioGil

You use the ? in places were will want to complete the query with data you don't know before hand. So:

You want to select 'user_addresses', so that does not take the ?
You want to take that from dm_user objects, so that does not take the ? either.
You want to filter the dm_user objects using filters you do not know beforehand, so you can place ? in parts of the were clause.

Taking your initial query you could have something like:

select user_address from dm_user where user_state=? and user_name in (select i_all_users_names from dm_group where group_name like ?)

We you press "Next" you map the values to each ? (Input Message Mapping). When you press "Next" again you map the "user_address", from the query, to the process data (Output Message Mapping).

Note:

Doing "select ? ..." does not mean, "select values into a variable". It means "select a specific value, that I will specify when I press Next, for all matching rows".

Also, this question is very similar to the one you have in https://community.emc.com/message/630129