What is the proper syntax when using TRUNC

maximus1974
maximus1974 used Ask the Experts™
on
When using TRUNC, my results are 0. When I remove it, my results are 37.72151898734177215189873417721518987342. What is the proper syntax to truncate and display 37?

SELECT
    wo_operation.si_number          wo_number,
    wo_operation.wo_type            wo_type,
    wo_operation.woo_auto_key,
    companies.company_name          customer,
    companies.company_code          customer_code,
    parts_master.pn                 part_number,
    parts_master.description,
    wo_quote_header.wqh_number      wq_number,
    wo_quote_header.quote_version   wq_version,
    wo_quote_header.entry_date      quote_date,
    wo_quote_status.description     status,
    qc_wo_pkg2.get_wqh_total_price(wo_quote_header.wqh_auto_key) quote_amount,
    wo_quote_header.sent_date       sent_date,
    wo_quote_header.approved_date   approved_date,
    wo_quote_header.syscm_auto_key,
    wo_quote_detail.woo_ref,
    wo_quote_detail.inh_auto_key,
    wo_quote_detail.sequence        wq_sequence,
    wos.description                 wo_status,
    decode(companies.cv_udf_002, 'T', 'Y', 'N') pbh_customer,
    dpt.dept_name                   shop,
    wo_operation.open_flag,
    sysur.first_name
    || ' '
    || sysur.last_name quoting_agent,
    parts_master.list_price,
    parts_master.pnm_auto_key,
    wo_quote_detail.bgs_auto_key,
    nvl((
        SELECT
            SUM(wqd2.qty * wqd2.base_cost)
        FROM
            wo_quote_detail wqd2
        WHERE
            wqd2.wqh_auto_key = wo_quote_header.wqh_auto_key
            AND wqd2.item_type = 'Part'
    ), 0) parts_cost_estimate,
    nvl((
        SELECT
            SUM(wqd2.qty)
        FROM
            wo_quote_detail wqd2
        WHERE
            wqd2.wqh_auto_key = wo_quote_header.wqh_auto_key
            AND wqd2.item_type = 'Labor'
    ), 0) labor_hours_estimate,
    nvl((
        SELECT
            SUM(wqd2.qty * wqd2.base_cost)
        FROM
            wo_quote_detail wqd2
        WHERE
            wqd2.wqh_auto_key = wo_quote_header.wqh_auto_key
            AND wqd2.item_type = 'Labor'
    ), 0) labor_cost_estimate,
    nvl((
        SELECT
            SUM(wqd2.qty * wqd2.base_cost)
        FROM
            wo_quote_detail wqd2
        WHERE
            wqd2.wqh_auto_key = wo_quote_header.wqh_auto_key
            AND wqd2.item_type = 'Repair'
    ), 0) external_cost_estimate,
    nvl((
        SELECT
            SUM(wqd2.qty * wqd2.base_cost)
        FROM
            wo_quote_detail wqd2
        WHERE
            wqd2.wqh_auto_key = wo_quote_header.wqh_auto_key
            AND wqd2.item_type = 'Charge'
    ), 0) charge_cost,
    wo_quote_header.wqh_auto_key,
    wo_operation.wo_disp,
   [b] (qc_wo_pkg2.get_wqh_total_price(wo_quote_header.wqh_auto_key) / ( parts_master.ic_udf_002 ) ) * 100 fmv,[/b]
    TRUNC(parts_master.ic_udf_002) fmv_pn,
    work_flows.code SERVICE_PERF_QUOTED,
    wo_quote_detail.wqd_auto_key
FROM
    wo_quote_detail
    INNER JOIN wo_operation ON wo_quote_detail.woo_ref = wo_operation.woo_auto_key
    INNER JOIN wo_quote_header ON wo_quote_detail.wqh_auto_key = wo_quote_header.wqh_auto_key
    INNER JOIN wo_quote_status ON wo_quote_header.wqs_auto_key = wo_quote_status.wqs_auto_key
    INNER JOIN parts_master ON wo_operation.pnm_auto_key = parts_master.pnm_auto_key
    LEFT JOIN companies ON wo_quote_header.cmp_auto_key = companies.cmp_auto_key
    LEFT JOIN wo_status    wos ON wo_operation.wos_auto_key = wos.wos_auto_key
    LEFT JOIN department   dpt ON wo_operation.dpt_auto_key = dpt.dpt_auto_key
    LEFT JOIN sys_users    sysur ON wo_quote_header.sysur_auto_key = sysur.sysur_auto_key
    LEFT JOIN qctl.work_flows ON wo_operation.wfw_auto_key = qctl.work_flows.wfw_auto_key
WHERE
    wo_quote_header.record_type = 'Q' AND wo_quote_header.wqh_number = 'WQA1006787'

Open in new window

Capture.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Seems you missed using TRUNC function for FMV column..
Kindly use this instead of the current value..
TRUNC((qc_wo_pkg2.get_wqh_total_price(wo_quote_header.wqh_auto_key) / ( parts_master.ic_udf_002 ) ) * 100) fmv,

Open in new window

Author

Commented:
Thank you
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial