#1 2020-06-18 07:36:14

kzain89
Member
From: Kenya
Registered: 2020-06-18

sqldataexception: ora-01861: literal does not match format string

I get the above error while trying to create a pivot table, the dynamic list works and i am able to populate data, however the same query gives me the above error when converted to Pivot.
query is as below


select
  y.*,
  (y.Sale_Value - y.Cost_Value) Margin_Value,
  decode(y.Sale_Value,0,0,
    round((y.Sale_Value - y.Cost_Value) / y.Sale_Value * 100,1)) Margin_Prcnt
from
(
select
  x.*,
  nvl(x.ACTUAL_COST,x.STD_Cost) Unit_Cost,
  round(x.Prim_Qty *
    decode(x.STD_Cost,0,nvl(x.ACTUAL_COST,0),x.STD_Cost),2) Cost_Value,
  round(x.Tran_Qty * nvl(x.SO_Price,0) * x.Exch_Rate,2) Sale_Value,
  case when x.Tran_Qty < 0 then 2 else 1 end srt
from
(
select
   so.OU,msi.SEGMENT1 Item_Code,msi.DESCRIPTION Item_Desc,msi.PRIMARY_UOM_CODE Uom,
   mmt.Tran_Qty,mmt.Prim_Qty,
   GMF_CMCOMMON.get_cmpt_cost(mmt.INVENTORY_ITEM_ID,109,$P{ToDate},1002,0) STD_Cost,
   mmt.ACTUAL_COST,mmt.TRANSACTION_DATE,
   decode(sub.ASSET_INVENTORY,1,'Yes',2,'No','-') subinv_costed,
   msi.COSTING_ENABLED_FLAG item_costed,
   decode(mst.TRANSACTION_SOURCE_TYPE_NAME,
     'Sales order',so.ORDER_NUMBER,
     'RMA',so.ORDER_NUMBER
     ) Tran_Ref,
   so.SO_Price,so.SO_Curr,
   decode(so.SO_Curr,'KES',1,nvl(GL_CURRENCY_API.get_rate(so.SO_Curr,'KES',mmt.TRANSACTION_DATE,'Corporate'),1)) Exch_Rate,
   so.CUSTOMER_NAME,so.Salesrep, cat.SEGMENT1 DIVISION, cat.SEGMENT2 PRODUCT, cat.SEGMENT3 SUBPRODUCT, cat.SEGMENT4 ANALYSIS, cat.SEGMENT5 BRAND, cat.SEGMENT6 SOURCE_
from
   (select ORGANIZATION_ID,INVENTORY_ITEM_ID,TRANSACTION_SOURCE_TYPE_ID,TRX_SOURCE_LINE_ID,
      SUBINVENTORY_CODE,ACTUAL_COST,TRANSACTION_DATE,
      sum(round(TRANSACTION_QUANTITY*-1,4)) Tran_Qty,
      sum(round(PRIMARY_QUANTITY*-1,4)) Prim_Qty
    from MTL_MATERIAL_TRANSACTIONS
    where (LOGICAL_TRANSACTION = 2 or LOGICAL_TRANSACTION is null)
      and TRANSACTION_ACTION_ID not in (24,30)
      and TRANSACTION_TYPE_ID in (33,15)
      and trunc(TRANSACTION_DATE) between  $P{Fr_Date} and $P{ToDate}
      and TRANSACTION_DATE >= '01-JUL-2016'
    group by ORGANIZATION_ID,INVENTORY_ITEM_ID,TRANSACTION_SOURCE_TYPE_ID,TRX_SOURCE_LINE_ID,
      SUBINVENTORY_CODE,ACTUAL_COST,TRANSACTION_DATE
   ) mmt
  left join MTL_SYSTEM_ITEMS_B msi
     on mmt.ORGANIZATION_ID = msi.ORGANIZATION_ID and mmt.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
  left join XXDOSHI_ITEM_CATEGORY_V cat ON cat.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID AND cat.organization_id = msi.ORGANIZATION_ID
   left join MTL_PARAMETERS mp
     on mmt.ORGANIZATION_ID = mp.ORGANIZATION_ID
   left join MTL_TXN_SOURCE_TYPES mst
     on mmt.TRANSACTION_SOURCE_TYPE_ID = mst.TRANSACTION_SOURCE_TYPE_ID
   left join MTL_SECONDARY_INVENTORIES sub
     on mmt.SUBINVENTORY_CODE = sub.SECONDARY_INVENTORY_NAME
   left join
     (select distinct c.HEADER_ID SO_Hdr_ID,c.LINE_ID SO_Ln_ID,d.ORDER_NUMBER,
        d.TRANSACTIONAL_CURR_CODE SO_Curr,c.UNIT_SELLING_PRICE SO_Price,
        e.SALESREP_NUMBER Salesrep,f.CUSTOMER_NAME,g.SHORT_CODE OU
      from OE_ORDER_LINES_ALL c
        left join OE_ORDER_HEADERS_ALL d on c.HEADER_ID = d.HEADER_ID
        left join JTF_RS_SALESREPS e on d.SALESREP_ID = e.SALESREP_ID and d.ORG_ID = e.ORG_ID
        left join AR_CUSTOMERS f on d.SOLD_TO_ORG_ID = f.CUSTOMER_ID
       left join HR_OPERATING_UNITS g on d.ORG_ID = g.ORGANIZATION_ID
     ) so
     on mmt.TRX_SOURCE_LINE_ID = so.SO_Ln_ID and mst.TRANSACTION_SOURCE_TYPE_NAME in ('Sales order','RMA')
where 1=1
   and so.OU in ('A5A','A5B','A5C')
) x
where x.subinv_costed in ('Yes','-') and x.item_costed = 'Y'
) y
order by OU,srt,Item_Code,TRANSACTION_DATE,Tran_Ref

Last edited by kzain89 (2020-06-18 07:43:48)

Offline

#2 2020-06-18 08:13:40

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi kzain89,

can you please post the exact reportserver version, the complete error stack (found in the tomcat logs), the exact oracle version and which drivers are you using for connecting to oracle?

Regards,
Eduardo

Offline

#3 2020-06-18 11:21:50

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

1. RS3.1.2-6022 (2020-04-01-15-17-05)
2. Oracle: odbc7.jar
3. Oracle Databases version 11.2.0.3.0

4.SELECT * FROM (select
  y.*,
  (y.Sale_Value - y.Cost_Value) Margin_Value,
  decode(y.Sale_Value,0,0,
    round((y.Sale_Value - y.Cost_Value) / y.Sale_Value * 100,1)) Margin_Prcnt
from
(
select
  x.*,
  nvl(x.ACTUAL_COST,x.STD_Cost) Unit_Cost,
  round(x.Prim_Qty *
    decode(x.STD_Cost,0,nvl(x.ACTUAL_COST,0),x.STD_Cost),2) Cost_Value,
  round(x.Tran_Qty * nvl(x.SO_Price,0) * x.Exch_Rate,2) Sale_Value,
  case when x.Tran_Qty < 0 then 2 else 1 end srt
from
(
select
   so.OU,msi.SEGMENT1 Item_Code,msi.DESCRIPTION Item_Desc,msi.PRIMARY_UOM_CODE Uom,
   mmt.Tran_Qty,mmt.Prim_Qty,
   GMF_CMCOMMON.get_cmpt_cost(mmt.INVENTORY_ITEM_ID,109,'2017-06-17',1002,0) STD_Cost,
   mmt.ACTUAL_COST,mmt.TRANSACTION_DATE,
   decode(sub.ASSET_INVENTORY,1,'Yes',2,'No','-') subinv_costed,
   msi.COSTING_ENABLED_FLAG item_costed,
   decode(mst.TRANSACTION_SOURCE_TYPE_NAME,
     'Sales order',so.ORDER_NUMBER,
     'RMA',so.ORDER_NUMBER
     ) Tran_Ref,
   so.SO_Price,so.SO_Curr,
   decode(so.SO_Curr,'KES',1,nvl(GL_CURRENCY_API.get_rate(so.SO_Curr,'KES',mmt.TRANSACTION_DATE,'Corporate'),1)) Exch_Rate,
   so.CUSTOMER_NAME,so.Salesrep, cat.SEGMENT1 DIVISION, cat.SEGMENT2 PRODUCT, cat.SEGMENT3 SUBPRODUCT, cat.SEGMENT4 ANALYSIS, cat.SEGMENT5 BRAND, cat.SEGMENT6 SOURCE_
from
   (select ORGANIZATION_ID,INVENTORY_ITEM_ID,TRANSACTION_SOURCE_TYPE_ID,TRX_SOURCE_LINE_ID,
      SUBINVENTORY_CODE,ACTUAL_COST,TRANSACTION_DATE,
      sum(round(TRANSACTION_QUANTITY*-1,4)) Tran_Qty,
      sum(round(PRIMARY_QUANTITY*-1,4)) Prim_Qty
    from MTL_MATERIAL_TRANSACTIONS
    where (LOGICAL_TRANSACTION = 2 or LOGICAL_TRANSACTION is null)
      and TRANSACTION_ACTION_ID not in (24,30)
      and TRANSACTION_TYPE_ID in (33,15)
      and trunc(TRANSACTION_DATE) between  '2017-06-17' and '2017-06-17'
      and TRANSACTION_DATE >= '01-JUL-2016'
    group by ORGANIZATION_ID,INVENTORY_ITEM_ID,TRANSACTION_SOURCE_TYPE_ID,TRX_SOURCE_LINE_ID,
      SUBINVENTORY_CODE,ACTUAL_COST,TRANSACTION_DATE
   ) mmt
  left join MTL_SYSTEM_ITEMS_B msi
     on mmt.ORGANIZATION_ID = msi.ORGANIZATION_ID and mmt.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
  left join XXDOSHI_ITEM_CATEGORY_V cat ON cat.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID AND cat.organization_id = msi.ORGANIZATION_ID
   left join MTL_PARAMETERS mp
     on mmt.ORGANIZATION_ID = mp.ORGANIZATION_ID
   left join MTL_TXN_SOURCE_TYPES mst
     on mmt.TRANSACTION_SOURCE_TYPE_ID = mst.TRANSACTION_SOURCE_TYPE_ID
   left join MTL_SECONDARY_INVENTORIES sub
     on mmt.SUBINVENTORY_CODE = sub.SECONDARY_INVENTORY_NAME
   left join
     (select distinct c.HEADER_ID SO_Hdr_ID,c.LINE_ID SO_Ln_ID,d.ORDER_NUMBER,
        d.TRANSACTIONAL_CURR_CODE SO_Curr,c.UNIT_SELLING_PRICE SO_Price,
        e.SALESREP_NUMBER Salesrep,f.CUSTOMER_NAME,g.SHORT_CODE OU
      from OE_ORDER_LINES_ALL c
        left join OE_ORDER_HEADERS_ALL d on c.HEADER_ID = d.HEADER_ID
        left join JTF_RS_SALESREPS e on d.SALESREP_ID = e.SALESREP_ID and d.ORG_ID = e.ORG_ID
        left join AR_CUSTOMERS f on d.SOLD_TO_ORG_ID = f.CUSTOMER_ID
       left join HR_OPERATING_UNITS g on d.ORG_ID = g.ORGANIZATION_ID
     ) so
     on mmt.TRX_SOURCE_LINE_ID = so.SO_Ln_ID and mst.TRANSACTION_SOURCE_TYPE_NAME in ('Sales order','RMA')
where 1=1
   and so.OU in ('A5A','A5B','A5C')
) x
where x.subinv_costed in ('Yes','-') and x.item_costed = 'Y'
) y
order by OU,srt,Item_Code,TRANSACTION_DATE,Tran_Ref
) wrappedQry
) "GenericDynaListCube" group by "GenericDynaListCube"."SALESREP" order by "GenericDynaListCube"."SALESREP" ASC NULLS LAST]
        at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:992)
        at mondrian.olap.Util.newInternal(Util.java:2543)
        at mondrian.olap.Util.newError(Util.java:2559)
        at mondrian.rolap.SqlStatement.handle(SqlStatement.java:363)
        at mondrian.rolap.SqlStatement.execute(SqlStatement.java:262)
        at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:346)
        at mondrian.rolap.SqlTupleReader.prepareTuples(SqlTupleReader.java:498)
        at mondrian.rolap.SqlTupleReader.readMembers(SqlTupleReader.java:635)
        at mondrian.rolap.SqlMemberSource.getMembersInLevel(SqlMemberSource.java:533)
        at mondrian.rolap.SmartMemberReader.getMembersInLevel(SmartMemberReader.java:132)
        at mondrian.rolap.RolapSchemaReader.getLevelMembers(RolapSchemaReader.java:528)
        at mondrian.rolap.RolapSchemaReader.getLevelMembers(RolapSchemaReader.java:512)
        at mondrian.rolap.RolapCube$RolapCubeSchemaReader.getLevelMembers(RolapCube.java:619)
        at mondrian.olap.DelegatingSchemaReader.getLevelMembers(DelegatingSchemaReader.java:185)
        at mondrian.olap.Query$QuerySchemaReader.getLevelMembers(Query.java:1481)
        at mondrian.olap.fun.FunUtil.getNonEmptyLevelMembers(FunUtil.java:2170)
        at mondrian.olap.fun.FunUtil.levelMembers(FunUtil.java:2178)
        at mondrian.olap.fun.LevelMembersFunDef$1.evaluateList(LevelMembersFunDef.java:37)
        at mondrian.calc.impl.AbstractListCalc.evaluate(AbstractListCalc.java:65)
        at mondrian.rolap.RolapResult.evaluateExp(RolapResult.java:958)
        at mondrian.rolap.RolapNamedSetEvaluator.ensureList(RolapNamedSetEvaluator.java:88)
        at mondrian.rolap.RolapNamedSetEvaluator.evaluateTupleIterable(RolapNamedSetEvaluator.java:61)
        at mondrian.mdx.NamedSetExpr$1.evaluateIterable(NamedSetExpr.java:98)
        at mondrian.rolap.RolapResult.executeAxis(RolapResult.java:856)
        at mondrian.rolap.RolapResult.evalLoad(RolapResult.java:693)
        at mondrian.rolap.RolapResult.loadMembers(RolapResult.java:649)
        at mondrian.rolap.RolapResult.<init>(RolapResult.java:279)
        at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:500)
        ... 7 more
    Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at mondrian.rolap.SqlStatement.execute(SqlStatement.java:202)
        ... 30 more

Offline

#4 2020-06-18 11:30:14

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi kzain89,

thanks for the data.

Your query is quite complex. Could you please try to gradually delete elements from the query such that eventually the error disappears ? In such way you can identify the element that causes the problem and we can reproduce the problem.

The idea would be to make the query as simple as possible, but still causing the error.

Regards,
Eduardo

Offline

#5 2020-06-18 15:09:25

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Hi Eduardo,

I have realized the issue is caused by the date parameters, I have tested the same query by hard-coding the dates instead of keeping parameters and it worked.

parameters "GMF_CMCOMMON.get_cmpt_cost(mmt.INVENTORY_ITEM_ID,109,$P{ToDate},1002,0) STD_Cost," replaced with GMF_CMCOMMON.get_cmpt_cost(mmt.INVENTORY_ITEM_ID,109,'01-JUL-2016',1002,0) STD_Cost,
parameters "trunc(TRANSACTION_DATE) between  $P{Fr_Date} and $P{ToDate}" replaced with "and TRANSACTION_DATE >= '01-JUL-2016 and TRANSACTION_DATE <= '01-NOV-2016'"

I have another more simpler query with the same issue if you still need it for troubleshooting.

Regards,
Zain.

Offline

#6 2020-06-19 07:18:27

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi kzain89,

if the data parameters cause this, a similar query as the following should not work:

SELECT * FROM myTable
WHERE TRANSACTION_DATE <= $P{Fr_Date}

could you please check this and send me the exact query where this is not working? Please make sure that the dynamic table works and it shows data before converting into a pivot report.

Regards,
Eduardo

Offline

#7 2020-06-19 08:12:16

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Hi,

i have tested with the below,

SELECT * FROM myTable
WHERE TRANSACTION_DATE <= $P{Fr_Date}

It populates within the dynamic list but it bring the same error "sqldataexception: ora-01861: literal does not match format string" (just as the above query does) when i try to generate pivot. Exact query is below.

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_DATE <= $P{Fr_Date}


Regards,
Zain.

Offline

#8 2020-06-19 11:09:05

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi kzain89,

ok, this query is much easier to debug as the query you sent previously, thanks smile
can you please post a screenshot of your $P{Fr_Date} parameter configuration in ReportServer? you can use http://imgbb.com for this.

If you change the query to this:

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_DATE <= '01-NOV-2016'

does it work then?

Regards,
Eduardo

Offline

#9 2020-06-19 11:21:53

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Yes the query below works both in dynamic lists and pivots.

SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_DATE <= '01-NOV-2016'

Please find the screenshots for the date parameter here:

