Home » RDBMS Server » Server Utilities » SQL Function Greatest (Oracle 11g)
SQL Function Greatest [message #481057] Fri, 29 October 2010 15:12 Go to next message
lachu23
Messages: 7
Registered: June 2010
Junior Member
I have a control file like below where am trying to convert dimensions which are less than .01 to roundup as .01. But the table is loaded with 0 instead of .01. Please help. I have given the sample data below.


LOAD DATA
INFILE * 
INTO TABLE TMPTPC_PRT_CTLG
TRUNCATE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ITM_NO, 
EPR_DT DATE "YYYY-MM-DD", 
UPC_CD, 
INCL_QTY, 
CORE_VAL_FL, 
CORE_VAL_AMT, 
ITM_DS, 
ITM_LGTH "greatest(:ITM_LGTH,.01)", 
ITM_WDTH "greatest(:ITM_WDTH, .01)", 
ITM_HGHT "greatest(:ITM_HGHT,.01)", 
ITM_WGHT "greatest(:ITM_WGHT,.01)", 
SH_LGTH "greatest(:SH_LGTH,.01)", 
SH_WDTH "greatest(:SH_WDTH,.01)", 
SH_HGHT "greatest(:SH_HGHT,.01)", 
SH_WGHT "greatest(:SH_WGHT,.01)",
NBR_PER_CRTN, 
NBR_CRTN_PER_PLLT, 
STD_SH_TYP,
UNIT_PER_OTBND_SH,
TRK_MTHD,
AUTO_QC_FL "decode(:AUTO_QC_FL,'Y','Y','N','N',' ','N','N')",
ALT_UOM, 
VELOCITY_CD,
FRT_CLS_GRND,
FRT_CLS_AIR,
RCPT_QTY_LMT,
NO_CST_FL "decode(:NO_CST_FL,'Y','Y','N','N',' ','N','N')",
ITM_CAT_NO,
ITM_SUB_CAT_NO1,
ITM_SUB_CAT_NO2,
RCL_FL "decode(:RCL_FL,'0','N','1','Y',' ','N','N')")


00913941A1930||||N|0.0|Chaincable|0.00001|0.00001|0.00001|0.00001|0.00001|0.00001|0.00001|0.00001||||||N||||||N|1|||N
Re: SQL Function Greatest [message #481059 is a reply to message #481057] Fri, 29 October 2010 16:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
It is comparing the values as characters. You need to convert them to numbers first, then compare them, like so:

"greatest(to_number(:column_name),.01)"
Re: SQL Function Greatest [message #481060 is a reply to message #481059] Fri, 29 October 2010 16:15 Go to previous message
lachu23
Messages: 7
Registered: June 2010
Junior Member
You are correct! I had to convert into a number. But it is a number field in the database as ITM_LGTH NUMBER(12,2). That's the reason I didnt try to convert into a number.
Previous Topic: Upgrade 8i to 10g
Next Topic: Loading data into tables
Goto Forum:
  


Current Time: Fri Sep 17 07:24:52 CDT 2021