Link to home
Start Free TrialLog in
Avatar of maximus1974
maximus1974

asked on

What is the proper syntax when using TRUNC

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
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maximus1974
maximus1974

ASKER

Thank you
Welcome, glad to assist!!