https://ibb.co/SKKrnKT
https://ibb.co/99qgZdW
https://ibb.co/LgpnGjb

Regards,
Zain.

Offline

#10 2020-06-19 11:29:52

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi Zain,

thanks for the screenshots. What exact type does the TRANSACTION_DATE field have in your Oracle installation?

Regards,
Eduardo

Offline

#11 2020-06-19 11:31:33

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Timestamp

We TRUNCATE the field so that we only get the date in the format "DD-MON-YYYY"

Regards,
Zain.

Last edited by kzain89 (2020-06-19 11:54:42)

Offline

#12 2020-06-19 12:18:40

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi Zain,

we raised ticket RS-4228 for this. We will get back to you when we have more information.

Regards,
Eduardo

Offline

#13 2020-06-19 13:08:31

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi Zain,

in the upper section of your pivot report you have the button "export cube to...", "Mondrian 4". Can you please post the output for the simple query ? I think here you will see the problem query in the SQL dialect="generic" section.

Can you please create a new dynamic list with this exact query you get here and check if you get the same error ?

Regards,
Eduardo

Offline

#14 2020-06-19 13:15:07

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Also see the screenshot https://ibb.co/HrTFPRz

<?xml version="1.0" encoding="UTF-8"?>

<Schema name="GenericDynaListSchema" metamodelVersion="4.0">
  <Cube name="GenericDynaListCube">
    <MeasureGroups>
      <MeasureGroup table="GenericDynaListCube" name="measures">
        <Measures></Measures> 
        <DimensionLinks></DimensionLinks>
      </MeasureGroup>
    </MeasureGroups> 
    <Dimensions></Dimensions>
  </Cube> 
  <PhysicalSchema>
    <Query alias="GenericDynaListCube">
      <ExpressionView>
        <SQL dialect="generic"> <![CDATA[SELECT * FROM (SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_DATE <= '2020-06-19') wrappedQry]]> </SQL>
      </ExpressionView>
    </Query>
  </PhysicalSchema>
</Schema>

Offline

#15 2020-06-19 13:46:57

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi Zain,

if you create a new dynamic list with this query:

SELECT * FROM (SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_DATE <= '2020-06-19') wrappedQry

do you get the same error ?

Regards,
Eduardo

Offline

#16 2020-06-19 13:52:14

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

Yes i get the same error when executing the above query

https://ibb.co/JqtM8xZ

Zain.

Offline

#17 2020-06-27 06:33:17

kzain89
Member
From: Kenya
Registered: 2020-06-18

Re: sqldataexception: ora-01861: literal does not match format string

HI Eduardo,

Kindly update us on the above parameter issue.

Regards,
Zain.

Offline

#18 2020-06-29 07:32:22

eduardo
Administrator
Registered: 2016-11-01
Website

Re: sqldataexception: ora-01861: literal does not match format string

Hi Zain,

we raised ticket RS-4228 for this. We will get back to you when we have more information.

Regards,
Eduardo

Offline

Board footer

Powered by FluxBB