Upgrading report

[ClowneUser]

Hi,
We are in the process of upgrading our current ERP system to a new version, as part of this upgrade we are upgrading the version of Oracle we use from 8i to 10g, also the version of crystal supported by the application has gone from 8.5 to 11, so I'm converting all reports to work on the new platform, all has been going well until i got to one report, when i run the report and enter the parameters i get ORA-01861 errors, below is the sql for the report, can anyone see whats wrong?

Thanks in advance,

Mick.


SELECT "PURCHASE_ORDER\"."ADDR_STATE", "PURCHASE_ORDER\"."ADDRESS1", "PURCHASE_ORDER\"."ADDRESS2", "PURCHASE_ORDER\"."ORDER_NO", "PURCHASE_ORDER\"."RECIPIENT_NAME", "PURCHASE_ORDER\"."COUNTRY_CODE", "PURCHASE_ORDER\"."VENDOR_NO", "PURCHASE_ORDER\"."ZIP_CODE", "PURCHASE_ORDER_LINE_PART\"."PART_NO", "PURCHASE_ORDER_LINE_PART\"."ENG_CHG_LEVEL", "OPERATION_HISTORY\"."OUTSIDE_QTY_SHIPPED", "OPERATION_HISTORY\"."TRANSACTION", "OPERATION_HISTORY\"."DATED", "OPERATION_HISTORY\"."REVERSED_FLAG", "SUPPLIER_INFO_ADDRESS_TYPE\"."ADDRESS_TYPE_CODE", "SUPPLIER_INFO_ADDRESS\"."ADDRESS1", "SUPPLIER_INFO_ADDRESS\"."ADDRESS2", "SUPPLIER_INFO_ADDRESS\"."COUNTRY", "SUPPLIER_INFO_ADDRESS\"."ZIP_CODE", "SUPPLIER\"."VENDOR_NAME", "PURCHASE_ORDER_LINE_PART\"."LINE_NO", "PURCHASE_ORDER_LINE_PART\"."DESCRIPTION", "PURCHASE_ORDER_LINE_PART\"."DEMAND_ORDER_NO", "SUPPLIER_INFO_ADDRESS\"."CITY", "PURCHASE_PART_SUPPLIER\"."VENDOR_MANUF_LEADTIME", "SUPPLIER_ADDRESS_LEADTIME\"."VENDOR_DELIVERY_LEADTIME", (CONCAT("PURCHASE_ORDER_LINE_PART\"."ORDER_NO",to_Char("OPERATION_HISTORY\"."DATED",'/DD/MM/YYYY') )
)
FROM ((((((("IFSAPP"."OPERATION_HISTORY" "OPERATION_HISTORY\" LEFT OUTER JOIN "IFSAPP"."PURCHASE_ORDER_LINE_PART" "PURCHASE_ORDER_LINE_PART\" ON "OPERATION_HISTORY\"."ORDER_NO"="PURCHASE_ORDER_LINE_PART\"."DEMAND_ORDER_NO") LEFT OUTER JOIN "IFSAPP"."PURCHASE_ORDER" "PURCHASE_ORDER\" ON "PURCHASE_ORDER_LINE_PART\"."ORDER_NO"="PURCHASE_ORDER\"."ORDER_NO") INNER JOIN "IFSAPP"."PURCHASE_PART_SUPPLIER" "PURCHASE_PART_SUPPLIER\" ON ("PURCHASE_ORDER_LINE_PART\"."PART_NO"="PURCHASE_PART_SUPPLIER\"."PART_NO") AND ("PURCHASE_ORDER_LINE_PART\"."VENDOR_NO"="PURCHASE_PART_SUPPLIER\"."VENDOR_NO")) INNER JOIN "IFSAPP"."SUPPLIER_INFO_ADDRESS_TYPE" "SUPPLIER_INFO_ADDRESS_TYPE\" ON ("PURCHASE_ORDER\"."ADDR_NO"="SUPPLIER_INFO_ADDRESS_TYPE\"."ADDRESS_ID") AND ("PURCHASE_ORDER\"."VENDOR_NO"="SUPPLIER_INFO_ADDRESS_TYPE\"."SUPPLIER_ID")) LEFT OUTER JOIN "IFSAPP"."SUPPLIER" "SUPPLIER\" ON "PURCHASE_ORDER\"."VENDOR_NO"="SUPPLIER\"."VENDOR_NO") INNER JOIN "IFSAPP"."SUPPLIER_INFO_ADDRESS" "SUPPLIER_INFO_ADDRESS\" ON ("SUPPLIER_INFO_ADDRESS_TYPE\"."ADDRESS_ID"="SUPPLIER_INFO_ADDRESS\"."ADDRESS_ID") AND ("SUPPLIER_INFO_ADDRESS_TYPE\"."SUPPLIER_ID"="SUPPLIER_INFO_ADDRESS\"."SUPPLIER_ID")) INNER JOIN "IFSAPP"."SUPPLIER_ADDRESS" "SUPPLIER_ADDRESS\" ON ("SUPPLIER_INFO_ADDRESS_TYPE\"."ADDRESS_ID"="SUPPLIER_ADDRESS\"."ADDR_NO") AND ("SUPPLIER_INFO_ADDRESS_TYPE\"."SUPPLIER_ID"="SUPPLIER_ADDRESS\"."VENDOR_NO")) INNER JOIN "IFSAPP"."SUPPLIER_ADDRESS_LEADTIME" "SUPPLIER_ADDRESS_LEADTIME\" ON ("SUPPLIER_ADDRESS\"."ADDR_NO"="SUPPLIER_ADDRESS_LEADTIME\"."ADDR_NO") AND ("SUPPLIER_ADDRESS\"."VENDOR_NO"="SUPPLIER_ADDRESS_LEADTIME\"."VENDOR_NO")
WHERE "OPERATION_HISTORY\"."TRANSACTION"='WIPTOSUB' AND "PURCHASE_ORDER\"."ORDER_NO"='O201000' AND "SUPPLIER_INFO_ADDRESS_TYPE\"."ADDRESS_TYPE_CODE"='Delivery' AND "OPERATION_HISTORY\"."REVERSED_FLAG"='Not Reversed' AND ("OPERATION_HISTORY\"."DATED">=TO_DATE ('20-09-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "OPERATION_HISTORY\"."DATED"<TO_DATE ('01-01-10000 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

 

[synapsevampire]

I'd guess that it's because of the type of connectivity you're using, perhaps you used the Crystal supplied ODBC in CR 8.5, which was correct in CR 8.5 if you prefer to use the slower ODBC over the built in native connectivity in Oracle. However this is no longer required.

All along you should have been using Native connectivity though for the best performance.

Try switching connectivity, and if you post back, state which connectivity you are using, which should be a part of any post to be thorough the first time.

-k

 

[Turkbear]

Hi,
That error:

ORA-01861: literal does not match format string
Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.
Action: Correct the format string to match the literal.

May indicate that the

to_Char("OPERATION_HISTORY\"."DATED",'/DD/MM/YYYY')

Is badly formed..It would be unusual to have a DateString Format start with a /...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ClowneUser]

Hi,
Thanks for your replies, I managed to find the problem, it was the code below... (I can't figure out how to use 'quote'... notice the year it's 10000, but the mask has only 4 characters, so i changed it to be 9999 and it cured the problem, strange as it worked fine in 8.5, but never mind it's OK now.

AND "OPERATION_HISTORY\"."DATED"<TO_DATE ('01-01-10000 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Thanks again for your help.

Mick.

 

[synapsevampire]

Change the date, if someone comes to you and complains that your code has failed because you only allowed for 01-01-3000 consider it an excellent time for a grand celebration

-k