Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I use sql statement to import data from oracle database. I receive "ORA-01843: not a valid month" error when try to connect.
there is something wrong with date format but don't know how to fix it, any suggestions please?
SELECT
ARADMIN.HPD_HELP_DESK.INCIDENT_NUMBER,
ARADMIN.HPD_HELP_DESK.DESCRIPTION,
decode(ARADMIN.HPD_HELP_DESK.PRIORITY,0,'Critical',1,'High',2,'Medium',3,'Low' ) ,
ARADMIN.HPD_HELP_DESK.PRODUCT_NAME,
decode(ARADMIN.HPD_HELP_DESK.STATUS,0,'New',1,'Assigned',2,'In Progress',3,'Pending',4,'Resolved',5,'Closed',6,'Cancelled',7,'Rejected'),
( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE /( 60*60*24 ) ) ),
( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.LAST_RESOLVED_DATE /( 60*60*24 ) ) ),
ARADMIN.HPD_HELP_DESK.DETAILED_DECRIPTION,
ARADMIN.HPD_HELP_DESK.ASSIGNEE,
ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_1,
ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_2,
ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_3,
ARADMIN.HPD_HELP_DESK.CATEGORIZATION_TIER_1,
ARADMIN.HPD_HELP_DESK.CATEGORIZATION_TIER_2,
ARADMIN.CTM_PEOPLE.FULL_NAME,
ARADMIN.HPD_HELP_DESK.ASSIGNED_GROUP,
ARADMIN.HPD_HELP_DESK_RESOLUTION.DIF_DAY,
ARADMIN.HPD_HELP_DESK.Z1D_TEMPLATE_NAME
FROM
ARADMIN.HPD_HELP_DESK,
ARADMIN.CTM_PEOPLE,
ARADMIN.HPD_HELP_DESK_RESOLUTION
WHERE
( ARADMIN.HPD_HELP_DESK.CORPORATE_ID=ARADMIN.CTM_PEOPLE.CORPORATE_ID(+) )
AND ( ARADMIN.HPD_HELP_DESK.INCIDENT_NUMBER=ARADMIN.HPD_HELP_DESK_RESOLUTION.INCIDENT_NUMBER(+) )
AND
(
ARADMIN.HPD_HELP_DESK.ASSIGNED_GROUP IN ( 'IT Helpdesk','Technical Support' )
AND
( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.LAST_RESOLVED_DATE /( 60*60*24 ) ) ) >= '01-01-2020 00:00:00'
AND
decode(ARADMIN.HPD_HELP_DESK.STATUS,0,'New',1,'Assigned',2,'In Progress',3,'Pending',4,'Resolved',5,'Closed',6,'Cancelled',7,'Rejected') IN ( 'Closed','Resolved' )
)
Thanks,
Solved! Go to Solution.
You may be better off asking this in an Oracle SQL forum to help you with that syntax. T-SQL in SQL Server uses this format, but that is probably specific to their platform.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSELECT /*+ INDEX(POLICY_WORK I_AR_DT) */
"TIPS"."POLICY_WORK"."AGENT_ID" AGENT_ID,
SUM(NVL("TIPS"."POLICY_WORK"."ADJ_RATED_EXPSRE_AMT", 0)) RATED_EXPSRE_AMT,
SUM(NVL("TIPS"."POLICY_WORK"."ADJ_PREM_AMT",0)) FUND_PREM,
SUM(NVL(DECODE("TIPS"."POLICY_WORK"."PRMLGTD_AMT","TIPS"."POLICY_WORK"."ADJ_PRMLGTD_AMT",1,0),0)) COUNT
FROM "TIPS"."POLICY_WORK"
WHERE "TIPS"."POLICY_WORK"."AGENT_ID" = :a_agent_id AND
TO_DATE(TO_CHAR( "TIPS"."POLICY_WORK"."AR_DT",'MM/dd/yyyy') ,'MM/dd/yyyy') BETWEEN TO_DATE('01-01-'||TO_CHAR(TO_CHAR(TO_DATE(:a_start_date,'MM/dd/yyyy'),'YYYY')),'MM/dd/yyyy')AND
TO_DATE('01-01-'||TO_CHAR(TO_CHAR(tO_DATE(:a_end_date,'MM/dd/yyyy'),'YYYY')),'MM/dd/yyyy') AND
( "TIPS"."POLICY_WORK"."TRAN_ID" > 999 )
GROUP BY "TIPS"."POLICY_WORK"."AGENT_ID"
You may be better off asking this in an Oracle SQL forum to help you with that syntax. T-SQL in SQL Server uses this format, but that is probably specific to their platform.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting