We help IT Professionals succeed at work.
Get Started

What is the proper syntax when using TRUNC

69 Views
Last Modified: 2019-12-18
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
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE