So it seems IFS somehow got it wrong. I deleted the quick report, created a brand new one with the exact same query and now it’s working. That’s pretty frustrating to be honest, because from my perspective I am not doing anything differently, but suddenly the output is correct. Can it be that IFS saves an old/incorrect version of the quick report and keeps using it even though it changed? I have to correct this. Even after deleting the report and creating a new one, it sometimes gets sum7 and sum9 completely wrong when being scheduled.
So it seems IFS somehow got it wrong.I deleted the quick report, created a brand new one with the exact same query and now it’s working. That’s pretty frustrating to be honest, because from my perspective I am not doing anything differently, but suddenly the output is correct. Can it be that IFS saves an old/incorrect version of the quick report and keeps using it even though it changed?
select '' as "Kunde, Ort", 'Summe 1: Gruppe' as "Nr., Summen", sum(Menge) as Bestandfrom IFSINFO.BT_STOCK_EOMwhere Art = 'Bestand'and kunr LIKE 'C%'unionselect '' as "Kunde, Ort", 'Summe 2: Anonym -NB' as "Nr,, Summen", sum(Menge) as Bestandfrom IFSINFO.BT_STOCK_EOMwhere Art = 'Bestand'and Ort LIKE 'Anonym%'and Artikel NOT LIKE '99%'group by kunrunionselect '' as "Kunde, Ort", 'Summe 3: Gruppe +Anonym -NB = Board' as "Nr, Summen", sum(Menge) as Bestandfrom IFSINFO.BT_STOCK_EOMwhere Art = 'Bestand'and kunr LIKE 'C%'or Ort LIKE 'Anonym%'and Artikel NOT LIKE '99%'unionselect '' as "Kunde, Ort", 'Summe 4: NB = Nebenbahnen' as "Nr, Summen", sum(Menge) as Bestandfrom IFSINFO.BT_STOCK_EOMwhere Art = 'Bestand'and Artikel LIKE '99%'unionselect '' as "Kunde, Ort", 'Summe 5: Alle anderen Kunden' as "Nr, Summen", sum(Menge) as Bestandfrom IFSINFO.BT_STOCK_EOMwhere Art = 'Bestand'and Name NOT like 'Anonym'and kunr not like 'C%'unionselect '' as "Kunde, Ort", 'Summe 6: Macher Gesamt' as "Nr, Sum
The commands available in the query tool are more restricted than those available from PL/SQL developer. Any command that is going to attempt to ‘create’ an entity as part of the SQL statement is going to fail. The SQL query tool is completely read only and as such is bound by only connecting to the existing tables and views. Thank you for your explanation! But CTE’s should still work in a quick report right? I just have to write and test the query in PL/SQL Developer then.
Thank you, I found the right table 👍
I found out what caused the problem, even though I don’t fully understand it. The query contained a “Fetch First 1 Row” somewhere that caused an “ORA-44201: cursor needs to be reparsed” error similar to what is being described in this Stackoverflow thread. I removed the “Fetch First 1 Row” from the report and now the scheduled email is working as intended.
Yes, the report on its own is working just fine.
Could it be that the reports SQL expression is wrong somehow? The report on its own is working though.
Remove the ending semicolon when you save the quick reportThank you, the ending semicolon was the culprit. Now it’s working. 👍
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.