0

For a report to function properly, I have to convert the date parameters in a working Oracle SQL query with strings for a report. While this may sound odd - these are dates after all - the use of strings is a requirement for the report to run in our hosted database. The report runs beautifully in Crystal Reports with the date parameters, and be advised that the curly braces {} are how Crystal Reports defines parameters.

The challenge is converting the current syntax to a character-based syntax. Here is an excerpt of the larger query; the {?01_START_DT} is a date parameter of format MM/DD/YYYY (e.g., 07/31/2020):

SELECT U."UnitNumber" AS UNIT_NO
     , U."UsingDepartment" AS USE_DEPT
     , U."UnitStatus" AS STATUS
     , (
    SELECT H.USAGE
    FROM UNIT_HIST H
    WHERE U."UNITID" = H.UNIT_ID 
    AND   H.COMPANY  = 'OKS' 
    AND   FISC_PD    = CASE WHEN EXTRACT(MONTH FROM {?01_START_DT}) >= 7 
                         THEN TO_CHAR(ADD_MONTHS({?01_START_DT}, 12), 'YYYY') || TO_CHAR(ADD_MONTHS({?01_START_DT}, -6), 'MM')
                         ELSE TO_CHAR({?01_START_DT}, 'YYYY') || TO_CHAR(ADD_MONTHS({?01_START_DT}, 6), 'MM') 
                       END
FROM VIEW_ALL_UNITS U

Straight-forward to replace the parameter with a string {?START_DT} of the form MM/DD/YYYY (e.g., '07/31/2020', entered without quotes) as follows:

SELECT U."UnitNumber" AS UNIT_NO
     , U."UsingDepartment" AS USE_DEPT
     , U."UnitStatus" AS STATUS
     , (
    SELECT H.USAGE
    FROM UNIT_HIST H
    WHERE U."UNITID" = H.UNIT_ID 
    AND   H.COMPANY  = 'OKS' 
    AND   FISC_PD    = CASE 
                         WHEN EXTRACT(MONTH FROM TO_DATE({?START_DT}, 'MM/DD/YYYY') >= 7 
                         THEN TO_CHAR(ADD_MONTHS(TO_DATE({?START_DT}, 'MM/DD/YYYY'), 12), 'YYYY') || TO_CHAR(ADD_MONTHS(TO_DATE({?START_DT}, 'MM/DD/YYYY'), -6), 'MM')
                         ELSE TO_CHAR(TO_DATE({?START_DT}, 'MM/DD/YYYY'), 'YYYY') || TO_CHAR(ADD_MONTHS(TO_DATE({?START_DT}, 'MM/DD/YYYY'), 6), 'MM') 
                      END
FROM VIEW_ALL_UNITS U

Except this produces a ORA-01858 "non-numeric character" error!

I've tried sooo many things without success:

  • Changes to the input string (even though it matches the format)
  • Adding the time component to the format and the input string
  • Adding the optionally NLZ portion of TO_DATE
  • Simplifying the line by removing the EXTRACT and MONTH statements (for troubleshooting)
  • Changing the CASE expression (for troubleshooting) to:
FISC_PD = CASE WHEN TO_DATE({?START_DT}, 'MM/DD/YYYY') = TO_DATE('07/01/2020', 'MM/DD/YYYY') THEN

Needless to say the error persists, regardless of what I try.

As I alluded to previously, this queries a database on a hosted system to which I do not have physical or electronic access. These queries are against a reporting database. The error does not provide a line number or any other information useful to troubleshooting.

Any ideas what may be causing this issue?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
spacetanker
  • 119
  • 2
  • 15
  • I believe TO_DATE({?START_DT}, 'MM/DD/YYYY') would need to have the date parameter in quotes: TO_DATE('{?START_DT}', 'MM/DD/YYYY') – Stilgar Aug 26 '20 at 17:58
  • I would think adding quotes would *cause* this error, not solve it, if it's already bound as a string? Anyway... to clarify, `{?01_START_DT}` is passed as an actual date, and `{?START_DT}` is passed as a string, definitely in MM/DD/YYYY format, right? What happens if you run your queries directly in the DB but change all `{?01_START_DT}` to `date '2020-08-01'` and all `{?START_DT}` to `'08/01/2020'` ? You seem to be missing a couple of closing parentheses in the examples, which might not matter but is slightly confusing. And can you try this with a much simpler query to narrow it down? – Alex Poole Aug 26 '20 at 18:03
  • Possibly stupid question - when you generate the string to pass in, are you including quotes *within* that string? i.e. is the actual value of that string like `08/01/2020` or `'08/01/2020'`? ([Quick demo of the difference](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=35a1b597b302c012d2524e9c8df27abb)} – Alex Poole Aug 26 '20 at 18:12
  • @Stilgar, thank you for your comment. I am inclined to believe this would precipitate an error. The parameter is a string in MM/DD/YYYY format already. – spacetanker Aug 26 '20 at 18:16
  • @Alex Poole, Ditto my comment to Stilgar. And yes, I am affirming ALL of your clarifying questions elements. I did try something similar to your suggested troubleshooting step, but perhaps not exactly that way. I'll make some time shortly to try that and will advise. The missing parentheses are undoubtedly from copy/paste; no open quotation issues in the original code. – spacetanker Aug 26 '20 at 18:21
  • On your last comment, I am entering the actual value as 08/01/2020 without any quotations or other characters. – spacetanker Aug 26 '20 at 18:22
  • Lots of TO_CHAR/TO_DATE calls like this is a bit of a red flag. There's probably a simpler way of doing this. Please **edit your question** to clarify what rules you're trying to implement, and the actual input values in the precise format(s). – APC Aug 26 '20 at 18:49
  • 1
    @spacetanker - are you sure none are being added along the way? We don't know anything about your situation beyond the query, so trying to rule things out indirectly. Are you able to run a version that just does `select {?START_DT} from dual` to see what it returns? Like I said, might be way off base, but... – Alex Poole Aug 26 '20 at 19:05
  • @APC, I completely concur with your comments on the number of calls. The code I provided was but one subquery from a much larger query that gathers information from several other tables, so there are many more such calls! As I mentioned, the database is not of my design, so my options are limited...unless there's another way to convert a string to a date. As for your request that I edit the original question, the only rule is that my working query with date parameters must be converted to use string parameters which is explicitly stated. Thanks! – spacetanker Aug 26 '20 at 19:17
  • 2
    I don't understand how the part `{?01_START_DT}` is getting replaced by a date. What you are showing are SQL queries, except for these parts. What tool do you use to replace these parts? In Oracle I'd rather expect bind variables, e.g. `:START_DT` instead of these parentheses thingies. – Thorsten Kettner Aug 26 '20 at 19:27
  • @Thorsten Kettner, I will edit my question to explicitly indicate that those curly braced variables are how Crystal Reports defines parameters. It hadn't occurred to me that those might throw some off the scent. Thank you. – spacetanker Aug 26 '20 at 19:35
  • Ah, okay. Then this may be the same issue: https://stackoverflow.com/questions/23898201/crystal-reports-parameters-using-a-command-to-an-oracle-database – Thorsten Kettner Aug 26 '20 at 19:49
  • @Thorsten Kettner, appreciate the link. Note that the question was never marked solved, though. The advice provided was not accurate because the command runs in Oracle and Crystal Reports' syntax will cause an error (i.e., CDate is NOT Oracle SQL). I know this because I tried that first thing, when I naively thought this would be an easy conversion. – spacetanker Aug 26 '20 at 20:58

0 Answers0