I had SQL that worked in other SQL editors, but they do not seem to work in TOAD. Can someone help me?
Probably. Post your query!
here is the SQL
SELECT rpAN8, abALKY, abALPH, alADD1, alADD2, alCTY1, alADDS, alADDZ, rpDOC, rpDCT,
TO_DATE(TO_CHAR(rpDIVJ + 1900000),'YYYYDDD'), rpDOCO, rpSFX, rpVR01, TO_DATE(TO_CHAR(rpDDJ + 1900000),'YYYDDD'),
TO_DATE(TO_CHAR(rpDDNJ + 1900000), 'YYYYDDD'), SUM(CAST(rpAG AS NUMERIC (15,2))/100) AS "RPAG",
SUM(CAST(rpAAP AS NUMERIC (15,2))/100) AS "RPAAP", SUM(CAST(rpSTAM AS NUMERIC (15,2))/100) AS "RPSTAM",
SUM(CAST(rpADSC AS NUMERIC (15,2))/100) AS "RPADSC", rpODOC, abAC23, a.drdl01, b.drdl01
FROM proddtausg.F03B11, proddtausg.F0101, proddtausg.F0116, prodctlna.F0005 a, prodctlna.F0005 b
WHERE rpan8 = aban8
AND rpan8 = alan8
AND rpgfl1 = a.drky
AND rpddex = b.drky
AND a.drsy = '03B'
AND a.drrt = 'AR'
AND b.drsy = '03B'
AND b.drrt = 'CR'
AND rpaap <> 0
AND rpkco IN (52815, 53007, 53009, 53010, 53012, 53021, 53023, 53081, 53800, 53807,
53951, 54701, 54950, 54967, 56000, 60028, 60029, 60030, 60035, 60082, 60083, 60084,
60085, 60658, 60677, 60683, 60700, 60720, 60801, 60802, 60811, 60830, 60832, 60835,
60838, 60856, 60876, 60902, 60931, 60943, 84000, 85000)
GROUP BY rpkco, rpan8, abALKY, abALPH, alADD1, alADD2, alCTY1, alADDS, alADDZ, rpDOC, rpDCT,
rpDIVJ, rpDOCO, rpSFX, rpVR01, rpDDJ, rpDDNJ, rpODOC, abAC23, a.drdl01, b.drdl01
ORDER BY rpkco, rpan8, rpdoc
Here is the SQL that does NOT work:
SELECT rpAN8, abALKY, abALPH, alADD1, alADD2, alCTY1, alADDS, alADDZ, rpDOC, rpDCT,
TO_DATE(TO_CHAR(rpDIVJ + 1900000),'YYYYDDD'), rpDOCO, rpSFX, rpVR01, TO_DATE(TO_CHAR(rpDDJ + 1900000),'YYYDDD'),
TO_DATE(TO_CHAR(rpDDNJ + 1900000), 'YYYYDDD'), SUM(CAST(rpAG AS NUMERIC (15,2))/100) AS "RPAG",
SUM(CAST(rpAAP AS NUMERIC (15,2))/100) AS "RPAAP", SUM(CAST(rpSTAM AS NUMERIC (15,2))/100) AS "RPSTAM",
SUM(CAST(rpADSC AS NUMERIC (15,2))/100) AS "RPADSC", rpODOC, abAC23, a.drdl01, b.drdl01
FROM proddtausg.F03B11, proddtausg.F0101, proddtausg.F0116, prodctlna.F0005 a, prodctlna.F0005 b
WHERE rpan8 = aban8
AND rpan8 = alan8
AND rpgfl1 = a.drky
AND rpddex = b.drky
AND a.drsy = '03B'
AND a.drrt = 'AR'
AND b.drsy = '03B'
AND b.drrt = 'CR'
AND rpaap <> 0
AND rpkco IN (52815, 53007, 53009, 53010, 53012, 53021, 53023, 53081, 53800, 53807,
53951, 54701, 54950, 54967, 56000, 60028, 60029, 60030, 60035, 60082, 60083, 60084,
60085, 60658, 60677, 60683, 60700, 60720, 60801, 60802, 60811, 60830, 60832, 60835,
60838, 60856, 60876, 60902, 60931, 60943, 84000, 85000)
GROUP BY rpkco, rpan8, abALKY, abALPH, alADD1, alADD2, alCTY1, alADDS, alADDZ, rpDOC, rpDCT,
rpDIVJ, rpDOCO, rpSFX, rpVR01, rpDDJ, rpDDNJ, rpODOC, abAC23, a.drdl01, b.drdl01
ORDER BY rpkco, rpan8, rpdoc
However, this DOES work:
SELECT rpAN8, abALKY, abALPH, alADD1, alADD2, alCTY1, alADDS, alADDZ, rpDOC, rpDCT,
TO_DATE(TO_CHAR(rpdivj+1900000),'YYYYDDD')
FROM proddtausg.F03B11, proddtausg.F0101, proddtausg.F0116, prodctlna.F0005 a, prodctlna.F0005 b
WHERE rpan8 = aban8
AND rpan8 = alan8
AND rpgfl1 = a.drky
AND rpddex = b.drky
AND a.drsy = '03B'
AND a.drrt = 'AR'
AND b.drsy = '03B'
AND b.drrt = 'CR'
AND rpaap <> 0
AND rpkco IN (52815, 53007, 53009, 53010, 53012, 53021, 53023, 53081, 53800, 53807,
53951, 54701, 54950, 54967, 56000, 60028, 60029, 60030, 60035, 60082, 60083, 60084,
60085, 60658, 60677, 60683, 60700, 60720, 60801, 60802, 60811, 60830, 60832, 60835,
60838, 60856, 60876, 60902, 60931, 60943, 84000, 85000)Grammatically, it seems OK at first glance.
What error do you get in Toad when you try to run it?
Where does Toad put the cursor after that? (Oracle tells us the error line/position and we move the cursor to that position)
Hey Pattie,
I’m also in a JDE shop, so I might have a little insight. Are you getting an ORA-1830 error in the TO_DATEs?
In our F03B11, there’s occasionally a “bad” date, like 151031, where the year is beyond 2050, IIRC. If you’re getting the ORA error above, I can help you track it down via Toad.
As an aside, the CASTs are probably more CPU intensive than just dividing, as the RPAG column is already a NUMBER.
Let us know!
Rich
p.s. For non-JDE shops, JDE stores dates in a pseudo-Julian 6-digit NUMBER column, which you might be able to glean from the date conversion in Pattie’s SQL.
Wait, wait, WAIT! My answer didn’t “smell” right – you have a format mask error in the RPAG transformation. You have “YYYDDD” and it should be “YYYYDDD”.
Thinking and typing at the same time is difficult for me… 
Rich
And it’s the “RPDDJ” format that has the issue. Sheesh! I need a beer or 6 apparently!
Rich
@ Rich J
error: ORA-01848: day of year must be between 1 and 365 (366 for leap year)
@Rich J.
It runs now and returns 500 records, but when I try to pull the entire data set; about 45K records, it gives me the same error. I am thinking I have bad data, but not sure how to approach.
Pattie
OK, let’s hope I have this correct before I post! [B]
There’s only two parts to the JDE Julian date – the year and the day of the year. So, it must be one or both of those parts of the date value, right? Let’s separate them:
SELECT
rpan8,
rpdoc,
rpdct,
rpkco,
rpsfx,
rpddj
,rpddj-mod(rpddj,1000) modyear
,mod(rpddj,1000) moddate
– ,TO_DATE(TO_CHAR(rpddj + 1900000), ‘YYYDDD’) rpddj
– ,TO_DATE(TO_CHAR(rpddnj + 1900000), ‘YYYYDDD’) rpddnj
– ,TO_DATE(TO_CHAR(rpdivj + 1900000), ‘YYYYDDD’) rpdivj
FROM
crpdta.f03b11
ORDER BY 7
– DESC
;
This SELECTs the primary key (and AN8 for good measure) from your Customer Ledger table, along with the dreaded DDJ date that we’ll troubleshoot. It also gives us MODYEAR, which is the year portion of the date, and MODDATE for the day portion. It then ORDERs the output by column 7, which is the JDE Julian year part of DDJ.
Looking at the first 500 rows that Toad returns, you should be able to tell if you see any garbage in the first few rows of MODYEAR. If not, uncomment the DESC part of the ORDER BY and run again, examining the first few rows of the new results.
Repeat, but ORDER BY 8, which is MODDATE. If you have any issues with DDJ, you should see them here. You can then use AN8 or the primary key columns retrieved to correct the dates via SQL (hopefully you have business procedures in place to be able to do that!).
Since your original SQL also referenced DDNJ and DIVJ, you’ll likely want to check those as well.
Fire this up and let us know if/how it worked!
GL!
Rich
p.s. Noticed I have the SQL above running in PY – please test this in a lower environment first, if possible!
@Rich, It was actually a data problem. 2 records had zero as the RPDDNJ…
Thanks everyone for your help!