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

Report message to a moderator

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: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel

Report message to a moderator

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: 9092
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.

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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: 9092
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 07 01:49:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.