Confirm

Expand all | Collapse all

Enquiry attributes

  • 1.  Enquiry attributes

    Posted 06-23-2019 07:15
    I'm fairly new to report writing and I'm stuck on sql for written for attributes fields, can someone direct to guidance on report writing for attributes

    ------------------------------
    Afzal
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Enquiry attributes

    Posted 06-24-2019 06:01
    Hi Afzal,

    You can either link the relevant tables using a sub query or as a left join.
    The following is an example of a left join to get the attribute value name.

    FROM
    feature AS f1 LEFT JOIN
    feat_attrib_type AS fatlmh ON

    f1.site_code = fatlmh.site_code AND
    f1.plot_number = fatlmh.plot_number AND
    fatlmh.attrib_type_code IN ('LMH') LEFT JOIN
    attribute_value AS avlmh ON

    fatlmh.attrib_type_code = avlmh.attrib_type_code AND
    fatlmh.attrib_value_code = avlmh.attrib_value_code

    LMH is an attribute Type Code we have in our db and you would need to substitute this with your own code. You would need to repeat the above for other attribute type codes to get the values as different columns, and replace LMH with the other attribute type codes.

    Hope this helps?

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 3.  RE: Enquiry attributes

    Posted 06-24-2019 08:16
    Hello Hafizur, thank you for your response.

    Lets say i have attribute setup for full payment date in customer services and how would i define that data to retrieve full payment data from the fields below

    SELECT
    enquiry_attribute.enquiry_number,
    enquiry_attribute.param_type_code,
    enquiry_attribute.param_string_value,
    enquiry_attribute.param_num_value,
    enquiry_attribute.param_date_value,
    enquiry_attribute.param_value_code,
    parameter_value.param_type_code,
    parameter_value.param_value_code,
    parameter_value.param_value_name
    FROM
    enquiry_attribute,
    parameter_value
    WHERE
    parameter_value.param_type_code = enquiry_attribute.param_type_code AND
    parameter_value.param_value_code = enquiry_attribute.param_value_code

    ------------------------------
    Afzal Hussain
    Knowledge Community Shared Account
    ------------------------------



  • 4.  RE: Enquiry attributes

    Posted 06-24-2019 08:48
    Hi Afzal,

    Apologies, I missed the Enquiry bit of your message title, but the principal would be the same.

    SELECT
    cenq.enquiry_number,
    ea1.param_date_value AS date_attribute

    FROM
    central_enquiry cenq LEFT JOIN
    enquiry_attribute ea1 ON

    cenq.enquiry_number = ea1.enquiry_number AND
    ea1.param_type_code = 'DATE'

    Replace the 'DATE' code with the attribute code for your db. You can repeat this if you have multiple date attribute fields by adding further LEFT JOIN statements, or edit if you have text, number or pick list attributes.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Confirm Systems Administrator
    London Borough Of Richmond Upon Thames
    Twickenham
    ------------------------------



  • 5.  RE: Enquiry attributes

    Posted 06-24-2019 09:21
    Thanks Hafezur,

    I will give this go and come back to you

    ------------------------------
    Afzal Hussain
    Knowledge Community Shared Account
    ------------------------------



  • 6.  RE: Enquiry attributes

    Pitney Bowes
    Posted 06-24-2019 07:19
    Hi Afzal

    The knowledge article Creating Data Sources, Saved Qualifications and Reporting Templates in Confirm gives an overview of writing Data Sources and creating Report Templates which you may find useful.

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Pitney Bowes
    ------------------------------



  • 7.  RE: Enquiry attributes

    Moderator
    Posted 18 days ago
    Hi Afzal,

    Greetings!
    I hope you got the resolution of your query.
    Please mark the best answer by selecting "Make Best Answer" in the Reply menu.
    It will help us and other users save their time.

    For more information click here.

    Regards

    ------------------------------
    Aakash Singh
    Pitney Bowes Software India Pvt. Lt
    NOIDA
    ------------------------------