I have the darndest issue. I have a .tdr I’m trying to automate. When I run the SQL query behind it, it runs fine. When I refresh the .tdr itself with new values for the bind variables, it runs fine.
When I put it in an automation sequence, I get an “invalid number” error. There’s something different about how the automation defines & passes variables than the Bind Variable dialogue used in refreshing a standalone .tdr.
Here’s the section of problematic code:
SELECT xxxx from yyyyy
where TR.D_LST_PRM_CNTY_TYP IN (&TCOUNTY_GRP)
I can pass a list of counties char(3) to &TCOUNTY_GRP and it will run correctly as a solo .sql query or solo .tdr, but when I set the value (as a string) in the Set Variable function in Automation, I get “Invalid Number.” I’ve tried setting it as a number, using to_char inside the code; I’ve tried with and without quotes when setting the value, quotes around the parameter name, etc. Hard coding the list of counties is possible, but it’s a lot of reports 64 x 6 to be exact.
Anyway, insight would be a great present for when I come back from the holiday tomorrow!
Happy holidays, all!