Skip to main content
Solved

How to fetch details of Contact User Query Profiles?


Forum|alt.badge.img+7

We have a requirement to fetch what all Cost Centers are linked to Contact User Query Profiles.

Don’t see any DMW or DB table that gives such relation.

Best answer by McrPauloV

Hi,

You dont have commented your assyst version, but I think this query below (tested on assystdb 11.8) will work for you.

img1 - assyst config

 

img2 - Query Results

img3 - SQL Query

SELECT uqp.usr_qry_prof_sc "CONT_USER_QRY_PRF_SC"
    ,uqp.usr_qry_prof_n "CONT_USER_QRY_PRF_N"
    ,cc.cost_centre_sc "COST_CENTRE_SC"
    ,cc.cost_centre_n "COST_CENTRE_N"
FROM usr_qry_prof uqp
INNER JOIN usr_qry_crit uqc ON uqc.usr_qry_prof_id = uqp.usr_qry_prof_id
INNER JOIN usr_qry_crit_val uqcv ON uqcv.usr_qry_crit_id = uqc.usr_qry_crit_id
INNER JOIN cost_centre cc ON cc.cost_centre_id = start_int
WHERE uqc.usr_qry_crit_n = 'costCentreId'

View original
Did this topic help you find an answer to your question?

3 replies

McrPauloV
Sidekick (Customer)
Forum|alt.badge.img+4
  • Sidekick (Customer)
  • 9 replies
  • Answer
  • July 26, 2024

Hi,

You dont have commented your assyst version, but I think this query below (tested on assystdb 11.8) will work for you.

img1 - assyst config

 

img2 - Query Results

img3 - SQL Query

SELECT uqp.usr_qry_prof_sc "CONT_USER_QRY_PRF_SC"
    ,uqp.usr_qry_prof_n "CONT_USER_QRY_PRF_N"
    ,cc.cost_centre_sc "COST_CENTRE_SC"
    ,cc.cost_centre_n "COST_CENTRE_N"
FROM usr_qry_prof uqp
INNER JOIN usr_qry_crit uqc ON uqc.usr_qry_prof_id = uqp.usr_qry_prof_id
INNER JOIN usr_qry_crit_val uqcv ON uqcv.usr_qry_crit_id = uqc.usr_qry_crit_id
INNER JOIN cost_centre cc ON cc.cost_centre_id = start_int
WHERE uqc.usr_qry_crit_n = 'costCentreId'


Forum|alt.badge.img+7
  • Author
  • Do Gooder (Customer)
  • 20 replies
  • July 26, 2024

Thank you so much @McrPauloV. This SQL query gives me the exact data that I was looking for.


McrPauloV
Sidekick (Customer)
Forum|alt.badge.img+4
  • Sidekick (Customer)
  • 9 replies
  • July 26, 2024

Great!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings