How to resolve UDA-SQL-0219 The function "date_trunc" error

[yjmoon56]

Hi All,

My report data has parent-child structure.

The parent data is for insurance premium and its child records are for endorsement reasons. Their relation is 1:m.

I have tried to aggregate the parent level premium with condition that doesn't have the dendorsement reason 'COVID-19'.

Let me show the example.

Parent records
--------------------------------------------------------
Ins_No.  |   Premium   |   Quoted Date(YYYY-MM)
--------------------------------------------------------
  P1        |   1,500       |   2021-04
  P2        |   1,000       |   2021-05
  P3        |     500        |   2021-05
===============================

Child Reocrd
---------------------------------------------------------------
Ins_No.  |   Reason Code  |   Reason
---------------------------------------------------------------
  P1       |   R1                 |  Change to Expiry Date
  P1       |   R2                 |  Change to Policy Clauses
  P2       |   R1                 |  Change to Expiry Date
  P3       |   R3                 |  COVID-19
===================================

Should have the result report
--------------------------------------------------------------------------------
Ins_No.  |   Premium   |   Quoted Date(YYYY-MM)    |   Reason Count
--------------------------------------------------------------------------------
  P1        |  1,500        |  2021-04                           |   2
  P2        |  1,000        |  2021-05                           |   1
--------------------------------------------------------------------------------
                 2,500
=============================================


I developed a query for child records for using 'Join' from Toolbox;
Child Reocrd without 'COVID-19' reason
---------------------------------------------------------------
Ins_No.  |   Reason Count(using total functionality)
---------------------------------------------------------------
  P1       |   2
  P2       |   1
===================================

And I added 'Join' functionality. But UDA-SQL-0219 "date_trunc" error occured.

How can I resolve this?

Shoul I change the way to result? or Is there any way to escape from UDA-SQL-0219 "date_trunc" error?

Please help me.

[MFGF]

Hi All,

My report data has parent-child structure.

The parent data is for insurance premium and its child records are for endorsement reasons. Their relation is 1:m.

I have tried to aggregate the parent level premium with condition that doesn't have the dendorsement reason 'COVID-19'.

Let me show the example.

Parent records
--------------------------------------------------------
Ins_No.  |   Premium   |   Quoted Date(YYYY-MM)
--------------------------------------------------------
  P1        |   1,500       |   2021-04
  P2        |   1,000       |   2021-05
  P3        |     500        |   2021-05
===============================

Child Reocrd
---------------------------------------------------------------
Ins_No.  |   Reason Code  |   Reason
---------------------------------------------------------------
  P1       |   R1                 |  Change to Expiry Date
  P1       |   R2                 |  Change to Policy Clauses
  P2       |   R1                 |  Change to Expiry Date
  P3       |   R3                 |  COVID-19
===================================

Should have the result report
--------------------------------------------------------------------------------
Ins_No.  |   Premium   |   Quoted Date(YYYY-MM)    |   Reason Count
--------------------------------------------------------------------------------
  P1        |  1,500        |  2021-04                           |   2
  P2        |  1,000        |  2021-05                           |   1
--------------------------------------------------------------------------------
                 2,500
=============================================


I developed a query for child records for using 'Join' from Toolbox;
Child Reocrd without 'COVID-19' reason
---------------------------------------------------------------
Ins_No.  |   Reason Count(using total functionality)
---------------------------------------------------------------
  P1       |   2
  P2       |   1
===================================

And I added 'Join' functionality. But UDA-SQL-0219 "date_trunc" error occured.

How can I resolve this?

Shoul I change the way to result? or Is there any way to escape from UDA-SQL-0219 "date_trunc" error?

Please help me.

Hi,

What are the data types of the items in each query? You show an item called "Quoted Date" in your post, but it doesn't looks like a complete date? Is this just the way you are formatting it, and it really is a date, or is it a string item?

Are you using any functions or expressions in your report - particularly for the Quoted Date item?

What is the exact detailed error message you see?

Cheers!

MF.

[yjmoon56]

Hi MF,

I am sorry I can't show you the full error message because I found another way to go to the goal.

And since the report has already been modified, it has not shown the error message.

Thank you for your answer.

Young