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.72151898734177215189873 4177215189 87342. 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'
Capture.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome, glad to assist!!
ASKER