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

kzain89 · 2020-06-18 07:36:14

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)

eduardo · 2020-06-18 08:13:40

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

kzain89 · 2020-06-18 11:21:50

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

eduardo · 2020-06-18 11:30:14

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

kzain89 · 2020-06-18 15:09:25

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.

eduardo · 2020-06-19 07:18:27

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

kzain89 · 2020-06-19 08:12:16

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.

eduardo · 2020-06-19 11:09:05

Hi kzain89,

ok, this query is much easier to debug as the query you sent previously, thanks
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

kzain89 · 2020-06-19 11:21:53

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.

eduardo · 2020-06-19 11:29:52

Hi Zain,

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

Regards,
Eduardo

kzain89 · 2020-06-19 11:31:33

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)

eduardo · 2020-06-19 12:18:40

Hi Zain,

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

Regards,
Eduardo

eduardo · 2020-06-19 13:08:31

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

kzain89 · 2020-06-19 13:15:07

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

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

eduardo · 2020-06-19 13:46:57

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

kzain89 · 2020-06-19 13:52:14

Yes i get the same error when executing the above query

https://ibb.co/JqtM8xZ

Zain.

kzain89 · 2020-06-27 06:33:17

HI Eduardo,

Kindly update us on the above parameter issue.

Regards,
Zain.

eduardo · 2020-06-29 07:32:22

Hi Zain,

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

Regards,
Eduardo

ReportServer Forum

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Board footer