Year parameter in Crystal Reports command 1

[lbass]

I am using Crystal Reports XI with CR Oracle ODBC Driver 4.2 to an Oracle 8 database. I am trying to use a fiscal year parameter within a command (in a subreport) in order to accomplish the equivalent of:

where
"table"."date" < date({?fiscal year},7,1)

{?fiscal year} is a 4-digit number parameter, which will be used to link the subreport to a main report fiscal year parameter. Does anyone know the correct syntax? I've tried many different approaches, e.g., like:

"table"."date" < T0_DATE(TO_CHAR({?Fiscal Year},'9999')||'-07-01','YYYY-MM-DD')

I get an error:

"Unrecognizable ODBC SQL escape sequence."

Thanks for any help.

-LB

 

[SantaMufasa]

LB,

I'm guessing that you might receive better/faster responses to this issue in a Crystal Reports forum since we have no built-in "fiscal" functions in Oracle, and certainly not using the escape-based syntax that you posted.

So, let us know if you receive a resolution to this from a Crystal Reports forum.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

Thanks for responding, Dave. I tried there first and was directed here. The {?fiscal year} isn't a particular fiscal function, just a number parameter as represented in Crystal Reports syntax and used in a SQL query. So what I'm really looking for is the appropriate SQL query syntax for an Oracle database. Still the wrong forum?

-Lb

 

[SantaMufasa]

LB,

If you are simply looking for the Oracle-appropriate syntax to determine if your "table"."date" < [some "fiscal" date of your specification], then you can say the following:

...WHERE "table"."date" < T0_DATE('2006-07-01','YYYY-MM-DD')...

Is this what you wanted to know?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

Almost, but what I'm trying to do is substitute my 4-digit number parameter for the 2006 in your code.

-LB

 

[SantaMufasa]

Sure...you can do this:

...WHERE "table"."date" <
   T0_DATE(<some expression containing 4-digit year>'
          ||-07-01','YYYY-MM-DD')...

Let us know if this takes care of business for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Don't you just hate it when you hit the [Submit] button .37446 seconds before you realise that you have a slight error in the posted code...a quote was out of place. It should read:

...WHERE "table"."date" <
   T0_DATE(<some expression containing 4-digit year>
          ||'-07-01','YYYY-MM-DD')...

 [santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via [b]www.dasages.com[/b]]

 

[lbass]

After another error message, I adjusted this a little for missing (?) quotes to:

WHERE "table"."date" <
   T0_DATE('<some expression containing 4-digit year>'
          ||'-07-01','YYYY-MM-DD')...

...and now I'm getting a message that a year must be between -4000 and 9999 and cannot be zero (numbers like this anyway). I think in previous reports I had to use syntax like:

'01-JUL-06' //(DD-MMM-YY)

Do you think I need to use the same formatting?

-LB

 

[lbass]

Sorry, I didn't see your post. Your last suggestion gave me the original error message again.

-LB

 

[SantaMufasa]

LB,

It's complaining that whatever you substituted for '<some expression containing 4-digit year>' is not a 4-character numeric value within the "-4000/9999" range. Can you confirm the CRpts 4-chr-year value that you are attempting to use?

Crystal Reports may not have the smarts that Oracle has by doing an implicit conversion from a 4-digit numeric value to a 4-character string value. Therefore, you may need to revert to something similar to your original post, that converts a number expression into a 4-character string.

In any case, the method we are assessing is solid within Oracle; it's the value you are trying to use that is bogus.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

Thank you for your time, Dave. It turned out that the problem was partly due to Crystal Reports--where if you add a new (number only?) parameter to an existing command, it doesn't recognize it. I had understood that this was corrected in the first CR XI service pack--but I guess not. When I tried a new command from scratch, the following syntax worked:

where
"table"."date" < to_date('{?fiscal year}-07-01')

I should have realized earlier that that was the problem, since it wasn't prompting me for the year!

Thanks so much.

-LB

 

[SantaMufasa]

Cool! I'm glad you got it resolved.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

As a follow-up, I realized I really needed the {?fiscal year}-1. Had to change the syntax again to:

"table"."date" <
TO_DATE(TO_CHAR({?Fiscal Year}-1)||'-07-01')

...almost what I originally tried. Oh, well! Thanks again.

-LB