Home » RDBMS Server » Server Utilities » Date function validation 'merged)
Date function validation 'merged) [message #353356] Mon, 13 October 2008 09:02 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good day,

My requirment like i need to filter leesthen sysdate values.

my data file values comes position 1 to 8 like (20080929)(YYYYMMDD) format.

TDRB19_EFFECTIVE_DATE_Y POSITION(1) CHAR(8) "to_date(:TDRB19_EFFECTIVE_DATE_Y,'yyyymmdd')"

example (TDRB19_EFFECTIVE_DATE_Y > SYSDATE)

LOAD DATA
REPLACE
INTO TABLE METDR02.CTDRB19_LLP_TL
(
TDRB19_PLANT_C 	POSITION(69) CHAR(5)  "NVL(RTRIM(LTRIM(:TDRB19_PLANT_C )),' ')",
TDRB19_SUPPLIER_C POSITION(74) CHAR(5)  "DECODE(LENGTH(RTRIM(LTRIM(:TDRB19_SUPPLIER_C))),5,:TDRB19_SUPPLIER_C,'INVALID')",
TDRB19_SHIP_DAYS_C POSITION(79) CHAR(7)  "NVL(RTRIM(LTRIM(:TDRB19_SHIP_DAYS_C )),'  ')",
TDRB19_ROUTE_TYPE_C  POSITION(19) CHAR(5),
TDRB19_ROUTE_NAME_N constant 1 ,
TDRB19_SEQNUMBER_R SEQUENCE(MAX,1),
TDRB19_DRAW_AREA_X   POSITION(40) CHAR(20) "NVL(RTRIM(LTRIM(:TDRB19_DRAW_AREA_X )),'  ')",
TDRB19_DOCK_C  	   POSITION(113) CHAR(5) "NVL(RTRIM(LTRIM(:TDRB19_DOCK_C)),' ')", 
TDRB19_CARRIER_C     POSITION(221) CHAR(4) "RTRIM(LTRIM(:TDRB19_CARRIER_C  ))", 
[COLOR=red]TDRB19_EFFECTIVE_DATE_Y  POSITION(1) CHAR(8) "to_date(:TDRB19_EFFECTIVE_DATE_Y,'yyyymmdd')",[/COLOR]
TDRB19_MODE_C        POSITION(14) CHAR(2)  "RTRIM(LTRIM(:TDRB19_MODE_C ))", 
TDRB19_PU_TIME_S     POSITION(93) CHAR(4),
TDRB19_RETENTION_C   POSITION(175) CHAR(1),
TDRB19_DEPT_TIME_S   POSITION(97) CHAR(4),
TDRB19_UNLOAD_TYPE_C POSITION(212) CHAR(4),
TDRB19_BROKER_SCAC_C POSITION(221) CHAR(4),
TDRB19_RACK_RET_RATIO_P  POSITION(237) CHAR(7) "NVL(RTRIM(LTRIM(:TDRB19_RACK_RET_RATIO_P)),'0')", 
TDRB19_ALT_DEST_C    POSITION(247) CHAR(7),
TDRB24_EXCEPTION_C constant 0,
TDRB19_TD504_MODE_C   POSITION(14) CHAR(2) "RTRIM(LTRIM(:TDRB19_TD504_MODE_C))"	,
TDRB19_MINWEIGHT_Q POSITION(259) Char(5), 
TDRB19_RTEUPDFLAG_F POSITION(265) CHAR(5) "DECODE(RTRIM(LTRIM(:TDRB19_RTEUPDFLAG_F)),'IO','I',:TDRB19_RTEUPDFLAG_F)",
TDRB19_ALT_ORIG_C    POSITION(270) CHAR(5),
TDRB19_CONV_SIZE_C POSITION(254) CHAR(3) "DECODE(NVL(RTRIM(LTRIM(:TDRB19_CONV_SIZE_C)), ' '),'48','48W','53','53W',:TDRB19_CONV_SIZE_C)",
TDRB19_NEW_SI_F  CONSTANT 'Y',
TDRB19_TIMEZONE_C  CONSTANT 'E',
tdrb19_llp_source_c CONSTANT 'U',
TDRB19_LAST_UPDATE_S     SYSDATE 
)



Let me know if you need any more details.

Thanks & Regards
Thangam.

[EDITED by LF: disabled smilies in this post]

[Updated on: Mon, 13 October 2008 14:15] by Moderator

Report message to a moderator

