Using UserViews to call records from the same database

[Murray Miskelly]

Hi there,

I know that these are very basic questions for someone used to using SQL and UserViews, but I am getting totally flummoxed.

I have a requirement to recall multiple records from the main recipients database to populate a table ADOR on a page, and to display on an HTML email.

The scenario is that in the main campaign database ('CampaignDB'), there is a table ('Recipients').  In the campaign each recipient has the opportunity to add one or more friends with the 'refer-a-friend' methodology. Each friend has the name of the referrer stored in fields TLF, and TLL (being Team Leader Firstname, and Team Leader Lastname respectively), as well as their own names in 'Firstname', and  'Lastname', and their contact details in 'Mobile', and 'Email'. Referred friends are also identified by the value 'F' being set in the 'Status' column.

I have been trying to create a UserView (called TeamMembers) to return Firstname, Lastname, Mobile, and Email for every record that TLF =Firstname, and TLL = Lastname for the current record, but my working knowledge of SQL is sparse (being generous) and it keeps failing the parse check..

In essence I am trying to write the following:

SELECT [TeamMembers].[Firstname], [TeamMembers].[Lastname], [TeamMembers].[Mobile], [TeamMembers].[Email]

FROM @[Recipients]

WHERE TLF=|->[Firstname] AND TLL=|->[Lastname]

So my request is in two parts, firstly I need to get this happening so assistance with the actual syntax would be welcome, but secondly, I have had some assistance from the XMPie support team, that although their suggestions haven't worked yet it has added some other SQL lines that I would like to know specifically the meaning and usage for.

a/. Why is it that sometimes the data field is referenced [Recipients].[Firstname], and other times @{Recipients}.[Firstname]?

b/. Why precede the above SQL statement with @{CampaignDB}: ? 

c/. In the UserView query should the SELECT clause list the fields defined in the UserView, thus binding results to them, or should it refer to the actual table and fields that the data is held in?

I have looked at the XMPie manuals for UserViews and besides the examples containing multiple syntax errors they show the UserView with the same name as one of the data tables, so I am confused with that even.

Any assistance with this query is most welcome.

Murray

I will return to W3 schools......

[Wayne]

Some answers to your questions

a/. Why is it that sometimes the data field is referenced [Recipients].[Firstname], and other times @{Recipients}.[Firstname]?

I think you mean @[Recipients].[Firstname] this allows uPlan to convert the Query to the correct name depending on the Data Source 

http://help.xmpie.com/uPlan/v.8.2/Help/en/uPlanUserGuide/uDirectRuleEditing/Using_QLingo_Generic_Query_Syntax_When_Working_with_Multiple.htm?rhtocid=_10_9_0#TOC_Using_QLingo_Generic 

b/. Why precede the above SQL statement with @{CampaignDB}: ?

  "@{CampaignDB}:" References the Schema this is required when multiple data sources are used  

   http://help.xmpie.com/uPlan/v.8.2/Help/en/uPlanUserGuide/uDirectRuleEditing/SQL_Queries.htm?rhtocid=_10_9#TOC_SQL_Queries 

c/. In the UserView query should the SELECT clause list the fields defined in the UserView, thus binding results to them, or should it refer to the actual table and fields that the data is held in?

  The Fields in the UserView need to match the Fields returned in the SQL Query. When writing your SQL Query the you can make the Field (Column) name whatever you like by using the AS (Alias) operator or referencing the name.

https://www.w3schools.com/sql/sql_alias.asp

Is your SQL not working? are you getting errors or not getting the results expected.

Regards,

Wayne

[Tyrone]

Try creating a userview and use the below to get to what you need. Remember the userview needs to contain all the fields you are requiring and an ID to link from the Database to the adore, I used email. This is an example of a userview select I’ve used before

 

@{ CampaignDB }:

 

SELECT [TeamMembers].[Firstname], [TeamMembers].[Lastname], [TeamMembers].[Mobile], [TeamMembers].[Email]

FROM   [Recipients]

WHERE [Recipients].[Email] = |->[Email]

 

Regards,

 

Tyrone O’Brien

Director | DM Solutions Specialist

 

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xmpie-users...@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

[Murray Miskelly]

Wayne,

Many thanks for the reply. Those help links were informative.

Currently the error I am getting is:              Error: Failed to execute query. The multi-part identifier "TeamMembers.Firstname" could not be bound.

I have rewritten the query using your suggestions, and Tyrone's (see below)  but still getting the same result.

Cheers,

Murray

[Murray Miskelly]

Hi Tyrone,

Thanks for the response.. As I mentioned to Wayne I am still getting an error Failed to execute query. The multi-part identifier "TeamMembers.Firstname" could not be bound.

This leads me to think that I still am not getting the right syntax.

I will translate your suggestion into how I am reading it just to see if I am on the correct path.

@{CampaignDB}:                                                                                                                                                                              -- The name of the SQL database that holds the Recipients table (and another table I have not mentioned)

SELECT [TeamMembers].[Firstname],[TeamMembers].[Lastname],[TeamMembers].[Mobile]                                                       -- The three fields I have defined in the UserView "TeamMembers" that I have declared in uPlan

FROM [Recipients]                                                                                                                                                                           -- The main Recipients table as defined in the CampaignDB

WHERE [Recipients].[Email] = |->[Email]                                                                                                                                         -- Email field from the Recipients table equals the value of the Email field in the record being browsed.

Thanks again for any comment,

Murray

[Wayne]

Hi Murray,

I think you are confusing filling the UserView with accessing the the UserView 

Try-

SELECT [Firstname], [Lastname], [Mobile], [Email]

FROM @[Recipients]

WHERE TLF=|->[Firstname] AND TLL=|->[Lastname]

Please email me the Plan file if you are still having problems.

Regards,

Wayne

[Murray Miskelly]

Wayne,

Thanks for setting me straight. Yes, I was getting confused as to where the binding from the UserView to the active table occurred. Once I reverted back to your example and added an ALIAS to allow that some of my UserView fields had slightly different names then everything worked fine.

Thanks again.

MM