Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi,
Load
Casenum,
Startdate,
enddate
from
table
where len(trim(Startdate))<>0 and len(trim(enddate))<>0;
I hope it will be helpful.
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
;
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
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
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;
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;;
Try
Select *
From ....
Where Not YouField is Null
Hi,
You can use in SQL command,
.......
.......
......
Where STARTDT is NOT NULL;