troubleshooting Question

How I can prevent the results of a column from adding two zeroes to the end of the value?

Avatar of maximus1974
maximus1974 asked on
Oracle DatabaseSQL* oracle syntax
4 Comments2 Solutions100 ViewsLast Modified:
How I can prevent the results of DAYS_LEFT from adding two zeroes to the end of the value(screenshot attached)? here is the statement I am using:
With N1 As (
SELECT 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
  BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, 
  DEPARTMENT.DEPT_NAME, 
  BA_VIEW_SHIPPING_ORDERS.PN, 
  BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, 
  TRUNC(CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - BA_VIEW_WO_SUMMARY.NET_TAT) AS WO_DAYS_LEFT, 
  TRUNC(BA_VIEW_SHIPPING_ORDERS.DUE_DATE - SYSDATE) AS SO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'RO' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE 0 END) AS RO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'WHS' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE 0 END) AS WHS_DAYS_LEFT
FROM 
  BA_VIEW_SHIPPING_ORDERS 
  LEFT OUTER JOIN BA_VIEW_WO_SUMMARY ON BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = BA_VIEW_WO_SUMMARY.WO_NUMBER 
  LEFT OUTER JOIN DEPARTMENT ON BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = DEPARTMENT.DPT_AUTO_KEY 
WHERE 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%' 
  AND BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED')
  )
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||RO_DAYS_LEFT||WHS_DAYS_LEFT) Days_Left
From N1
screenshot
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros