Confirm

Expand all | Collapse all

Enquiry attributes

Jump to Best Answer
  • 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
    Best Answer

    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
    Best Answer

    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

    Posted 28 days ago
      |   view attached
    Hello Hafizur,

    I have been trying to understanding this, where does ea1 come from, does this come from attributes codes, for example from the "Operational Attribute Type" lookups

    We dont have have "central_enquiry cenq" instead in our database we have "central_enquiry.enquiry_number" and i am assuming it is the same thing, and when run it i get the attached error,

    SELECT
    central_enquiry.enquiry_number,
    ea1.param_date_value as date_attribute

    FROM
    central_enquiry.enquiry_number LEFT JOIN
    enquiry_attribute ea1 ON

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

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------



  • 7.  RE: Enquiry attributes

    Posted 28 days ago
    Hi Afzal,

    In the following extract of the SQL

    FROM
    central_enquiry cenq LEFT JOIN
    enquiry_attribute ea1

    I have specified cenq as an alias name for the central_enquiry table and ea1 as an alias for enquiry_attribute table. it could also have been written as follows.

    FROM
    central_enquiry AS cenq LEFT JOIN
    enquiry_attribute AS ea1

    But the AS is not required as SQL will recognise it as an alias.

    If you have multiple attributes you need to add in, as they are all in the enquiry_attribute table, it would be better to give it a unique alias name to ensure the correct data is selected from the appropriate table. Giving an alias name also acts as an abbreviation of the table name, so if you type your SQL code, it makes it quicker, as you don't have to type the full name every time you refer to a field.

    e.g.

    SELECT
    cenq.enquiry_number,
    ea1.param_date_value AS attribute1,
    ea2.param_date_value AS attribute2

    FROM

    central_enquiry AS cenq LEFT JOIN
    enquiry_attribute AS ea1 ON

    cenq.enquiry_number = ea1.enquiry_number AND
    ea1.param_type_code = 'AttributeCode1' LEFT JOIN
    enquiry_attribute AS ea2 ON

    cenq.enquiry_number = ea2.enquiry_number AND
    ea1.param_type_code = 'Attribute2' etc

    If you remove the alias name from the table in the FROM section, you will need to replace the instances of the aliases with the full table name in the whole SQL.

    If you have added an alias, you will need to ensure you use this when you add additional fields from the table and not the full table name.

    Hope this clarifies.

    Regards

    Haf

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



  • 8.  RE: Enquiry attributes

    Posted 28 days ago
    Thank you Hafizur

    i will give this a go

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    ------------------------------



  • 9.  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
    ------------------------------



  • 10.  RE: Enquiry attributes

    Moderator
    Posted 08-05-2019 01:29
    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
    ------------------------------