Variable prefix in Database views - How do you decide the prefix?

[Anish Reghu]

Hi all,

While I looked for the Product documentation for Database views, it shows a example where Variable prefix shows as inc for incident. OK - Variable prefix for incident is defined in the form, look at the Where clause - how does system automatically assume chg is for change?

And can someone explain what is this Left join all about?

In simple terms please. Please do not guide me to Product documentation. I didn't understand, hence I am here.

[Jaspal Singh]

Hi Anish,

Variable prefix for Incident table is inc from the screenshot. Also, what I se you have view change_request_metric there you will have chg defined as Change request prefix.

Left join in above would mean the table/variabel to the left of '=' in above case incident will have all entries displayed even if there are not match with the correspond query to right of =

[Anish Reghu]

Actually this query was asked because I am creating a view between Story and Scrum task.

Scrum task(rm_scrum_task) has a field Story (story) in it, which displays the (short_description) of the parent story where the scrum task is logged.

Now how do I create a view that displays me the Story number (number), Story Short description from Story table

AND 

Planned hours (planned_hours), Actual hours (hours) from Scrum task table?

What should be the query given in Where clause and in which view table should I give this query?

Should Left join be set to TRUE?

[Ankur Bawiskar]

Hi,

you need to join both the tables rm_scrum_task (scm) and rm_story (sto)

Where clause as below: scm_story = sto_sys_id

Story Table: Have these 2 view fields Story Number and Short Description

Scrum Task Table: Have these 3 view fields Planned Hours and Actual Hours and Story

If you want all Stories even though they don't have any scrum task then left join on this table i.e story table

Note: For left join to work you need to apply where clause to the table which you want data from; So set left join as true for story table and this should have lower order

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Jaspal Singh · Accepted solution]

Hi Anish,

In that case you can setup a view something as below.

Once done you can report on the view created & then add required set of fields.

No need to do a left join unless you want all Stories to be shown from above even if there does not exists any scrum task.