Hi Guys,
I gonna write a "Join" query & create a "View" on it. Now, I want to query the data from MySQL's "Views" & use it in my Appian application. Thanks in advance.
Discussion posts and replies are publicly visible
That sounds fine - do you have any particular question?
Yeah. Thanks for your response.
Please refer to the structure of the tables that I gonna use to create a View using Join operation.
Student Table:
Pass_Request_Details Table:
Pass_Data View:
My question is how to query this data (from above mentioned View) and use it in our appian application like in Interface, Reports etc. Thanks for your help.
Hi,
You can Create CDT for the view similar to how you create for a table and map it to the data store. Then you can query using a!queryEntity(). Querying is pretty much same how you can do with tables except write operations.
Similar to how you query from a regular table, Views are also queried by creating a CDT and Data Store Entity mapping to the view. The one word of caution I would give you is to make sure you construct your View such that your primary key column is actually unique. If it's not a unique value, funny things will happen when you query on the view, relating to the count of items it contains.
Sure. I will do. Thank you.
Thanks for your information. I will do it.
while i am creating CDT from a view the CDT is being created all good but the mapping to data store is not happening and i can't seem to find why? can you please help me??
More details please. What went wrong? Any error messages? Did you try to add the entity manually to the DS?
yes stefan, I have created a view from two of my tables and then i tried to create a CDT from view, but even after selecting the check box that lets you to crate an Entity for the Data Type, Its not able to create an Entity. and is showing an Error that states
"""
Data type created successfully. The entity was added to the selected data store, but one or more of the data entities does not map to the table structure in the selected data source. The data store has been saved as a draft. View Data Store
i am unable to get it right please help me
You need to open the draft datastore and try to validate it. It will probably see an error, click on the more option to see details of the error, and write it back here.
Thanks for replying salim in more info its showing
<<<
The data source schema does not match the type mappings: Missing column: a_id in Appian.losviewpractice (APNX-2-4056-000)
>>>
my column is named " id " actually not "a_id" like it shows in error message, thought you should know
this means in the cdt, you did not mention which column is primary key. update the corresponding cdt and mark column id as primary key. You should then be able to publish the datastore
Thank you salim, I got it
Some additional context: Appian requires you to define a field as a PK in order to map it to a data store entity. It's also required for several other parts of Appian (e.g. as the source of a recordv type). However, a DB View doesn't have a concept of a PK field, so you often will need to construct a field in your DB view that is unique to act as the PK in Appian.
The error that you're seeing about "a_id" is Appian's attempt to automatically add a PK field for you. If you have a table, Appian on publish of the data store Appian can actually alter the table to add this as a PK. However, a database view wouldn't allow this (since it isn't a table), so that's why you're seeing this error.