Re: Date function validation [message #353370 is a reply to message #353356] Mon, 13 October 2008 11:29 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

It means (TDRB19_EFFECTIVE_DATE_Y > SYSDATE) Just we need to allow only Grater then SYSDATE.

My dat file values comes like(20081013,'YYYYMMDD')

Could you please give me someone tips on this.


Thanks & Regards
Thangam
Re: Check date format [message #353379 is a reply to message #353356] Mon, 13 October 2008 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel
Re: Date function validation [message #353418 is a reply to message #353370] Mon, 13 October 2008 16:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You will need to use either SQL*Loader or external tables to load your data into a staging table, then insert just the rows you want into your target table using SQL. Or you could load all of them, then delete the ones you don't want.

Re: Date function validation [message #353582 is a reply to message #353418] Tue, 14 October 2008 04:32 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

using SQL loader.

CTL file
LOAD DATA
REPLACE
INTO TABLE METDR02.CTDRB19_LLP_TL
(
TDRB19_PLANT_C 	POSITION(69) CHAR(5)  "NVL(RTRIM(LTRIM(:TDRB19_PLANT_C )),' ')",
TDRB19_SUPPLIER_C POSITION(74) CHAR(5)  "DECODE(LENGTH(RTRIM(LTRIM(:TDRB19_SUPPLIER_C))),5,:TDRB19_SUPPLIER_C,'INVALID GSDB CODE'",
TDRB19_SHIP_DAYS_C POSITION(79) CHAR(7)  "NVL(RTRIM(LTRIM(:TDRB19_SHIP_DAYS_C )),'  ')",
TDRB19_ROUTE_TYPE_C  POSITION(19) CHAR(5),
TDRB19_ROUTE_NAME_N constant 1 ,
TDRB19_SEQNUMBER_R SEQUENCE(MAX,1),
TDRB19_DRAW_AREA_X   POSITION(40) CHAR(20) "NVL(RTRIM(LTRIM(:TDRB19_DRAW_AREA_X )),'  ')",
TDRB19_DOCK_C  	   POSITION(113) CHAR(5) "NVL(RTRIM(LTRIM(:TDRB19_DOCK_C)),' ')", 
TDRB19_CARRIER_C     POSITION(221) CHAR(4) "RTRIM(LTRIM(:TDRB19_CARRIER_C  ))", 
TDRB19_EFFECTIVE_DATE_Y  POSITION(1) CHAR(8) "to_date(:TDRB19_EFFECTIVE_DATE_Y,'yyyymmdd')",
TDRB19_MODE_C        POSITION(14) CHAR(2)  "RTRIM(LTRIM(:TDRB19_MODE_C ))", 
TDRB19_PU_TIME_S     POSITION(93) CHAR(4),
TDRB19_RETENTION_C   POSITION(175) CHAR(1),
TDRB19_DEPT_TIME_S   POSITION(97) CHAR(4),
TDRB19_UNLOAD_TYPE_C POSITION(212) CHAR(4),
TDRB19_BROKER_SCAC_C POSITION(221) CHAR(4),
TDRB19_RACK_RET_RATIO_P  POSITION(237) CHAR(7) "NVL(RTRIM(LTRIM(:TDRB19_RACK_RET_RATIO_P)),'0')", 
TDRB19_ALT_DEST_C    POSITION(247) CHAR(7),
TDRB24_EXCEPTION_C constant 0,
TDRB19_TD504_MODE_C   POSITION(14) CHAR(2) "RTRIM(LTRIM(:TDRB19_TD504_MODE_C))"	,
TDRB19_MINWEIGHT_Q POSITION(259) Char(5), 
TDRB19_RTEUPDFLAG_F POSITION(265) CHAR(5) "DECODE(RTRIM(LTRIM(:TDRB19_RTEUPDFLAG_F)),'IO','I',:TDRB19_RTEUPDFLAG_F)",
TDRB19_ALT_ORIG_C    POSITION(270) CHAR(5),
TDRB19_CONV_SIZE_C POSITION(254) CHAR(3) "DECODE(NVL(RTRIM(LTRIM(:TDRB19_CONV_SIZE_C)), ' '),'48','48W','53','53W',:TDRB19_CONV_SIZE_C)",
TDRB19_NEW_SI_F  CONSTANT 'Y',
TDRB19_TIMEZONE_C  CONSTANT 'E',
tdrb19_llp_source_c CONSTANT 'U',
TDRB19_LAST_UPDATE_S     SYSDATE 
)


Data file
2008092923   O    SUPDC                                    20080929 AP02AD1N6AW                                 OW                                                                                                 DROP DON UNPC                      CE4BR       0     I    DFBEA


While exceuting the ctl file using this data file, the dat going to updating the DB.

Incase the DATA file contains position 1 to 8 the date lessthen sysdate, it should not allowing to insert my table.


Let me know if you need any more details.

Thanks & Regards
Thangam
Re: Date function validation 'merged) [message #353628 is a reply to message #353356] Tue, 14 October 2008 07:02 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Could you please anyone can help me on this.

Thanks & Regards
Thangam
Re: Date function validation 'merged) [message #353719 is a reply to message #353628] Tue, 14 October 2008 17:58 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Your question has already been answered. You cannot do what you want using SQL*Loader alone. SQL*Loader WHEN clauses can only validate equal or not equal. It cannot validate LESS THAN. As previously stated, just load your data into a staging table, then use SQL:

INSERT INTO target_table
SELECT * FROM staging_table
WHERE date_column >= SYSDATE;

to insert your data from your staging table to your target table. Stop repeating your question waiting for a solution that doesn't exist.

[Updated on: Tue, 14 October 2008 18:00]

Report message to a moderator

Previous Topic: Data Pump Backup
Next Topic: Tricky Loading of file!!
Goto Forum:
  


Current Time: Tue Dec 06 12:31:13 CST 2016

Total time taken to generate the page: 0.07474 seconds