Ho to avoid loading of data with null datetime

[Anonymous]

    Hi,

I am trying to load data with Casenum,Startdate(datetime),enddate(datetime), i want to drop the coulumns with null datetime while loading,  can anyone help me to solve this.

Best Regards,

Arjin.

AGMT_NUM	Start_date	End_Date
160429055		29Apr2016 14:07:19
160429055	29Apr2016 14:07:19
160429055	29Apr2016 14:07:51	29Apr2016 14:07:59
160429055	29Apr2016 14:07:59	09May2016 14:52:22
160429055		09May2016 15:45:06
160429055	09May2016 15:45:06	09May2016 16:36:22
160429055	09May2016 16:36:22	10May2016 17:53:49
160429055		10May2016 17:55:50
160429055	10May2016 17:55:50	10May2016 18:11:46
160429055	10May2016 18:11:46	10May2016 18:14:38
160429055	10May2016 18:14:38
160429055	11May2016 9:15:34	11May2016 10:28:28
160429055		11May2016 12:12:52
160429055	11May2016 12:12:52	11May2016 12:13:19
160429055		11May2016 12:14:45

[muthukumar77]

Hi,

Load

Casenum,

Startdate,

enddate

from

table

where len(trim(Startdate))<>0 and len(trim(enddate))<>0;

I hope it will be helpful.

[Gysbert_Wassenaar]

Perhaps like this:

MyTable:

LOAD

     AGMT_NUM,

     Start_date,

     End_Date

FROM

     ...source...

WHERE

     Len(Trim(Start_date))>0 AND Len(Trim(End_Date))>0

     ;

[Anonymous]

Hi,

Thanks for your reply, i am getting the below error when used ur suggestion

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE Len(Trim(STARTDT))<>0

[Anonymous]

Hi,

Thanks for your reply, i am getting the below error when used ur suggestion

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE Len(Trim(TV_CHECKED_1ST_DTTM))>0

[qlikview979]

Hi Arjin,

help full to you

T1:

load * Inline [

AGMT_NUM,  Start_date ,    End_Date

160429055 ,             ,29Apr2016 14:07:19

160429055 ,29Apr2016 14:07:19 ,

160429055, 29Apr2016 14:07:51, 29Apr2016 14:07:59

160429055, 29Apr2016 14:07:59, 09May2016 14:52:22

160429055,               ,09May2016 15:45:06,

160429055, 09May2016 15:45:06, 09May2016 16:36:22

160429055, 09May2016 16:36:22, 10May2016 17:53:49

160429055, 10May2016 17:55:50,

160429055, 10May2016 17:55:50 ,10May2016 18:11:46

160429055, 10May2016 18:11:46, 10May2016 18:14:38

160429055, 10May2016 18:14:38,

160429055, 11May2016 9:15:34, 11May2016 10:28:28

160429055,                ,11May2016 12:12:52,

160429055, 11May2016 12:12:52, 11May2016 12:13:19

160429055,               ,11May2016 12:14:45,

] Where len(Trim(Start_date))>0 and Len(Trim(End_Date))>0;

[Anonymous]

Hi Mahesh,

it works perfect with the sample data & script, bu t when i apply it in my data its giving error,

ERROR MESSAGE{SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'TRIM' keyword.

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE  len(Trim(START_DTTM))>0 and Len(Trim(END_DTTM))>0}

please advise if my script is wrong.

LEAPS_MASTER:

LOAD

CASE_NUM,

END_DTTM- START_DTTM as [TAT HRS],

if((END_DTTM- START_DTTM) <=   .1666666666,Dual('0 to 4 Hours',1),

if((END_DTTM- START_DTTM) >     .1666666666  AND (END_DTTM- START_DTTM) <=  .3333333333, Dual('4 to 8 Hours',2),

if((END_DTTM- START_DTTM) >     .3333333333  AND (END_DTTM- START_DTTM) <= .5000000000, Dual('8 to 12 Hours',3),

if((END_DTTM- START_DTTM) >     .5000000000  AND (END_DTTM- START_DTTM) <= 1.0000000000, Dual('12 to 24 Hours',4),

if((END_DTTM- START_DTTM) >     1.0000000000, Dual('>24 Hours',5)))))) as [TAT Bucket],

if((END_DTTM- START_DTTM)<= .3333333333,1,0) as [SLA BUCKET],

'Last Submit to First TV Checked' AS [CS OUTPUT],

'MLTV' AS TEAM,

SUBMIT_SALES_LEAPS_ID,

SUBMIT_SALES_GROUP,

'MLTV' & WeekEnd(END_DTTM) as KEY,

MakeTime(Hour(START_DTTM), Minute(START_DTTM), Second(START_DTTM)) as [TV ARRIVAL TIME],

HL_TL_IND,

PROPERTY_TYPE,

capitalize (TVO_NAME) as [TV OFFICER];

SQL SELECT *

FROM "BIP_EUC_IP_SA1"."ADM_LEAPS_CASE_HIST"

WHERE  len(Trim(START_DTTM))>0 and Len(Trim(END_DTTM))>0;;

[tamilarasu]

[antoniotiman]

Try

Select *

From ....

Where Not YouField is Null

[muthukumar77]

Hi,

You can use in SQL command,

.......

.......

......

Where STARTDT is NOT NULL;